Archives of the TeradataForum

Message Posted: Tue, 23 Jun 2015 @ 12:33:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dear Forum,

Sqltextinfo in dbc.dbqlsqltbl has max size of varchar 31000. So any queries beyond this limit will be split into multiple rows while logging. Is there any way to concatenate these multiple rows into Single SQL row?

We are working on a report where we need to fetch the complete SQL. This row splitting is causing issue in generating the report.

Writing a recursive query and converting SQLtext column to CLOB also failing with reason "LOB's are not allowed to be hashed".

     lock row for access
     WITH RECURSIVE rec_dbqlsql(procid1,queryid1,sqltextinfo1, lvl)
        AS
        (
         SELECT procid, queryid, sqltextinfo (clob (250000)),sqlrowno
         from dbc.dbqlsqltbl
         where sqlrowno=1
         and  (collecttimestamp) = current_date
         union all
         SELECT procid, queryid, sqltextinfo1 || trim(sqltextinfo),sqlrowno
         from dbc.dbqlsqltbl inner join rec_dbqlsql
         on procid=procid1
         and queryid=queryid1
         and  (collecttimestamp) = current_date
         and sqlrowno=rec_dbqlsql.lvl+1
         )
         select procid1 sqltextinfo1, lvl
     from    rec_dbqlsql
     QUALIFY RANK() OVER(PARTITION BY procid1 ORDER BY LVL DESC) = 1;

     Error: EXPLAIN Failed. 5690:  LOBs are not allowed to be hashed.

Any help or suggestions please.

Thanks & Regards,

Sridhar K

	<Prev	Next>		<<First	<Prev	Next>	Last>>