Archives of the TeradataForum

Message Posted: Fri, 25 Sep 2015 @ 09:10:04 GMT

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

Hello Experts,

This issue was discussed already with the below subject:

Subj: Concatenate Sqltextinfo in dbc.dbqlsqltbl -- Tue, 23 Jun 2015 @ 12:33:52 GMT

www.teradataforum.com/20150623_123352.htm

I'm trying to use LOB data type in recursive query like below

     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.

The solution by Frydryszak, Marek was modify the query like below as CLOB datatype used in union cannot be hashed

     lock row for access
          WITH RECURSIVE rec_dbqlsql(procid1,queryid1,sqltextinfo1, lvl)
             AS
             (
              select procid, queryid,
                 cast( max(case when sqlrowno=1 then sqltextinfo  else '' end) as clob(250000) )  ||
                  max(case when sqlrowno=2 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=3 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=4 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=5 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=6 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=7 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=8 then sqltextinfo else '' end) ||
                  max(case when sqlrowno=9 then sqltextinfo else '' end)
                  as sqltextinfo
           from   dbc.dbqlsqltbl
           where cast(collecttimestamp as date)  = current_date group by 1,2
              )
              select procid1 sqltextinfo1, lvl
          from    rec_dbqlsql
          QUALIFY RANK() OVER(PARTITION BY procid1 ORDER BY LVL DESC) = 1;

First check the max of sqlrowno and - if >9 - modify the query

The above solution works well but our current max of sqlrowno exceeds more than 5000 and also it is dynamic.

Is there any way to alter the above solution to make to more flexible and simple. We really don't want to concatenate 5000 sqlrowno.

Any pointers would be greatly appreciated! Thanks!

Regards,

John

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