on 02-23-2015 9:28 PM
Hi Experts, I am Scheduling my Jobs, but i need to Check if it is a Hoilday, So i need a Script to Check if it is a Holiday Any Suggesions Thanks
Create a calendar (table) in your database. You could use the Date_Generation to that extent. Include a flag column indicating whether the date is a holiday or not.
Embed your complete job logic in the 'Then'-part of a conditional, leave the 'Else' empty. Use the built-in lookup_ext function in the 'If' condition:
lookup_ext([<datastore_name>.<owner>.Calendar,'PRE_LOAD_CACHE','MAX'], [HOLIDAY_FLAG],['N'],["DATE",'=',$G_sysdate]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>
</output_cols_info>' ) = 'N'
with $G_Sysdate a global variable of type date, initialised to sysdate().
You can now simply schedule your job to run once every day. As the exprssion will return false on holidays, the job won't be executed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Dirk, Thanks for responding Hoilday_calendar Table Calendar_date Holiday_flag 2/24/2014 y i have used based on the Logic In the If satement of Conditional , i have used below lookup_ext([Staging_Area.BODIUSER.HOLIDAY_CALENDAR,'PRE_LOAD_CACHE','MAX'], [HOLIDAY_FLAG],['Y'],[CALENDAR_DATE,'=',$G_sysdate]) SET ("run_as_separate_process"='no', "output_cols_info"='
' ) = 'Y' I have placed the Data Flow(my logic) in the Then and ignored the Else part It is not working Any Suggesions Thanks
OK, that's clear.
That means function call lookup_ext([Staging_Area.BODIUSER.HOLIDAY_CALENDAR,'PRE_LOAD_CACHE','MAX'], [HOLIDAY_FLAG],['Y'],[CALENDAR_DATE,'=',$G_sysdate])
returns 'Y'.
Or, totally equivalent,
select HOLIDAY_FLAG from BODIUSER.HOLIDAY_CALENDAR where CALENDAR_DATE = $G_sysdate returns null or 'Y'.
Can you check the record in the HOLIDAY_CALENDAR table for the date you submit?
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.