Guia prático do iQuery

Exemplos práticos de uso do iQuery na construção de fontes de dados.

01. Derivando campos a partir de campos do tipo grade

Um campo do tipo grade pode ser usado como lookup de um campo no filtro de uma consulta.

Exemplo:

var iq = new IQuery();
iq.uncheckPermission();
iq = iq.from(-1891168937)
  .where([{
    field: 'GRIDDETALHE.AUXILIAR',
    operator: '=',
    value: 0
  }])
  .column('*');

SQL gerado:

select *
from master master1
where (
  exists (
    select 1 from Detalhe Detalhe2
    where (Detalhe2.mestre = master1.ikey and Detalhe2.AUXILIAR = 0)
    and (( Detalhe2.iClass in (-1891168945)))
  )
)
and ((master1.iClass = -1891168937))

02. Usando o "pontinho" para derivar novos campos

Código fonte:

IQuery.from( -2008889000 /* Títulos */ )
  .column("PESSOA.UF")
  .execute()

SQL Gerado:

select ENTIDADE2.UF
from TITULO TITULO1
left outer join ENTIDADE ENTIDADE2 on (
TITULO1.PESSOA = ENTIDADE2.CHAVE)
where ((
ENTIDADE2.CLASSE is null or ENTIDADE2.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))
)
and (
TITULO1.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,
  -1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638)
)

03. Personalizando um "group by"

Em algumas situações, é necessário que a cláusula “group by” seja personalizada. Neste caso é possível indicar a API para ignorar o group by gerado automaticamente e para que seja utilizado um personalizado. Para isto, utiliza-se o método groupBy conforme exemplo abaixo:

var IQuery = require('@nginstack/iquery/lib/IQuery');

var alocacaoMaior = IQuery.from(-1895833131 /* Requisitos num Sprint  */);
alocacaoMaior = alocacaoMaior.where( [ { field: "SPRINT", operator: "=", value: 3454359 } ] )
alocacaoMaior = alocacaoMaior.groupBy( [ { field: "TAREFA"} ] )

var alocacao = IQuery.from(-1895833131 /* Requisitos num Sprint  */);
alocacao = alocacao.where(
[
  {
    field: "CHAVE",
    operator: "=",
    value: alocacaoMaior.column([{
      literal: "CAST(SUBSTR( max(" +
        "CAST(EXTRACT( year from INICIO ) as char(4) ) || " +
        "SUBSTR('00' || CAST(EXTRACT( month from INICIO ) as char(2) ), CHAR_LENGTH('00' || " +
        "CAST( EXTRACT( month from INICIO ) as varchar(2) )) - 1, 2 ) || " +
        "SUBSTR('00' || CAST( EXTRACT( day from INICIO ) as char(2) ), CHAR_LENGTH('00' || " +
        "CAST( EXTRACT( day from INICIO ) as varchar(2) )) - 1, 2 ) || " +
        "SUBSTR('00000000000000000000' || CAST(CHAVE as varchar(20) ), " +
        "CHAR_LENGTH('00000000000000000000' || CAST(CHAVE as varchar(20) )) - 19, 20 ) ), 9, 20) as BIGINT) as CHAVE"
    }])
  }
]);
alocacao = alocacao.column( [ { field: "CHAVE"}, { field: "INICIO"}, { field: "TAREFA"} ] )
alocacao.toSql();

Irá gerar o sql abaixo:

SELECT chave,
      inicio,
      sprint,
      tarefa
FROM   vinculamovimentac
WHERE  chave IN (SELECT CAST(SUBSTR(Max(CAST(EXTRACT(year FROM inicio) AS CHAR(4
                                            ))
                                        || SUBSTR('00'
                                                  || CAST(EXTRACT(
                                                    month FROM inicio) AS
                                                          CHAR(2))
                                          ,
                                              CHAR_LENGTH('00'
                                                    || CAST(EXTRACT(
                                                        month FROM
                                                        inicio) AS
                                                        VARCHAR(2))) - 1, 2)
                                        || SUBSTR('00'
                                                  || CAST(EXTRACT(day FROM
                                                          inicio) AS
                                                          CHAR(2)),
                                          CHAR_LENGTH('00'
                                                  || CAST(
                                              EXTRACT(day FROM inicio) AS
                                              VARCHAR(2)))
                                          - 1, 2)
                                        || SUBSTR('00000000000000000000'
                                                  || CAST(chave AS VARCHAR(20)),
                                          CHAR_LENGTH
                                          (
                                          '00000000000000000000'
                                          || CAST(chave AS
                                              VARCHAR(20)
                                              ))
                                          - 19, 20)), 9, 20) AS BIGINT) AS CHAVE
                FROM   vinculamovimentac
                WHERE  classe = -1895833131 /* Requisitos num Sprint */
                        AND sprint = 3454359
                GROUP  BY tarefa)

