BLOB to CLOB convertion and alternate for ORacle UTIL procedure utl_compress.Lz_uncompress

Database
Enthusiast

BLOB to CLOB convertion and alternate for ORacle UTIL procedure utl_compress.Lz_uncompress

Hi,

I am trying to convert below Oracle query into Teradata, let me know if more details required.

SELECT 

dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)) AS detailxml,

instime AS lastmodifieddate,

'O' AS mapping_source

FROM   

h_everest_ordersummary 

WHERE 

type not in ('CALENDARDESK', 'CALENDARWALL', '12X12CALWALL','BOOKBUNDLE','BOOKWALLET','POSTERPRINTS', 'SQUAREPRINTS', 'CLNDR_POSTER')

AND

(

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)), 'SimpleCollageOrder') > 0

OR

                Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)), 'NotebookOrder') > 0 

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)), 'PhotogiftOrder') > 0 

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)), 'PhotoCardOrder') > 0

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detailxml)), 'GreetingCardProject') > 0

)

1 REPLY
Enthusiast

Re: BLOB to CLOB convertion and alternate for ORacle UTIL procedure utl_compress.Lz_uncompress

Hi All,

I want to convert below Oracle query into Teradata, Challenge here is blob_to_clob function and ORacle util procedures. thanks in advance.

SELECT 

dw_core.Blob_to_clob(utl_compress.Lz_uncompress(detail xml)) AS detailxml,

instime AS lastmodifieddate,

'O' AS mapping_source

FROM   

h_everest_ordersummary 

WHERE 

type not in ('CALENDARDESK', 'CALENDARWALL', '12X12CALWALL','BOOKBUNDLE','BOOKWALLET',' POSTERPRINTS', 'SQUAREPRINTS', 'CLNDR_POSTER')

AND

(

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress( detailxml)), 'SimpleCollageOrder') > 0

OR

                Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress( detailxml)), 'NotebookOrder') > 0 

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress( detailxml)), 'PhotogiftOrder') > 0 

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress( detailxml)), 'PhotoCardOrder') > 0

OR

Instr(dw_core.Blob_to_clob(utl_compress.Lz_uncompress( detailxml)), 'GreetingCardProject') > 0

)