Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm tying to set a variable $(ToDate) to the value contained in a filedname (EndDate) so I can use it in a subsequent load SQL statement. I tried using the peek statement but I believe that can only be used in the load statement. Any ideas? Thanks.
DatePatternTo:
Load
if ($(sub) = 1, CDD01J ,
if ($(sub) = 2, CDD02J ,
if ($(sub) = 3, CDD03J ,
if ($(sub) = 4, CDD04J ,
if ($(sub) = 5, CDD05J ,
if ($(sub) = 6, CDD06J ,
if ($(sub) = 7, CDD07J ,
if ($(sub) = 8, CDD08J ,
if ($(sub) = 9, CDD09J ,
if ($(sub) = 10, CDD10J ,
if ($(sub) = 11, CDD11J ,
if ($(sub) = 12, CDD12J )))))))))))) as EndDate
;
SQL SELECT CDFY,CDDFYJ,CDD01J,CDD02J,CDD03J,CDD04J,CDD05J,CDD06J,CDD07J,CDD08J,CDD09J,CDD10J,CDD11J,CDD12J
FROM "JDE_PRODUCTION".PRODDTA.F0008 where CDFY = $(PrevYear2) and CDDTPN = 'R';
Set $(ToDate) = EndDate;
SalesDetails:
Load
'N/A' as Week,
'N/A' as Day,
'N/A' as WeekDay,
'N/A' as [Week Ending Date],
.
.
.
SQL SELECT
SDFY,SDLNTY,SDITM,SDDOCO,SDDOC,SDDCT,SDMCU ,SDFY,SDAN8 ,SDSHAN,SDIVD,SDDGL ,SDLITM,SDDSC1,SDDSC2,SDUOM ,SDSOBK,SDADDJ,
SDUORG,SDSOQS ,SDITWT,SDWTUM,SDUPRC,SDRSDJ,SDLTTR,SDDCTO,SDRCD,SDAEXP,SDLNID,SDUNCS,SDECST,SDDCTO,SDTRDJ,SDPA8,SDURAT, SDFRTH, SDCARS
FROM "JDE_PRODUCTION".PRODDTA.F42119
where SDIVD >= $(FromDate) and SDIVD <= $(ToDate)
and SDLNTY IN ('A','C','D','E','F','G','H','S','M','N','Q','K','R','Y','YR','Z','FP','SC')
and SDDCTO IN ('SO','SV','CO','CV','CM','SZ','SD','S1','SR')
and SDLTTR > '579'
and SDNXTR > '619'
and SDSHAN > 500
plz try peek function to get end date vaue and store that in a variable.
hope this will help ...
The peek function can be used outside Load statements, typically in a Let statement, e.g. Let $(vToDate) = peek(...); The problem you face is to choose which record to use - you most likely have several end dates in the table. Try something similar to the following (just one value):
MaxDate:
Load Max(Date) as EndDate resident FirstTable ;
Let vToDate = peek('EndDate',-1,'MaxDate');
Further, you should probably look at the Crosstable prefix to collapse your CDD01J .. CDD12J columns into one column.