04. Fazendo um sub-select

Código fonte:

includeOnce -1897036629 //IQuery.ijs

var pedidos = IQuery.from( -2008879000 /* Pedidos ou Provisões */ )
var parcelas = IQuery.from( -1894443296 /* Parcelas */ )
  .where({ field: "CHCRIACAO", operator: "=", value: pedidos.column({ field: "CHCRIACAO"})})
  .column({ field: "CHCRIACAO" })
parcelas.toSql()

SQL Gerado:

select PARCELA2.CHCRIACAO
from PARCELA PARCELA2
where (
PARCELA2.CHCRIACAO in
(
  select PEDIDO1.CHCRIACAO
  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,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))
  )
)
and (PARCELA2.CLASSE = -1894443296)

05. Usando parênteses nas claúsulas Where

Código fonte:

// Use colchetes nas cláusulas where para gerar os parênteses.
var parcelas = IQuery.from( -1894443296 /* Parcelas */ )
  .where(
    [ {field: "CHCRIACAO", operator: "=", value: 1},
      "and",
      [ {field: "CHAVE", operator: "=", value: 1},
        "or",
        {field: "VERSAO", operator: "=", value: 3}
      ]
    ]
  )
  .column({ field: "CHCRIACAO" })
parcelas.toSql()

SQL Gerado:

select PARCELA1.CHCRIACAO
from PARCELA PARCELA1
where (
PARCELA1.CHCRIACAO = 1 and  (
PARCELA1.CHAVE = 1 or
PARCELA1.VERSAO = 3 ) )
and (
PARCELA1.CLASSE = -1894443296)

06. Usando "distinct"

Código fonte:

includeOnce -1897036629 //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)))

07. Usando cláusulas Where opcionais

Código fonte:

includeOnce -1897036629 //IQuery.ijs

var solicitacao = IQuery.from( -1897052056 /* Solicitações */ )
  .where([
    { field: "CHAVE", operator: "=", value: 1 },
    { field: "VERSAO", operator: "?=", value: null }
    // O operador ?= com o valor null faz com que esta clausula seja ignorada.
  ])
  .column("CHAVE")

solicitacao.toSql()

SQL Gerado:

select SOLICITACAO1.CHAVE
from SOLICITACAO SOLICITACAO1
where (
SOLICITACAO1.CHAVE = 1)
and (
SOLICITACAO1.CLASSE in (-1897052056,-1897052055,-1897052054,-1897051938,-1897050453,-1897048578,
-1897048577,-1894825103,3930906,35041724))

08. Usando a instrução "exists"

Código fonte:

var recurso = IQuery.from( -2007800000 /* Recursos */ )
  .where({ field: "CHAVE", operator: "=", value: 123 })

var solicitacao = IQuery.from( -1897052056 /* Solicitações */ )
  .where({ operator: "exists", value: recurso })
  .column("CHAVE")

solicitacao.toSql()

SQL Gerado:

