All Forums Database
tsreddy02 3 posts Joined 03/15
29 Apr 2015
SQL Server Query to Teradata : XML PATH

I am new to Teradata and working on conversion from SQL Server to Teradata. Is there any way, I can impliment below code in Teradata? Thanks.

SELECT DISTINCT row_date 
                , starttime 
                , logid 
                , CoreSkillList 
                , site_name 
                , SiteName 
                , Replace((select Cast(split AS VARCHAR(50)) + '' as 'data()' 
                           From   #tmpResults b 
                           where  b.row_date = a.row_date 
                                  AND b.starttime = a.starttime 
                                  AND b.logid = a.logid 
                                  AND b.rsv_level = 0 
                           ORDER  BY split 
                           for xml path('')), ' ', ',') SPLITS 
INTO   #tmpFinal 
FROM   (SELECT * 
        FROM   #tmpResults 
        WHERE  rsv_level = 0) a 
ORDER  BY 1 
          , 2 
          , 3 
          , 4 

 

tsreddy02 3 posts Joined 03/15
30 Apr 2015

Any possibility?

dnoeth 4628 posts Joined 11/04
30 Apr 2015

If your TD system supports XML (native or as addon) there's an XML aggregate function. And you better replace the Scalar Subquery with a join:

CREATE VOLATILE TABLE #tmpFinal AS (
   SELECT a.row_date 
          , a.starttime 
          , a.logid 
          , a.CoreSkillList 
          , a.site_name 
          , a.SiteName 
          , b.SPLITS
   FROM #tmpResults AS a
   JOIN
    (
     SELECT
        row_date 
       ,starttime 
       ,logid 
       ,OREPLACE((XMLAGG(TRIM(Split) ORDER BY Split) (VARCHAR(10000))), ' ', ',') AS SPLITS
     FROM #tmpResults 
     WHERE rsv_level = 0
     GROUP BY 1,2,3
    ) b
    ON b.row_date = a.row_date 
   AND b.starttime = a.starttime 
   AND b.logid = a.logid 
   WHERE  rsv_level = 0
 ) WITH DATA ON COMMIT PRESERVE ROWS

 

Dieter

tsreddy02 3 posts Joined 03/15
01 May 2015

Thanks Dieter. I was really looking for something like "XMLAGG". This helps.

You must sign in to leave a comment.