software‎ > ‎módulos‎ > ‎engine‎ > ‎manuais‎ > ‎

Exemplos de uso do iQuery


Como fazer para gerar instruções EXISTS e NOT EXISTS?

postado em 28 de fev de 2011 05:08 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 05:37 por Usuário desconhecido ]

Alteramos a API do IQuery para que ele pudesse gerar instrução EXISTS e NOT EXISTS nas cláusulas WHERE.

Vamos ao exemplo. Suponhamos que você desejar obter todos os pedidos ou baixas que não tenham acessórios. Você deverá usar o IQuery da forma abaixo codificada:

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

var pedidos = IQuery.from(-2008879000 /* Pedidos ou Provisões */)

var vinculosAcessorios = IQuery.from(-1897051125 /* Vínculos entre Pedidos e Acessórios */)
.where(
      {
             field: "CRPEDIDO",
             operator: "=",
             value: pedidos.column({field: "CHCRIACAO", isSubQuery: false})
      }
)
       
var pedidos = pedidos
              .where({notExists: vinculosAcessorios})
              .column({field: "CHAVE"})

pedidos.toSql()


Há dois pontos importantes que destaquei na API. O primeiro foi o uso da propriedade isSubQuery em pedidos.column({field: "CHCRIACAO", isSubQuery: false}). Esta propriedade é para mudar o comportamento padrão do iQuery de gerar sub-queries quando você passa uma cláusula Select na propriedade value de uma cláusula Where. Neste caso específico esta linha irá gerar um SQL semelhante a: 

VINCULAMOVIMENTAC2.CRPEDIDO = PEDIDO1.CHCRIACAO

Se a propriedade isSubQuery não estivesse sido desligada o SQL gerado seria algo assim:

VINCULAMOVIMENTAC2.CRPEDIDO in (select PEDIDO1.CHCRIACAO
from PEDIDO PEDIDO1
where (
(PEDIDO1.CLASSE in (
-2008879000,38292040,38530826,40035178, ...))))


O segundo ponto que merece destaque é o uso de fato do recurso novo. Note que o objeto passado para o a propriedade notExists é uma instância do objeto iQuery.

O SQL completo gerado pelo primeiro código apresentado neste post é semelhante ao código abaixo:

select PEDIDO1.CHAVE
from PEDIDO PEDIDO1
where
(
   exists
   (
      select 1

      from VINCULAMOVIMENTAC VINCULAMOVIMENTAC2
      where
      (

         VINCULAMOVIMENTAC2.CRPEDIDO = PEDIDO1.CHCRIACAO
      )

      and
      (

         VINCULAMOVIMENTAC2.CLASSE = -1897051125
      )
   )
)