select SOLICITACAO2.CHAVE
from SOLICITACAO SOLICITACAO2
where (
exists ( select 1
from RECURSO RECURSO1
where (
RECURSO1.CHAVE = 123)
and (
RECURSO1.CLASSE in (-2007800000,-2007790000,-1899998716,-1899998715,-1899998714,-1899998713,
-1899998380,-1899998379,-1899998055,-1899998054,-1899936366,-1899936350,
-1899936349,-1899933389,-1899933388,-1899931989,-1899931988,-1898188413,
-1898188412,-1897148139,-1897148138,-1897148136,-1897148107,-1897148105,
-1897131466,-1897053437,-1897053436,-1897053435,-1897053375,-1897053150,
-1897053127,-1897052333,-1897052332,-1897052108,-1897051928,-1897051833,
-1897051170,-1897050921,-1897050920,-1897050834,-1897050565,-1897050477,
-1897048830,-1897048371,-1897048043,-1897048042,-1897048041,-1897047843,
-1897047835,-1895836228,-1895836193,-1895836192,-1894815178,-1894815177,
-1894815176,-1894815082,-1894815081,-1894765217,-1894743905,-1894743764,
-1894743669,-1894743616,-1894543836,-1894543808,-1894543807,-1894442945,
-1894442712,-1894333991,-1894333967,-1894333966,-1894333962,-1894134133,
-1894134109,-1894134053,-1894134017,-1894134003,133,134,135,140,145,146,
147,148,149,150,151,153,156,157,158,159,162,163,170,184,185,186,187,188,
189,190,202,1933,1934,1937,1938,1939,1940,1941,1942,1943,1944,1945,1946,
1947,1948,1949,1952,1959,1960,1961,1962,1963,1964,1965,1966,1967,1968,
1969,1970,1971,1972,1973,1974,1975,1976,1977,1978,1979,1980,1981,1982,
1983,1984,1985,1986,1991,5059,5403,5414,5421,5422,5427,5428,5430,5431,
5432,5434,5872,5874,12689,38714,52892,52896,52898,52902,52908,52909,
52910,52911,52912,52913,52919,52920,52972,53139,62119,62120,62188,63053,
67026,67418,67419,67611,67612,67613,67614,68153,68157,69046,69050,69051,
69052,71070,82718,139961,139966,163516,163520,163521,163522,172883,173396,
173397,173398,176411,183534,183535,183536,183537,183538,183539,183540,
184174,185833,186076,2635628,2649461,3326356,3489361,3509956,3543770,3543779,
3614115,3742854,3742870,3745694,3745703,3745729,3745738,3870803,3870812,
3870821,3870830,4141102,4213977,4908311,4933393,4933406,4933419,5705198,
6109258,6109278,6109293,6114250,6114266,9842485,15237536,19322136,19322151,
26087951,27479461,35029703,35029725,35029747,35029769,35029791,35029813,
35029835,35029857,35029879,35029901,35029923,35029945,35029967,35029989,
35030011,35030033,35030055,35030077,35431262,35431283,35431304,35431325,
35431346,35441710,35441731,35441752,35441773,35441794,35441815,35441836,
35441857,35441878,35441920,35441941,35453972,35453993,35744652,35744667,
35944512,35944525,35944538,36513158,36593583,36640097,36786106,36786122,
36786138,36915632,36915650,37163088,37200148,37907890,37907908,37929041,
38963812,38963831,38963855,38965225))) )
and (
SOLICITACAO2.CLASSE in (-1897052056,-1897052055,-1897052054,-1897051938,
-1897050453,-1897048578, -1897048577,-1894825103,3930906,35041724))

09. Impedindo a geração de um campo

Código fonte:

includeOnce -1897036629 //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))

10. Gerando um campo com a média ponderada

Código fonte:

includeOnce -1897036629 //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

11. Gerando um "sum"

Código fonte:

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

SQL Gerado:

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

12. Gerando um "sum" com "group by"

Código fonte:

includeOnce -1897036629 //IQuery.ijs

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

SQL Gerado:

select TITULO1.RECURSO, sum(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.RECURSO

13. Gerando um "count" com "group by"

Código fonte:

includeOnce -1897036629 //IQuery.ijs

IQuery.from( -2008889000 /* Títulos */ )
  .column({ field: "CHAVE" })
  .column({ count: "CHAVE", alias: "QTDE", distinct: true })
  .toSql()

SQL Gerado:

select TITULO18.CHAVE, count(distinct(coalesce(TITULO1.CHAVE, 0))) as QTDE
from TITULO TITULO18
where (
TITULO18.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,-1899998369,
  -1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638)
)
group by TITULO18.CHAVE

14. Gerando um "avg" (média)

Código fonte:

includeOnce -1897036629 //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)
)

15. Gerando um "avg" (média) com "group by"

Código fonte:

includeOnce -1897036629 //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

16. Gerando instruções EXISTS e NOT EXISTS

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 //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 a serem destacados na API. O primeiro é o uso da propriedade isSubQuery em

