cancel
Showing results for 
Search instead for 
Did you mean: 

Need a Script to Check if it is a Holiday

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member187605
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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

former_member187605
Active Contributor
0 Kudos

Can you bit a more specific? Do you get an error message? Which one? At compile or at run time? Do you get unexpected results? Which?

...

Former Member
0 Kudos

I am not getting any error, the job is executing, which is not supposed to Execute

Former Member
0 Kudos

Hi Dirk, It worked, But Small Correction, have to place the logic in the else part and ignore the Then Thanks

former_member187605
Active Contributor
0 Kudos

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?

Answers (0)