and
(

   (
      PEDIDO1.CLASSE in
      (
         -2008879000,-1899999820,-1899999819,-1899999818,-1899999817,-1899999814,-1899999812,
         -1899999754,-1899999752,
-1899999480,-1899999479,-1899999478,-1899999477,-1899999476,
         -1899999475,-1899999474,-1899999473,-1899999472,
-1899999471,-1899999470,-1899999469,
         -1899999468,-1899999467,-1899999466,-1899999465,-1899999383,-1899999300,
-1899999299,
         -1899999295,-1899999289,-1899999288,-1899999287,-1899999286,-1899999285,-1899999278,
         -1899999274,
-1899999273,-1899999272,-1899999271,-1899999269,-1899999104,-1899999103,
         -1899998951,-1899998950,-1899998949,
-1899998948,-1899998947,-1899998946,-1899998945,
         -1899998944,-1899998943,-1899998942,-1899998941,-1899998940,
-1899998939,-1899998925,
         -1899998923,-1899998922,-1899998921,-1899998920,-1899998919,-1899998918,-1899998917,

         -1899998916,-1899998915,-1899998914,-1899998913,-1899998912,-1899998901,-1899998382,
         -1898190299,-1898190298,
-1898190297,-1898190136,-1898187711,-1898187709,-1898187600,
         -1898187552,-1898187551,-1897054192,-1897054190,
-1897054189,-1897054188,-1897054187,
         -1897054186,-1897054185,-1897054184,-1897054183,-1897054182,-1897054181,
-1897054180,
         -1897054179,-1897054178,-1897053396,-1897053239,-1897053225,-1897053120,-1897053119,
         -1897053118,
-1897053117,-1897052491,-1897052280,-1897052279,-1897052278,-1897052277,
         -1897052276,-1897052275,-1897052154,
-1897050841,-1897048773,-1897048772,-1897048771,
         -1897048743,-1897048742,-1897048700,-1897048154,-1897048153,
-1897036337,-1897036120,
         -1897036111,-1897036106,-1896648405,-1896648404,-1896648282,-1896648274,-1896648273,

         -1896648229,-1896648217,-1896648208,-1896648207,-1896648206,-1896648197,-1896648195,
         -1896648194,-1896648193,
-1896648190,-1896648103,-1896647767,-1896646976,-1896646955,
         -1896646934,-1896646914,-1895947335,-1895947334,
-1895947333,-1895946241,-1895836214,
         -1894743941,-1894743447,-1894543798,-1894543796,-1894543717,-1894443036,
-1894442985,
         -1894442619,1990,12682,34011,34015,34017,34018,34131,34440,38715,38754,42351,65941,
          79382,162703,
177781,183608,183609,186447,188473,2025108,5320945,6024675,12060574,
          35337554,35337574,35915925,35915943,
35915961,35915979,35915997,36762192,36762211,
          37428549,37859429,38292040,38530826,40035178,40035242,40509937,
40671029,40671120,
          40671204,40673028,42512517,42521138,42536106,42536134,44154242,44154285,44346488
      )
   )
)


Como fazer para usar o 'distinct'?

postado em 31 de dez de 2010 04:44 por Usuário desconhecido   [ atualizado em 24 de jan de 2011 05:27 por Usuário desconhecido ]

Abaixo um pequeno exemplo do uso do recurso 'distinct' no iQuery:

includeOnce -1897036629 /* /productos/INTEQengine/library/iquery/IQuery.ijs*/

var iquery = IQuery.from(-2008879000 /* Pedidos ou Provisões */)

.where({field: "chave", operator: "=", value: 1})
.column({field: "chave"})
.distinct()
iquery.toSql()


SQL gerado:

select distinct PEDIDO1.chave
from PEDIDO PEDIDO1
where (
PEDIDO1.chave = 1)
and (
(PEDIDO1.CLASSE in (
-2008879000,-1899999820,-1899999819,-1899999818,-1899999817,-1899999814,-1899999812,
-1899999754,-1899999752,-1899999480,-1899999479,-1899999478,-1899999477,-1899999476,
-1899999475,-1899999474,-1899999473,-1899999472,-1899999471,-1899999470,-1899999469,
-1899999468,-1899999467,-1899999466,-1899999465,-1899999383,-1899999300,-1899999299,
-1899999295,-1899999289,-1899999288,-1899999287,-1899999286,-1899999285,-1899999278,
-1899999274,-1899999273,-1899999272,-1899999271,-1899999269,-1899999104,-1899999103,
-1899998951,-1899998950,-1899998949,-1899998948,-1899998947,-1899998946,-1899998945,
-1899998944,-1899998943,-1899998942,-1899998941,-1899998940,-1899998939,-1899998925,
-1899998923,-1899998922,-1899998921,-1899998920,-1899998919,-1899998918,-1899998917,
-1899998916,-1899998915,-1899998914,-1899998913,-1899998912,-1899998901,-1899998382,
-1898190299,-1898190298,-1898190297,-1898190136,-1898187711,-1898187709,-1898187600,
-1898187552,-1898187551,-1897054192,-1897054190,-1897054189,-1897054188,-1897054187,
-1897054186,-1897054185,-1897054184,-1897054183,-1897054182,-1897054181,-1897054180,
-1897054179,-1897054178,-1897053396,-1897053239,-1897053225,-1897053120,-1897053119,
-1897053118,-1897053117,-1897052491,-1897052280,-1897052279,-1897052278,-1897052277,
-1897052276,-1897052275,-1897052154,-1897050841,-1897048773,-1897048772,-1897048771,
-1897048743,-1897048742,-1897048700,-1897048154,-1897048153,-1897036337,-1896648405,
-1896648404,-1896648282,-1896648274,-1896648273,-1896648229,-1896648217,-1896648208,
-1896648207,-1896648206,-1896648197,-1896648195,-1896648194,-1896648193,-1896648190,
-1896648103,-1896647767,-1896646976,-1896646955,-1896646934,-1896646914,-1895947335,
-1895947334,-1895947333,-1895946241,-1895836214,-1894743941,-1894743447,-1894543798,
-1894543796,-1894543717,-1894443036,-1894442985,-1894442619,1990,12682,34011,34015,
34017,34018,34131,34440,38715,38754,42351,65941,79382,162703,177781,183608,183609,
186447,188473,2025108,5320945,6024675,12060574,35337554,35337574,35915925,35915943,
35915961,35915979,35915997,36762192,36762211,37428549,37859429,38292040,38530826,40035178,
40035242,40509937,40671029,40671120,40671204,40673028,42512517,42521138,42536106,42536134,
44154242,44154285,44346488)))

