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
END

This message has been edited. Last edited by: Kerry,

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,

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