[SOLVED] Assign Define field value in Table1 to variable
Assign Define field value in Table1 to variable so that i can use in another table in the where clause..(something like -SET &variable which i can use in the where clause)..
What i need is...
DEFINE FILE FIRST MinDate/YYMD=HDATE(FIRST.VALUE_DATE , 'YYMD'); DOWeek/A3=; END DEFINE FILE WSS_HOLIDAYS HLYDate/YYMD=HDATE(WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_HOLIDAY_DATE , 'YYMD'); END
This is where i need help in assigning the Define FIELD in Table FIRST in the where loop of the table WSS_HOLIDAYS..I know how to use SET value and &...I know that i can do Join too..but would like to avoid it... ..Just need to get the value of a define field into a varaible using SET and put into the where condition for another table... TABLE FILE WSS_HOLIDAYS SUM 'CNT.WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_GDP_SITE' AS 'COUNT,GDP_SITE' BY 'WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_HOLIDAY_CCY' BY 'WSS_HOLIDAYS.WSS_HOLIDAYS.HLYDate' HEADING "" FOOTING "" WHERE ( WSS_HOLIDAYS.WSS_HOLIDAYS.HLYDate GE *MinDate) and ( WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_DELETE_FLAG EQ &GDP_SITE.QUOTEDSTRING ) ; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS H_HLDY1 ENDThis message has been edited. Last edited by: Kerry,
WebFOCUS 76 Windows all formats
September 21, 2010, 01:56 AM
Dan Satchell
It's unclear to me from your post whether you need a single value from your first file, or multiple values. If you only need a single value, the easiest solution is to save the value in a file and then read it into a variable for use in the WHERE clause in the second query. For example:
TABLE FILE FIRST
SUM MIN.VALUE_DATE NOPRINT
COMPUTE MinDate/YYMD = HDATE(MIN.VALUE_DATE,'YYMD');
ON TABLE SAVE AS SAVEFILE
END
-RUN
-READ SAVEFILE &MINDATE.8
DEFINE FILE WSS_HOLIDAYS
HLYDate/YYMD = HDATE(WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_HOLIDAY_DATE,'YYMD');
END
TABLE FILE WSS_HOLIDAYS
SUM
'CNT.WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_GDP_SITE' AS 'COUNT,GDP_SITE'
BY 'WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_HOLIDAY_CCY'
BY 'WSS_HOLIDAYS.WSS_HOLIDAYS.HLYDate'
HEADING
""
FOOTING
""
WHERE ( WSS_HOLIDAYS.WSS_HOLIDAYS.HLYDate GE &MINDATE )
AND ( WSS_HOLIDAYS.WSS_HOLIDAYS.WSS_DELETE_FLAG EQ &GDP_SITE.QUOTEDSTRING ) ;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H_HLDY1
END
If you need to retrieve multiple values from your first file and a standard JOIN won't work, you might consider a conditional JOIN, or the more restrictive DB_LOOKUP function.This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
September 21, 2010, 02:35 PM
Wfdev
Thank you for the update..Yes i was able to use the conditional join properly to get it working...It is goof to learn SAVEFILE functionality ...Thanks