Como fazer para desabilitar o filtro por classes filhas nas cláusulas where's de classe?

postado em 6 de ago de 2010 11:49 por Usuário desconhecido   [ atualizado em 19 de mai de 2014 12:55 por Usuário desconhecido ]

    A propriedade disableGetChildren permite definir se as chaves das classes filhas da classe informada para o iQuery serão adicionadas a cláusula 'WHERE' da query gerada. Por default a propriedade está definida como 'false' e não precisa ser incluída na chamada do método where.

    O exemplo abaixo por ser executado no iDBSql para efeito de testes:

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */
var iquery = IQuery.from( -2007890000 )
.where({field: "CLASSE", operator: "=", value: -2007890000, disableGetChildren: false})
.column({field: "CODIGO"})

iquery.toSql()

    O SQL gerado através do método 'toSql' do objeto 'iQuery' será este:

select ENTIDADE1.CODIGO
from ENTIDADE ENTIDADE1
where (
(ENTIDADE1.CLASSE in (
-2007890000,-2007889000,-2007888900,-2007888000,-2007887900,-2007887800,-2007887000,-2007886900,-2007886800,-2007886000,-1899999395,-1899998273,-1899997888,-1899933495,-1898188410,-1898188236,-1897054262,-1897054250,-1897053171,-1897052489,-1897052488,-1897050888,-1897050838,-1897050521,-1897049480,-1897047849,-1897047848,-1897047846,-1897037640,-1897037639,-1897037637,-1897037359,-1896048403,-1896048402,-1896047400,-1895836240,-1894835183,-1894443367,106,107,108,109,110,111,112,114,1923,9865,49345,49346,49347,49348,51558,51559,170257,
173007,173903,176413,178443,182979,185219,187746,188040,188356,278346,278347,278348,315481,332590,
399529,403627,523559,3742881,3742891,3800974,4141089,4310828,12407575,36230299,39018029,39018048,
40160535,43614400,43616000,44840991
)))
and (
(ENTIDADE1.CLASSE in (-2007890000,-2007889000,-2007888900,-2007888000,-2007887900,-2007887800,-2007887000,-2007886900,-2007886800,-2007886000,-1899999395,-1899998273,-1899997888,-1899933495,-1898188410,-1898188236,-1897054262,-1897054250,-1897053171,-1897052489,-1897052488,-1897050888,-1897050838,-1897050521,-1897049480,-1897047849,-1897047848,-1897047846,-1897037640,-1897037639,-1897037637,-1897037359,-1896048403,-1896048402,-1896047400,-1895836240,-1894835183,-1894443367,106,107,108,109,110,111,112,114,1923,9865,49345,49346,49347,49348,51558,51559,170257,-173007,173903,176413,178443,182979,185219,187746,188040,188356,278346,278347,278348,315481,332590,-399529,403627,523559,3742881,3742891,3800974,4141089,4310828,12407575,36230299,39018029,39018048,-40160535,43614400,43616000,44840991)))

    Para desabilitar a busca nas classes filhas basta configurar a propriedade pra 'true':

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */
var iquery = IQuery.from( -2007890000 )
.where({field: "CLASSE", operator: "=", value: -2007890000, disableGetChildren: true})
.column({field: "CODIGO"})