pedidos.column({field: "CHCRIACAO", isSubQuery: false}).

Esta propriedade visa mudar o comportamento padrão do iQuery de gerar sub-queries quando uma cláusula Select é passada na propriedade value de uma cláusula Where. Neste caso específico, essa linha irá gerar um SQL semelhante a:

VINCULAMOVIMENTAC2.CRPEDIDO = PEDIDO1.CHCRIACAO

Se a propriedade isSubQuery não tivesse 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. Note que o objeto passado para 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
    )
  )
)

17. Gerando colunas vazias no IQuery

Código fonte:

includeOnce -1897036629 //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)
)

18. Gerando colunas de expressões

Código fonte:

includeOnce -1897036629 //IQuery.ijs

IQuery.from( -2008879000 /* Pedidos ou Provisões */ )
  .column({ field: "CHAVE" })
  .column({ field: [ { field: "UNITARIO"}, "*", {field: "QUANTIDADE"} ], alias: "SUB_TOTAL" })
  .toSql()

SQL gerado:

select PEDIDO9.CHAVE, (coalesce(PEDIDO9.UNITARIO, 0)*coalesce(PEDIDO9.QUANTIDADE, 0)) as SUB_TOTAL
from PEDIDO PEDIDO9
where (
PEDIDO9.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,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))

19. Gerando colunas com valores literais

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.

Exemplo do uso de um literal simples:

includeOnce -1897036629 //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 //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, caso o desenvolvedor queira mudar a precedência dos operadores da expressão, ele deve usar . 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.

20. Gerando claúsulas "is Null" e "is not Null"

Código fonte:

includeOnce -1897036629 //IQuery.ijs

var parcelas = IQuery.from( -1894443296 /* Parcelas */ )
  .where(
    [ {field: "CHCRIACAO", operator: "=", value: null},
      "and",
      {field: "CHAVE", operator: "<>", value: null}
    ]
  )
  .column({ field: "CHCRIACAO" })

parcelas.toSql()

SQL Gerado:

select PARCELA1.CHCRIACAO
from PARCELA PARCELA1
where (
PARCELA1.CHCRIACAO is null and
PARCELA1.CHAVE is not null)
and (
PARCELA1.CLASSE = -1894443296)

21. Gerando claúsulas "in"

Código fonte:

includeOnce -1897036629 //IQuery.ijs

var parcelas = IQuery.from( -1894443296 /* Parcelas */ )
  .where(
    {field: "CHCRIACAO", operator: "=", value: [1,2,3]}
  )
  .column({ field: "CHCRIACAO" })

parcelas.toSql()

SQL gerado:

select PARCELA1.CHCRIACAO
from PARCELA PARCELA1
where (
PARCELA1.CHCRIACAO in (1,2,3))
and (
PARCELA1.CLASSE = -1894443296)

22. Gerando "cast" de campos

Código fonte:

includeOnce -1897036629 //IQuery.ijs

IQuery.from( -2008889000 /* Títulos */ )
  .column({ field: "VALOR", cast: "number", alias: "VALOR_CONVERTIDO" })
  .toSql()

SQL Gerado:

select cast(TITULO20.VALOR as number) as VALOR_CONVERTIDO
from TITULO TITULO20
where (
TITULO20.CLASSE in (-2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,
-1899998369,-1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638))

23. Desabilitando o filtro por classes filhas nas cláusulas where de classe

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 padrão 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 //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 //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))

24. Definindo o valor do COALESCE de uma coluna

Código fonte:

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))

25. Fazendo "left join" com subquery

Código Fonte:

__includeOnce(-1897036629); /* IQuery.ijs */

var loc = IQuery.from(-1894743013); /* Localizadores */
var med = IQuery.from(-1894743008) /* Medições */
  .where([{field: "DATA", operator: ">=", value: new Date(2013, 0, 1)}])
  .column([{field: "POSICAOLOCALIZADOR", alias: "POSICAO"}, {field: "MEDICAO"}, {field: "DATA"}]);
  // a linha acima pode ser suprimida

loc = loc.leftOuterJoin(med)
  .on([{field: "CHAVE", operator: "=", value: med.column({field:"LOCALIZADOR"}) }]);

loc = loc.column([{field: "CHAVE"}]);
loc.toSql();

