Issue with use variable in SQL statement

[sergio0592]

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

[mangalsk]

Write quotes ('$(V_DT_END)')

[shiveshsingh]

Use peek function

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

SET V_DT_END=Peek(DT_MAX);

[antoniotiman · Accepted solution]

Hi Shivesh,

You are correct,

However Syntax is

LET V_DT_END=Peek('DT_MAX');

Regards,

Antonio

[sergio0592]

Already tried but don't works

[shiveshsingh]

what value is thsi variable holding after executing the script?

i mean after using peek function..

u can check in textbox

[sergio0592]

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

[antoniotiman]

'$(V_DT_END)'

[Kushal_Chawda]

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

[sergio0592]

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