4

Is there any equivalent of Listagg function of oracle in teradata. With recursive is an option I have tried, but wanted something other than.

Thanks Aritra

Aritra Bhattacharya
  • 720
  • 1
  • 7
  • 18

1 Answers1

6

If Teradata's XML-services are installed (default since 14.10?) there's a function named XMLAGG, e.g.

SELECT DatabaseName, TABLENAME, 
   TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Columnname)|| ',' ORDER BY ColumnId) (VARCHAR(10000))))
FROM dbc.ColumnsV
WHERE databasename = 'dbc' 
  AND TABLENAME = 'ColumnsV'
GROUP BY 1,2
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • But one problem. Is there any problem regarding its length, because whenever I am trying to integrate it on a comparatively larger table it is throwing me the error : Failure 2513 A data row is too long. – Aritra Bhattacharya Apr 16 '15 at 06:53
  • could you please help me with the above issue @dnoeth – Aritra Bhattacharya Apr 16 '15 at 09:33
  • Try to cast it to a smaller VARCHAR. Do you have some other columns with functions like oReplace/oTranslate? Cast them to a matching size, too. – dnoeth Apr 16 '15 at 12:09
  • tried casting ting it to a smaller varchar , varchar(50) to be exact. But still the same error. Any other way out . @dnoeth – Aritra Bhattacharya Apr 20 '15 at 07:17
  • Sure. SELECT colA , colB , colC , colD , colE , XMLAGG((colF)|| ',' ORDER BY rank_col) (VARCHAR(50)) col from table group by colA , colB , colC , colD , colE ; @dnoeth – Aritra Bhattacharya Apr 21 '15 at 05:29
  • Well, this is not the actual SQL :-) What's the datatypes of those columns? – dnoeth Apr 21 '15 at 06:00
  • :D . All the data types are varchar and rank_col is INTEGER. This is the actual SQL . INSERT INTO supplier_app.wrk_apn_class_custprg_multimp ( oem_site_cd , oem_part_nr , apple_part_nr , prod_class_id , prod_class_desc , custom_program_cd ) SELECT oem_site_cd , oem_part_nr , apple_part_nr , prod_class_id , prod_class_desc , XMLAGG((program_cd)|| ',' ORDER BY rank_col) (VARCHAR(40)) custom_program_cd from supplier_app.wrk_apn_class_custprg_rnk group by oem_site_cd , oem_part_nr , apple_part_nr , prod_class_id , prod_class_desc ;@dnoeth. – Aritra Bhattacharya Apr 21 '15 at 07:07
  • well I did one thing. I had the prod desc col which had data type precision of 100. I removed that from my select query and the query ran through just fine. But is that a permanent solution. @dnoeth – Aritra Bhattacharya Apr 21 '15 at 07:26
  • When you need this columns you can't remove it :-) Is there any other large VarChar? Based on the column names all but prod_class_desc seem to be short. You hit this error when the size hits the 64K limit, so Unicode VarChars up to 30.000 characters should be fine. Is VARCHAR(40) large enough for the expected result? Then it's a very small number of program_cd and you could do the same using SUM(CASE) instead of XMLAGG or recursion... – dnoeth Apr 21 '15 at 10:07
  • actually prod class desc is a column which is not a mandatory one (luckily its the one we can live without :-) ) and regarding the program code , i have increased the precision to 1000 , even then the error is not thrown. But its a workaround nevertheless :P . But whats confusing me is the fact that how come the size is hitting 64K limit only because of the prod_class_desc column, because the prod class desc usually contains max 50-60 characters. I had changed the program code precision, just for testing, ideally it shud be around 300-400 chars long. – Aritra Bhattacharya Apr 21 '15 at 12:17
  • program code field is short , but the record counts for program codes are huge, we have to concatenate the program codes based on the other columns in the table ( apart from prod class desc, since granularity of prod class id and prod class desc is same ). With recursive will hit the performance of procedure big time since the records set it will operate on is huge, and SUM CASE won't be a generalised one i guess since we have to hardcode the upper limit for it. Thats y i was trying out a generalised approach for concatenating all the program codes without hitting performance. – Aritra Bhattacharya Apr 21 '15 at 12:21
  • I knew about LISTAGG in oracle, it was exactly the function i needed to carry out my goal, but I was unable to find its equivalent in Teradata, thats when u came to the rescue thankfully .. :-) . Now its working fine both performance wise and functionality wise, but if u could just help me out with the confusion mentioned earlier it would be really helpful. – Aritra Bhattacharya Apr 21 '15 at 12:24
  • Any VarChar will be expanded to a fixed Char of the defined size when it's used in any GROUP or ORDER BY, that's why it's important to define VarChars of correct size. – dnoeth Apr 21 '15 at 17:03