DB statement - Static calendar dates

[Krum_Ganev]

Hello,

 

i'm trying to write a DB statement to see the defined dates within all static calendar keywords as it seems some of the .xml exports cannot be reviewed via excel. 

 

so far this is the statement the statement that i came up with:

 

SELECT OH_name, OKB_Name,OKB_CType,OKD_Year, OKD_Content FROM OH LEFT JOIN OKB
ON OH_Idnr = OKB_OH_Idnr
LEFT JOIN OKD
ON OKB_OH_Idnr = OKD_OH_Idnr
WHERE OH_OType like 'CALE'
AND OKB_CType like 'S'
AND OH_Client = 0
AND OH_DeleteFlag = 0

 

result:

CALE.BASE ZSC_NN_PC_SAL_IDD4254F S 2021 111111111111111111111111111111111111111111111111111111111110001111111111111111111111111111111111111111111111111111111111111011111111111111111111111111111111111111111111111111111111111110111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110111111111111111111111111111111111111111111111111111111111111101111111111111111111111111111111

 

It seems that the dates are defined in bytes. Is there any way to get the dates in human readable format?

[LuuLe]

As is posted is in the CA Automic Community and there is no tags/categories selected but from the post content it appear to be regarding the Automic Workload Automation / Automation Engine product line. I have move this to the CA Automic Workload Automation section to better reach the product user audience.

[Prakash Subramanian]

Any one have any update on the above question?

[Reed Byers]

It looks like every three years, it's somebody's job to revive this post, and poke the community to see if anybody has an answer to this question -- and this time around, it's my turn.  :)

I have the same need -- to translate the byte field into actual dates.  It sure LOOKS like it ought to be a simple true/false, 1/0 mapping for the days of the year, but:

• There's more than 366 characters in the string.
• In the calendars I've looked at, the number of 1's appears correct, but the placement doesn't.  i.e. the first byte doesn't seem to represent Jan 1...?

I've enhanced Krum's SQL a tad to better suit my needs:

SELECT OH_Name     "Calendar", 
       OKB_Name    "Event",
       OKD_OKB_LNR "Line",
       OKB_CType   "Type",
       OKD_Year    "Year", 
       OKD_Content "Dates"
FROM OH 
--
LEFT JOIN OKB
ON OKB_OH_Idnr = OH_Idnr
--
LEFT JOIN OKD a
ON OKD_OH_Idnr = OKB_OH_Idnr
AND OKD_OKB_LNR=(select max(OKD_OKB_LNR) 
                        from okd b 
                        where a.okd_oh_idnr=b.okd_oh_idnr)
--
WHERE OH_OType = 'CALE'
AND OKB_CType = 'S'
AND OH_DeleteFlag = 0
--
AND OH_Client = 1000
AND OKD_Year = 2023
--
ORDER BY OKD_Year, OKB_Name;

I'm restricting to one client and one calendar year for my own reasons, and restricting to the highest "line number" (?) to avoid duplicate calendars.  Otherwise, it's basically the original code, tidied up a bit.

Can anyone point me (us) towards some logic to decode OKB_CONTENT into the actual dates -- either the actual SQL (highly preferable), or at least an API?

Thanks... :)

------------------------------
Reed Byers
Analyst/Programmer
Oregon State University
------------------------------

[Andy Wright]

OKD_CONTENT is made up of 372 boolean bytes, with each month "allocated" 31 slots.  So the dates in February (in 2025) will be presented by bytes 32-59, with 60, 61 & 62 unused. March 1 will be represented by byte 63, etc.

This will return the byte position of today's date - 

select (to_char(sysdate, 'MM')-1)*31 + to_char(sysdate, 'DD') from dual;

  

[Philipp Elmer]

Hi,

I found this in my notes (dated 2013 😂), I hope you find it useful!

Only works in non-leap-years, but should be fairly easy to dapot it to work in leap-years.
Change the filter settings for client, calename and event.
The "1" in the "Active" field marks active dates -> you can filter for them in order to retrieve just the dates included in the calender event.

