Como fazer para gerar colunas com valores literais?

Data de postagem: Jul 09, 2010 1:33:47 PM

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