Query a 4 byte RAW Data field and convert bit settings to a string val 1

[ljsmith91]

Hi all,

I have no idea how to go about this and any direction would be a great help.

I need to query a 4 byte raw data field in a table and convert the bits to string values. The db layout manual has a mapping for this field:


From the DB Manual:

field_name = day_of_week (RAW 4-bytes)

This field contains a bit mask where each bit
reflects what days of a week a calendar based
schedule will execute. The following is a list
of the bit patterns within this bit mask:
0 = Every Sunday
1 = Every Monday
2 = Every Tuesday
3 = Every Wednesday
4 = Every Thursday
5 = Every Friday
6 = Every Saturday

How would I query the schedule table for this field and map to the appropriate string(s). I would have to account for all bits set or only a few or 0 or 1 and output a string that would make sense like 'S-M-T-W-Th-F-Sa' or 'M-W-F' or 'T-Th'. I just have no idea how to code something like that up in sql.

Sample data in the day_of_week field for the schedule table is:

record1 = '00004001'
record2 = '00080020'
record3 = '04081020'
record4 = '0FFFFFFF'

Please, any direction would be a great help as I have never come across the need to do this before.

Thanks.

ls

 

[Dagon]

Not something I've done before, but I guess you could do something like convert the raw to a hex value using HEXTORAW, then convert it to a float using TO_CHAR(nnnnnn, 'XXXXXX'). Having got it has an integer, you could then use the BITAND function to work out which bits are set on and off. The DUMP function might also be useful.

 

[Dagon]

I was quite interested in this, so have had a go at providing a solution. However, I'm a bit puzzled by your data. If it's supposed to represent 7 days of the week, then all you would need would be one byte and the highest value you could have would be with all 7 bits set or 127 (7F in hexadecimal). Therefore, I'm not sure why your data is showing values like '0FFFFFFF' (assuming that's a hex representation of the value).

At any rate, the solution I have looks like this:

SQL> create table raw_vals (colval raw(8))
Table created.
SQL> insert into raw_vals values (hextoraw('00000001'))
1 row created.
SQL> insert into raw_vals values (hextoraw('00000002'))
1 row created.
SQL> insert into raw_vals values (hextoraw('00000003'))
1 row created.
SQL> insert into raw_vals values (hextoraw('0000007F'))
1 row created.
SQL> select regexp_replace(
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),1), 1,'S-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),2), 2,'M-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),4), 4,'T-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),8), 8,'W-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),16),16,'Th-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),32),32,'F-',null)||
       decode(BITAND(to_NUMBER(rawtohex(colval), 'XXXXXXXX'),64),64,'Sa-',null), '-$') days_to_run
 from raw_vals

DAYS_TO_RUN     
----------------
S               
M               
S-M             
S-M-T-W-Th-F-Sa

 

[ljsmith91]

Hi Dagon,

Thanks so much for the reply. I do not understand the 4 bytes either. I am going to put your code in play and see if it translates it as is expected. Thanks so much for the help. I have never come across this before so it's good to learn through this forum from folks like yourself who know sql so well.

ljs