with nums as (
	select ROW_NUMBER() OVER (order by (select 1 from dual)) as n
	from OH
),caledays as (
	select OH_Client, OH_Name, OKB_Name, OKD_Year,
	ROW_NUMBER() OVER (PARTITION by OH_Client, OH_Name, OKB_Name, OKD_Year ORDER BY n) as DayInYear, 
	SUBSTR(OKD_Content, n, 1) as active
	from OH 
	inner join OKB on OH_Idnr = OKB_OH_Idnr
	inner join OKD on OH_Idnr = OKD_OH_Idnr and OKB_Lnr = OKD_OKB_Lnr 
	inner join nums on n <= LENGTH(OKD_Content)
	where OH_DeleteFlag = 0
 	-- filter client, cale, event, current year
	and OH_Client = 1000
	and OH_Name = 'CALE.NEW.1'
	and OKB_Name = 'EVENTNAME'
	and OKD_Year = to_char(sysdate, 'YYYY')
    -- remove non-existing days (only works in non-leap-years)
    and n not in (60, 61, 62, 124, 186, 279, 341)
)
select OH_Client, OH_Name, OKB_Name, OKD_Year, DayInYear, active
, to_char(to_date(DayInYear || '-' || OKD_Year, 'DDD-YYYY'), 'YYYY-MM-DD') as Date_String
, to_date(DayInYear || '-' || OKD_Year, 'DDD-YYYY') as Date_DateFormat
from caledays;

------------------------------
Philipp Elmer

Learn Automic!
Home - PEM Automic

PEM Automic		remove preview
Home - PEM Automic Practice Exercise Master Learn Automic on the world's most interactive and intuitive Automic Online Training Platform Start now How does Automic Training work? We present Automic Training 2.0! Tutorials Enhance your skills effortlessly with our video training, accessible anytime, anywhere. PEM Automic features over 40 tutorials, with new content added weekly. View this on PEM Automic >

------------------------------

[Philipp Elmer]

OK, here's a version that also works for leap years:

--Oracle SQL
with nums as (
	select ROW_NUMBER() OVER (order by (select 1 from dual)) as n
	from OH
),caledays as (
	select OH_Client, OH_Name, OKB_Name, OKD_Year,
	ROW_NUMBER() OVER (PARTITION by OH_Client, OH_Name, OKB_Name, OKD_Year ORDER BY n) as DayInYear, 
	SUBSTR(OKD_Content, n, 1) as active
	from OH 
	inner join OKB on OH_Idnr = OKB_OH_Idnr
	inner join OKD on OH_Idnr = OKD_OH_Idnr and OKB_Lnr = OKD_OKB_Lnr 
	inner join nums on n <= LENGTH(OKD_Content)
	where OH_DeleteFlag = 0
    -- filter client, cale, event, current year
	and OH_Client = 1000
	and OH_Name = 'CALE.NEW.1'
	and OKB_Name = 'EVENTNAME'
	and OKD_Year = to_char(sysdate, 'YYYY')
    -- remove non-existing days (hardcoded, only works in non-leap-years)
    --and n not in (60, 61, 62, 124, 186, 279, 341)
    -- remove non-existing days, also consider leap years -> remove position 60 only if there is a Feb. 29th
    and n not in (
    case 
        when to_date(OKD_Year || '-02-29' default null on conversion error, 'YYYY-MM-DD') is null then 60
        else 0
    end
    , 61, 62, 124, 186, 279, 341)
)
select OH_Client, OH_Name, OKB_Name, OKD_Year, DayInYear, active
, to_char(to_date(DayInYear || '-' || OKD_Year, 'DDD-YYYY'), 'YYYY-MM-DD') as Date_String
, to_date(DayInYear || '-' || OKD_Year, 'DDD-YYYY') as Date_DateFormat
from caledays;

------------------------------
Philipp Elmer

Learn Automic!
https://www.pemautomic.com
------------------------------