topic Re: SQL ' in QlikView

SQL '

Thu, 20 Mar 2014 16:00:01 GMT

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';

Re: SQL '

Nicole-Smith — Thu, 20 Mar 2014 16:13:21 GMT

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

Re: SQL '

Thu, 20 Mar 2014 16:15:00 GMT

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

Re: SQL '

Thu, 20 Mar 2014 16:22:20 GMT

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

Re: SQL '

Nicole-Smith — Thu, 20 Mar 2014 16:29:45 GMT

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

Re: SQL '

maxgro — Thu, 20 Mar 2014 17:23:45 GMT

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)

;

Re: SQL '

Thu, 20 Mar 2014 17:39:45 GMT

Thanks, that worked.