SQL '

[#1]

Hola,

Necesito ayuda amigos, estoy intentando realizar una consulta sql como variable, pero esta lleva un campo con ' '. Esto hace que la variable se corte y no se genere de buena forma la consulta. Como puedo mostrar dentro de una variable el signo '.

Les dejo la consulta.

LET vSqlIMHIST= 'select ordno, itnbr,house,trqty, tramt,trndt from amflibx.imhist  where house not like 'X%' and trqty > 0 and trndt between  {1} and {2} and tcode = 'RP'; 

[Nicole-Smith]

SET vSqlIMHIST=select ordno, itnbr,house,trqty, tramt,trndt from amflibx.imhist  where house not like 'X%' and trqty > 0 and trndt between  {1} and {2} and tcode = 'RP';

Then to call it:

$(vSqlIMHIST);

[#3]

Use below:

LET vSqlIMHIST= 'select ordno, itnbr,house,trqty, tramt,trndt from amflibx.imhist  where house not like ''X%'' and trqty > 0 and trndt between  {1} and {2} and tcode = ''RP''';

Thanks,

Angad

[#4]

Hi Nicole, thanks for your answer but the problem it's with the ' in the sql. I don´t know how to concatenate in the sql, That cause a error.

Sorry my english is bad

[Nicole-Smith]

I think the problem is with your between:

SET vSqlIMHIST=select ordno, itnbr,house,trqty, tramt,trndt from amflibx.imhist  where house not like 'X%' and trqty > 0 and (trndt between 1 and 2) and tcode = 'RP';

What you really should do is try running the SQL code directly in SQL to see if it works before you even put it into QV...

[maxgro · Accepted solution]

this works, dbms is oracle

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=xxxx;Data Source=xxxxx;Extended Properties=""] (XPassword is xxxxxxxxxxxxx);

let vSql = 'SELECT L.COD_LOG, L.DAT_INIZIO, L.DAT_FINE, L.DES_LOG 

FROM DWH_DL.U002_LOG L

WHERE COD_LOG like ' & chr(39) & '%FTP%' & chr(39);

Table:

NoConcatenate

load *;

SQL

$(vSql)

;

[#7]

Thanks, that worked.