iquery.toSql()

    O SQL gerado através do método 'toSql' do objeto 'iQuery' será este:

select ENTIDADE1.CODIGO
from ENTIDADE ENTIDADE1
where (
ENTIDADE1.CLASSE = -2007890000)
and (
ENTIDADE1.CLASSE in (
-2007890000,-2007889000,-2007888900,-2007888000,-2007887900,-2007887800,-2007887000,
-2007886900,-2007886800,-2007886000,-1899999395,-1899998273,-1899997888,-1899933495,
-1898188410,-1898188236,-1897054262,-1897054250,-1897053171,-1897052489,-1897052488,
-1897050888,-1897050838,-1897050521,-1897049480,-1897047849,-1897047848,-1897047846,
-1897037640,-1897037639,-1897037637,-1897037359,-1896048403,-1896048402,-1896047400,
-1895836240,-1894835183,-1894443367,106,107,108,109,110,111,112,114,1923,9865,49345,49346,
49347,49348,51558,51559,170257,173007,173903,176413,178443,182979,185219,187746,188040,
188356,278346,278347,278348,315481,332590,399529,403627,523559,3742881,3742891,3800974,
4141089,4310828,12407575,36230299,39018029,39018048,40160535))

Como fazer para gerar colunas com valores literais?

postado em 9 de jul de 2010 06:33 por Usuário desconhecido   [ atualizado em 19 de mai de 2014 12:59 por Usuário desconhecido ]

A API do iQuery foi alterada para passar a suportar colunas com valores literais. Os valores literais são bastante úteis para casos em que o desenvolvedor deseje construir expressões de colunas calculadas. Já falamos de colunas calculadas através de expressões no post Como fazer para gerar colunas de expressões?
Vejamos agora exemplos do uso da API do iQuery para gerar colunas literais:

Exemplo do uso de um literal simples:
includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

var iquery = IQuery.from(-2008879000 /* Pedidos ou provisões */)
.where({field: "CHAVE", operator: "=", value: -1})
.column(
   {literal: "100"}
)

iquery.toSql()

//SQL gerado:
select 100 //Este é o literal gerado  
from PEDIDO PEDIDO1
where (
PEDIDO1.CHAVE = -1)
and (
PEDIDO1.CLASSE in (
-2008879000,-1899999820,-1899999819,-1899999818,-1899999817,-1899999814,-1899999812,-1899999754,
-1899999752,-1899999480,-1899999479,-1899999478,-1899999477,-1899999476,-1899999475,-1899999474,
-1899999473,-1899999472,-1899999471,-1899999470,-1899999469,-1899999468,-1899999467,-1899999466,
-1899999465,-1899999383,-1899999300,-1899999299,-1899999295,-1899999289,-1899999288,-1899999287,
-1899999286,-1899999285,-1899999278,-1899999274,-1899999273,-1899999272,-1899999271,-1899999269,
-1899999104,-1899999103,-1899998951,-1899998950,-1899998949,-1899998948,-1899998947,-1899998946,
-1899998945,-1899998944,-1899998943,-1899998942,-1899998941,-1899998940,-1899998939,-1899998925,
-1899998923,-1899998922,-1899998921,-1899998920,-1899998919,-1899998918,-1899998917,-1899998916,
-1899998915,-1899998914,-1899998913,-1899998912,-1899998901,-1899998382,-1898190299,-1898190298,
-1898190297,-1898190136,-1898187711,-1898187709,-1898187600,-1898187552,-1898187551,-1897054192,
-1897054190,-1897054189,-1897054188,-1897054187,-1897054186,-1897054185,-1897054184,-1897054183,
-1897054182,-1897054181,-1897054180,-1897054179,-1897054178,-1897053396,-1897053239,-1897053225,
-1897053120,-1897053119,-1897053118,-1897053117,-1897052491,-1897052280,-1897052279,-1897052278,
-1897052277,-1897052276,-1897052275,-1897052154,-1897050841,-1897048773,-1897048772,-1897048771,
-1897048743,-1897048742,-1897048700,-1897048154,-1897048153,-1897036337,-1896648405,-1896648404,
-1896648282,-1896648274,-1896648273,-1896648229,-1896648217,-1896648208,-1896648207,-1896648206,
-1896648197,-1896648195,-1896648194,-1896648193,-1896648190,-1896648103,-1896647767,-1896646976,
-1896646955,-1896646934,-1896646914,-1895947335,-1895947334,-1895947333,-1895946241,-1895836214,
-1894743941,-1894743447,-1894543798,-1894543796,-1894543717,-1894443036,-1894442985,-1894442619,
1990,12682,34011,34015,34017,34018,34131,34440,38715,38754,42351,65941,79382,162703,177781,
183608,
183609,186447,188473,2025108,5320945,6024675,12060574,35337554,35337574,35915925,
35915943,35915961,
35915979,35915997,36762192,36762211,37428549,37859429,38292040,38530826,
40035178,40035242,40509937,
40671029,40671120,40671204,40673028))


