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
------------------------------