topic Re: hextoraw() from Oracle SQL Select in QlikView

hextoraw() from Oracle SQL Select

Tue, 29 Sep 2015 15:35:52 GMT

I have 2 oracle tables I need to link together but one stores the value in raw(16) and the other in the hex format.

E5BE826A421AO8CBEFECF4YI9737303CF1A (RAW EXAMPLE) tried rawtohex(ID) but returned same value

5BA82BEE-A041-84C8-BFEC-J49DKG74KFL4 (HEX EXAMPLE) tried hextoraw(ID) "NEW ID" but gave me invalid hex

neither are actual.

I want to convert one to the other to link them. what is the best way to do this in load script .

I tried hextoraw(ID) "NEW ID" but no luck

Re: hextoraw() from Oracle SQL Select

Tue, 29 Sep 2015 16:40:46 GMT

PS: I am doing this in the SQL Select statement

Re: hextoraw() from Oracle SQL Select

marcus_sommer — Tue, 29 Sep 2015 23:47:41 GMT

If I understand this The Oracle PL/SQL RAW Datatype right is raw already stored as hex. Your second example contained "-" signs which didn't belonged directly to hex. It could be simply a kind of delimiter between combined content like a dialing code and phone number or similar. Maybe it need to be removed with functions like keepchar() or purgechar().

Another possibility could be to make those linking within qlikview and you used some kind of converting there: Convert hex to decimal

- Marcus

Re: hextoraw() from Oracle SQL Select

evan_kurowski — Wed, 30 Sep 2015 04:13:28 GMT

Hello Debbie,

The letters above 'F' on one side of your join does appear something isn't being stored or converting to HEX.

If I was going to paraphrase your SQL side syntax, you are attempting something like this:

LOAD

RESULT_FIELD

FROM table1 T1, table2 T2

WHERE RAWTOHEX(T1.STORED_AS_RAW_FIELD) = T2.STORED_AS_HEX_FIELD

It is possible to perform the HEX & BINARY conversions on the QlikView side, and form key pairs on HEX values, or even raw binary patterns. Here's an example of a conversion in both directions RAW2HEX & HEX2RAW (<~these are not functions, just descriptive field names)

[RAW]:
LOAD * INLINE [
RAW
00000001
11110100
100101101100
11
1111111111111111
];