Exemplo do uso de literais com expressão:
includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

var iquery = IQuery.from(-2008879000 /* Pedidos ou provisões */)
.where({field: "CHAVE", operator: "=", value: -1})
.column(
   {
                    //2*(QUANTIDADE + 100)
     field: [{literal: "2"}, "*", [{field: "QUANTITADE"}, "+", {literal: "100"}]],
     alias: "expressao"
   }

)

iquery.toSql()

SQL Gerado:
select (2*coalesce((coalesce(PEDIDO1.QUANTITADE, 0)+100), 0)) as expressao
from PEDIDO PEDIDO1
where (
PEDIDO1.CHAVE = -1)
and (
PEDIDO1.CLASSE in (
-2008879000,-1899999820,-1899999819,-1899999818,-1899999817,-1899999814,-1899999812,-1899999754,
-1899999752,-1899999480,-1899999479,-1899999478,-1899999477,-1899999476,-1899999475,-1899999474,
-1899999473,-1899999472,-1899999471,-1899999470,-1899999469,-1899999468,-1899999467,-1899999466,
-1899999465,-1899999383,-1899999300,-1899999299,-1899999295,-1899999289,-1899999288,-1899999287,
-1899999286,-1899999285,-1899999278,-1899999274,-1899999273,-1899999272,-1899999271,-1899999269,
-1899999104,-1899999103,-1899998951,-1899998950,-1899998949,-1899998948,-1899998947,-1899998946,
-1899998945,-1899998944,-1899998943,-1899998942,-1899998941,-1899998940,-1899998939,-1899998925,
-1899998923,-1899998922,-1899998921,-1899998920,-1899998919,-1899998918,-1899998917,-1899998916,
-1899998915,-1899998914,-1899998913,-1899998912,-1899998901,-1899998382,-1898190299,-1898190298,
-1898190297,-1898190136,-1898187711,-1898187709,-1898187600,-1898187552,-1898187551,-1897054192,
-1897054190,-1897054189,-1897054188,-1897054187,-1897054186,-1897054185,-1897054184,-1897054183,
-1897054182,-1897054181,-1897054180,-1897054179,-1897054178,-1897053396,-1897053239,-1897053225,
-1897053120,-1897053119,-1897053118,-1897053117,-1897052491,-1897052280,-1897052279,-1897052278,
-1897052277,-1897052276,-1897052275,-1897052154,-1897050841,-1897048773,-1897048772,-1897048771,
-1897048743,-1897048742,-1897048700,-1897048154,-1897048153,-1897036337,-1896648405,-1896648404,
-1896648282,-1896648274,-1896648273,-1896648229,-1896648217,-1896648208,-1896648207,-1896648206,
-1896648197,-1896648195,-1896648194,-1896648193,-1896648190,-1896648103,-1896647767,-1896646976,
-1896646955,-1896646934,-1896646914,-1895947335,-1895947334,-1895947333,-1895946241,-1895836214,
-1894743941,-1894743447,-1894543798,-1894543796,-1894543717,-1894443036,-1894442985,-1894442619,
1990,12682,34011,34015,34017,34018,34131,34440,38715,38754,42351,65941,79382,162703,177781,183608,
183609,186447,188473,2025108,5320945,6024675,12060574,35337554,35337574,35915925,35915943,
35915961,35915979,35915997,36762192,36762211,37428549,37859429,38292040,38530826,40035178,
40035242,40509937,40671029,40671120,40671204,40673028))

