topic Re: Issue with use variable in SQL statement in QlikView

Issue with use variable in SQL statement

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'am looking for use in my loading SQL statement, a date stored in a variable.

I have in my script sheet :

1.SQL which fetch DT_MAX

Select

to_char(max(DH_START),'DD/MM/YYYY') as DT_MAX
from MY_TABLE;

2.Assignment of DT_MAX in a variable

SET V_DT_END=DT_MAX;

3.Use of V_DT_END in my SQL statement

Select xxx

from xxx

where DT between '01/01/2016' and $(V_DT_END)

The third step don't works. If i write between '01/01/2016' and '14/07/2017', it works. The problem is '. How to reproduce 'DT' with V_DT_END??

I have tried différents ways but the issue remains.

Thanks

Re: Issue with use variable in SQL statement

mangalsk — Thu, 20 Jul 2017 10:20:46 GMT

Write quotes ('$(V_DT_END)')

Re: Issue with use variable in SQL statement

shiveshsingh — Thu, 20 Jul 2017 11:33:48 GMT

Use peek function

Peek(field_name[, row_no[, table_name ] ] )

SET V_DT_END=Peek(DT_MAX);

Re: Issue with use variable in SQL statement

antoniotiman — Thu, 20 Jul 2017 11:41:42 GMT

Hi Shivesh,

You are correct,

However Syntax is

LET V_DT_END=Peek('DT_MAX');

Regards,

Antonio

Re: Issue with use variable in SQL statement

sergio0592 — Thu, 20 Jul 2017 11:42:17 GMT

Already tried but don't works

Re: Issue with use variable in SQL statement

shiveshsingh — Thu, 20 Jul 2017 12:04:43 GMT

what value is thsi variable holding after executing the script?

i mean after using peek function..

u can check in textbox

Re: Issue with use variable in SQL statement

sergio0592 — Thu, 20 Jul 2017 12:24:26 GMT

Thanks, but don't works with LET V_DT_END=Peek('DT_MAX') and after DT between '01/01/2016' and $(V_DT_END)

Re: Issue with use variable in SQL statement

antoniotiman — Thu, 20 Jul 2017 12:37:38 GMT

'$(V_DT_END)'

Re: Issue with use variable in SQL statement

Kushal_Chawda — Thu, 20 Jul 2017 12:58:33 GMT

try below

1.SQL which fetch DT_MAX

Date:

Select

to_char(max(DH_START),'DD/MM/YYYY') as DT_MAX
from MY_TABLE;

2.Assignment of DT_MAX in a variable

LET V_DT_END= peek('DT_MAX',0,'Date');

3.Use of V_DT_END in my SQL statement

Select xxx

from xxx

where DT between '01/01/2016' and '$(V_DT_END)'

Re: Issue with use variable in SQL statement

sergio0592 — Thu, 20 Jul 2017 13:19:20 GMT

Thanks Antonio, it works. I have to put ' two times, one in Peek('DT_MAX') and again with '$(V_DT_END)'. Strange no?

Re: Issue with use variable in SQL statement

antoniotiman — Thu, 20 Jul 2017 13:24:13 GMT

No Jean, because Peek('DT_MAX') -> is required from Peek() function

and '$(V_DT_END)' needs according SQL syntax.

Regards,

Antonio

Re: Issue with use variable in SQL statement

vishsaggi — Thu, 20 Jul 2017 13:50:09 GMT

When you use this variable in a textbox what value can you see?