[RAW_TO_HEX]:
LOAD HEX_ID, CONCAT(HEX, ' ', RecNo()) AS RAW_SPACED, CONCAT(Num(Num#(HEX,'(BIN)'),'(HEX)'),'',RecNo()) AS RAW2HEX GROUP BY HEX_ID;
LOAD RecNo() AS HEX_ID, Num(Mid(RAW,IterNo()*4 - 3,4),'0000') AS HEX RESIDENT RAW
WHILE IterNo() <= RangeMax(1,Len(RAW)/4);

[HEX_TO_RAW]:
LOAD HEX_ID, CONCAT( Right('0000' & Num(Num#(RAW,'(HEX)'),'(BIN)'),4),'',RAW_ID) AS HEX2RAW GROUP BY HEX_ID;
LOAD HEX_ID, IterNo() AS RAW_ID, Mid(RAW2HEX,IterNo(),1) AS RAW RESIDENT [RAW_TO_HEX]
WHILE IterNo() <= Len(RAW2HEX);

Re: hextoraw() from Oracle SQL Select

evan_kurowski — Wed, 30 Sep 2015 11:46:18 GMT

This has issues though because it will have trouble with '0000' and also is reading from left to right, so '11100' doesn't come out right.. I'm going to tweak this. But it isn't really consuming a RAW stream of binary, it is taking a string representation of binary and converting 4 bytes of 1 & 0 chars and evaluating. Hmm...

Re: hextoraw() from Oracle SQL Select

evan_kurowski — Fri, 02 Oct 2015 02:00:28 GMT

Man, I couldn’t wait to rush home from work today (where I attempt to program) so I could get back on Community and talk more about RAW! THIS is Vegas!! Regardless of fixing these flawed HEX to BIN conversions that I offered up earlier, I am rethinking the whole approach of the RAW data type.

While Oracle appears to be taking in a stream of binary, not sure how that is done in QlikView.

My example ‘simulates’ interpreting binary, but actually using a long string of ‘1’ and ‘0’ text characters is probably an inefficient way to simulate RAW data. The symbol ‘0’ is ascii code 48 = 00110000 and ‘1’ is ascii code 49 = 00110001, so every ‘1’ or ‘0’ character supposedly representing 1 bit is still uses 8 bits to render its encapsulated data. So it is encoding at 1/8 efficiency.

Using num() values of 1,000,101, 1,100, etc.. is not the way to go either because binary doesn’t belong in a 10 radix system.

Hex seems like it naturally adds efficiency. For each HEX byte character that represents 4 bits of binary, it requires 8 bits to encapsulate the representing character. The store efficiency is ½. For every byte required to store a HEX character, you can encapsulate and retrieve 4 bits of data.

However, in the present (maybe there are some limitations I just don’t really understand), but in the present why not just consume whole bytes? Once we get out to Unicode possibilities maybe we can cover the whole 256 range in one character?

All the RAW data type really appears to be is a field that stores binary sequences the same way a text field would, but has a data-type name that tips you off that you aren’t to attempt making any sense from interpretation of the character combinations. The characters returned from a query of a RAW data-type field exist solely to encapsulate byte sequences.

The extended ASCII table seems like a natural choice to encapsulate our RAW data type, it offers binary coverage in exact byte-size, from 00000000 to 11111111. There doesn’t seem to be exclusive areas in terms of binary sequence possibilities when comparing a RAW field against an ASCII field, except that one is supposed to “make sense” and the other isn’t.

Attached is an example that seems to be having some initial positive results in encapsulating binary as a ‘TEXTRAW’ data type. There were 4 characters giving trouble re-converting back to binary when loading from the source .txt. These were 0 – Null, 10 – Line Feed, 12 – Form Feed, & 13 – Carriage Return. If anyone has any ideas how to parse them and extract their binary, the source file could be captured as extended ASCII. But in this case, the exceptions were moved to replacement characters above 255.

I think looking at the efficiency, the first 128 characters, less the 4 replacements will all encode at a 1:1 ratio. 1 character encapsulates and allows reading 8 bits of raw.

Then in the worst case scenario for efficiency at that point is the 4-replacements & the range from 128-255 all require 2-bytes to render. (If any of the characters above 128 could encode in 1-byte, this ratio would be even better)

124 characters at 8 bit encoding - 8 bit storage
132 characters at 8 bit encoding - 16 bit storage

HEX encoding of 256 characters

= encapsulates 1024 bits of data (256 x 4 bits)

= requires 2048 memory bits to store (256 x 8 bits)

= 1024 bits encoded / 2048 bits of memory to store = 50%

TEXTRAW encoding of 256 characters

= encapsulates 2048 bits (256 x 😎

= requires 3104 memory bits = (124 x 8 = 992) + (132 x 16 = 2112)

= 2048 bits encoded / 3104 bits of memory to store = 66%

(if it weren’t for those 4 characters giving trouble, this could’ve been 100%)

[HEX]:
LOAD HEX INLINE [
HEX
E5BE826A421AO8CBEFECF4YI9737303CF1A
];

[HEX_TO_RAW]:
LOAD *, PurgeChar(HEX2RAW_SplitByHEX,'|') AS HEX2RAW;
LOAD CONCAT( TEXT(Right('0000' & Num(Num#(RAW,'(HEX)'),'(BIN)'),4)),'|',RAW_ID) AS HEX2RAW_SplitByHEX;
LOAD IterNo() AS RAW_ID, Mid(HEX,IterNo(),1) AS RAW
RESIDENT [HEX]
WHILE IterNo() <= Len(HEX);

[INVALIDS]:
LOAD *, ORD + 256 AS REPLACE INLINE [
ORD
0
10
12
13
];

[MAP_INVALIDS]:
MAPPING LOAD ORD, REPLACE RESIDENT [INVALIDS];
[MAP_VALIDS]:
MAPPING LOAD REPLACE, ORD RESIDENT [INVALIDS];

Set Verbatim = 1;

[RAW_TO_STORAGE]:
LOAD CONCAT(BYTE,'|',BYTE_ID) AS HEX2RAW_SplitByBYTE,
CONCAT(
CHR( ApplyMap('MAP_INVALIDS', Num#(BYTE,'(BIN)') ) ) ,'',BYTE_ID) AS RAW2TEXT;
LOAD IterNo() AS BYTE_ID, Right(Repeat('0',8) & Mid(HEX2RAW,IterNo()*8-7,8),8) AS BYTE
RESIDENT [HEX_TO_RAW]
WHILE IterNo() <= RangeMax(1,Ceil(Len(HEX2RAW)/8));

[TEXT_TO_RAW]:
LOAD
RecNo() AS ROW,
[@1:n] AS TEXT_INCOMING
FROM

(fix, utf8, no eof)
WHERE RecNo() = 1;

[TEXT_TO_RAW_CONVERSION]:
LOAD ROW, CONCAT(RAW,'|',RAW_ID) AS TEXT2RAW_ByByte,
CONCAT( Ord(Replace(RAW,chr(300),chr(00))) ,'|',RAW_ID) AS TEXT2RAW_Ord,
CONCAT( Mod(Ord(RAW),256) ,'|',RAW_ID) AS TEXT2RAW_Ord2,
CONCAT( Right('00000000' & Num(Mod(Ord(RAW),256),'(BIN)'),8),'|',RAW_ID) AS TEXT2RAW
GROUP BY ROW;
LOAD ROW, IterNo() AS RAW_ID, Mid(TEXT_INCOMING,IterNo(),1) AS RAW
RESIDENT [TEXT_TO_RAW]
WHILE IterNo() <= Len(TEXT_INCOMING);

Re: hextoraw() from Oracle SQL Select

marcus_sommer — Mon, 05 Oct 2015 11:11:32 GMT

A quite heavy function to convert hex could be found within this application: License_Analyser_K3FDS.qvw

It seems that sometimes it goes not easier (if there aren't native inbuilt functionalities) ...

- Marcus