Note que em caso do desenvolvedor quiser mudar a precedência dos operadores da expressão ele deve user [](colchetes).
Exemplificando melhor:
No caso da expressão 2*(QUANTIDADE + 100) a precedência foi mudada para que a soma fosse executada antes da multiplicação.

Como fazer para gerar um campo com a média ponderada?

postado em 22 de jun de 2010 06:25 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 04:46 por Usuário desconhecido ]

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

IQuery.from( -2008879000 /* Pedidos ou Provisões */ )
.column({field: "RECURSO" })
.column({avg: "UNITARIO", weight: "QUANTIDADE", alias: "MEDIA" })
.toSql()


SQL Gerado:
select PEDIDO1.RECURSO, case when sum(coalesce(PEDIDO1.QUANTIDADE, 0)) = 0 then 0 else sum(coalesce(PEDIDO1.UNITARIO, 0)*coalesce(PEDIDO1.QUANTIDADE, 0))/sum(coalesce(PEDIDO1.QUANTIDADE, 0)) end as MEDIA
from PEDIDO PEDIDO1
where (
PEDIDO1.CLASSE in (
-2008879000,-1899999820,-1899999819,-1899999818,-1899999817,-1899999814,-1899999812,-1899999754,
-1899999752,-1899999480,-1899999479,-1899999478,-1899999477,-1899999476,-1899999475,-1899999474,
-1899999473,-1899999472,-1899999471,-1899999470,-1899999469,-1899999468,-1899999467,-1899999466,
-1899999465,-1899999383,-1899999300,-1899999299,-1899999295,-1899999289,-1899999288,-1899999287,
-1899999286,-1899999285,-1899999278,-1899999274,-1899999273,-1899999272,-1899999271,-1899999269,
-1899999104,-1899999103,-1899998951,-1899998950,-1899998949,-1899998948,-1899998947,-1899998946,
-1899998945,-1899998944,-1899998943,-1899998942,-1899998941,-1899998940,-1899998939,-1899998925,
-1899998923,-1899998922,-1899998921,-1899998920,-1899998919,-1899998918,-1899998917,-1899998916,
-1899998915,-1899998914,-1899998913,-1899998912,-1899998901,-1899998382,-1898190299,-1898190298,
-1898190297,-1898190136,-1898187711,-1898187709,-1898187600,-1898187552,-1898187551,-1897054192,
-1897054190,-1897054189,-1897054188,-1897054187,-1897054186,-1897054185,-1897054184,-1897054183,
-1897054182,-1897054181,-1897054180,-1897054179,-1897054178,-1897053396,-1897053239,-1897053225,
-1897053120,-1897053119,-1897053118,-1897053117,-1897052491,-1897052280,-1897052279,-1897052278,
-1897052277,-1897052276,-1897052275,-1897052154,-1897050841,-1897048773,-1897048772,-1897048771,
-1897048743,-1897048742,-1897048700,-1897048154,-1897048153,-1897036337,-1896648405,-1896648404,
-1896648282,-1896648274,-1896648273,-1896648229,-1896648217,-1896648208,-1896648207,-1896648206,
-1896648197,-1896648195,-1896648194,-1896648193,-1896648190,-1896648103,-1896647767,-1896646976,
-1896646955,-1896646934,-1896646914,-1895947335,-1895947334,-1895947333,-1895946241,-1895836214,
-1894743941,-1894543798,-1894543796,-1894543717,-1894443036,-1894442985,-1894442619,1990,12682,
34011,34015,34017,34018,34131,34440,38715,38754,42351,65941,79382,162703,177781,183608,183609,
186447,188473,2025108,5320945,6024675,12060574,35337554,35337574,35915925,35915943,35915961,
35915979,35915997,36762192,36762211,37428549,37859429,38292040,38530826,40035178,40035242,
40509937,40671029,40671120,40671204,40673028))
group by PEDIDO1.RECURSO

