Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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';
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)
;
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);
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
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
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...
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)
;
Thanks, that worked.