SQL gerado:

SELECT ENTIDADE1.chave,
       EVENTO2.posicao,
       EVENTO2.medicao,
       EVENTO2.data,
       EVENTO2.localizador
FROM  entidade ENTIDADE1
      LEFT OUTER JOIN (SELECT EVENTO2.posicaolocalizador AS POSICAO,
                              EVENTO2.medicao,
                              EVENTO2.data,
                              EVENTO2.localizador
                        FROM   evento EVENTO2
                        WHERE  ( EVENTO2.data >= '01/01/2013' )
                              AND ( EVENTO2.classe IN (
                                    -1894833940, -1894815028,
                                    -1894814878,
                                    -1894814870,
                                    -1894814854, -1894814853,
                                    -1894743008,
                                    108008301 ) )) EVENTO2
                    ON ( ENTIDADE1.chave = EVENTO2.localizador )
WHERE  ( ENTIDADE1.classe IN ( -1894743013, 120241501 ) )

26. Fazendo "joins"

Código fonte:

includeOnce -1897036629 //IQuery.ijs

var titulo = IQuery.from( -2008889000 /* Títulos */ )

IQuery.from( -2008879000 /* Pedidos ou Provisões */ )
.leftOuterJoin( titulo ).on(  //Aqui poderia também ser o usado o método innerJoin().
    { field: "CHCRIACAO", operator: "=", value: titulo.column("CHCRIACAO") }
)
.column({ field: "CHCRIACAO" })
.column({ field: titulo.column("VALOR") })
.toSql()

SQL Gerado:

select PEDIDO2.CHCRIACAO, TITULO1.VALOR
from PEDIDO PEDIDO2
left outer join TITULO TITULO1 on (
PEDIDO2.CHCRIACAO = TITULO1.CHCRIACAO)
where ((
TITULO1.CLASSE is null or TITULO1.CLASSE in (
  -2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,
  -1899998369,-1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638)))
and (
PEDIDO2.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,
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))

OBS.: É comum usar o iQuery dentro do DataSource, onde existem os métodos DataSourceFilters.prototype.getIQueryFilters e DataSourceColumns.prototype.getIQueryColumns. Estes métodos retornam, respectivamente, um vetor de filtros e colunas no formado do iQuery.

Segue abaixo um exemplo do uso de Joins com vetores de filtros e colunas:

includeOnce -1897036629 //IQuery.ijs

var colunasPedidos = [{field: "CHCRIACAO"}]
// Array que pode ser gerado pelo this.getIQueryColumns do DataSource
var colunasTitulos = [{field: "VALOR"}]
// Array que pode ser gerado pelo this.getIQueryColumns do DataSource.

var titulo = IQuery.from( -2008889000 /* Títulos */ )

IQuery.from( -2008879000 /* Pedidos ou Provisões */ )
  .leftOuterJoin( titulo )
  .on( { field: "CHCRIACAO", operator: "=", value: titulo.column("CHCRIACAO") } )
  .column(colunasPedidos)
  .column(titulo.column(colunasTitulos))
  .toSql()

SQL Gerado:

select PEDIDO2.CHCRIACAO, TITULO1.VALOR
from PEDIDO PEDIDO2
left outer join TITULO TITULO1 on (
PEDIDO2.CHCRIACAO = TITULO1.CHCRIACAO)
where ((
TITULO1.CLASSE is null or TITULO1.CLASSE in (-2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,
-1899998369,-1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638)))
and (
PEDIDO2.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,
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))

27. Gerando condições where de campos da mesma tabela

Exemplo: T.CHCRIACAO = T.NUMERO.

Código fonte:

var iQuery = IQuery.from( -2008889000 /* Títulos */ )
iQuery.where({ field: "CHCRIACAO", operator: "=", value: iQuery.column("NUMERO") })
  .column("CHAVE")
  .toSql()

SQL Gerado:

select TITULO1.CHAVE
from TITULO TITULO1
where (
TITULO1.CHCRIACAO = TITULO1.NUMERO)
and (
TITULO1.CLASSE in (-2008889000,-2008888890,-2008888790,-1899998525,-1899998524,-1899998370,
  -1899998369,-1899998367,-1899933806,51518,51519,35337524,35337539,35337623,35337638))