Como fazer para gerar um 'avg' (média) com 'group by'?

postado em 24 de mai de 2010 05:25 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 05:37 por Usuário desconhecido ]

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

IQuery.from( -2008889000 /* Títulos */ )
.column({field: "TIPO"})
.column({ avg: "VALOR" })
.toSql()


SQL Gerado:
select TITULO1.TIPO, avg(coalesce(TITULO1.VALOR, 0)) as VALOR
from TITULO TITULO1
where (
TITULO1.CLASSE in (
-2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,-1899998367,
-1899933806,51518,51519,35337524,35337539,35337623,35337638))
group by TITULO1.TIPO

Como fazer para gerar um 'avg' (média)?

postado em 24 de mai de 2010 05:20 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 04:45 por Usuário desconhecido ]

includeOnce -1897036629 /* /products/INTEQengine/library/iquery/IQuery.ijs */

IQuery.from( -2008889000 /* Títulos */ )
.column({ avg: "VALOR" })
.toSql()

SQL Gerado:
select avg(coalesce(TITULO1.VALOR, 0)) as VALOR
from TITULO TITULO1
where (
TITULO1.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,-1899998367,
  -1899933806,51518,51519,35337524,35337539,35337623,35337638)
)

Como fazer para definir o valor do COALESCE de uma coluna?

postado em 18 de mai de 2010 14:15 por Usuário desconhecido   [ atualizado em 19 de mai de 2014 12:55 por Usuário desconhecido ]

var iquery = IQuery.from( -2008889000 /* Títulos */ )
.where({field: "CHAVE", operator: "=", value: 1})
.column({ field: "CODIGO", useCoalesce: true, coalesceValue: "NÃO INFORMADO"})
.toSql()

SQL Gerado:
select coalesce(TITULO1.CODIGO, 'NÃO INFORMADO')
from TITULO TITULO1
where (
TITULO1.CHAVE = 1)
and (
TITULO1.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,-1899998367,
  -1899933806,51518,51519,35337524,35337539,35337623,35337638))

Como fazer para gerar colunas vazias no IQuery?

postado em 18 de mai de 2010 14:15 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 05:37 por Usuário desconhecido ]

includeOnce -1897036629 /* /products/INTEQerp infrastructure/library/iQuery/IQuery.ijs */

IQuery.from( -2008889000 /* Títulos */ )
.column({field: "CHAVE", show: false})
.column({castValue: null, cast: "Varchar(50)", alias: "COL_VAZIA"})
.toSql()

SQL Gerado:
select cast(null as Varchar(50)) as COL_VAZIA
from TITULO TITULO1
where (
 TITULO1.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,
  -1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638)
)

Como fazer para impedir a geração de um campo?

postado em 18 de mai de 2010 14:14 por Usuário desconhecido   [ atualizado em 20 de mai de 2014 04:39 por Usuário desconhecido ]

includeOnce -1897036629 /* /products/INTEQerp infrastructure/library/iQuery/IQuery.ijs */

IQuery.from( -2008889000 /* Títulos */ )
.column({ field: "CHAVE", show: false })
.column({ field: "VALOR" })
.toSql()

SQL Gerado:
select TITULO21.VALOR
from TITULO TITULO21
where (
TITULO21.CLASSE in (-2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,
-1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638))

1-10 of 24