6 Replies Latest reply: Oct 5, 2015 7:11 AM by Marcus Sommer RSS

    hextoraw() from Oracle SQL Select

    Deborah Pyykkonen


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

          PS:  I am doing this in the SQL Select statement

          • Re: hextoraw() from Oracle SQL Select
            Marcus Sommer

            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

              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

                  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

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

                              = 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
                  [C:\_ISOLATE\TEXT_TO_RAW.txt]
                  (
                  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);