Skip to Content
avatar image
Former Member

String_agg function with union

I am creating a scripted view in wihich I have to join two variables with different group by.

var1=select STRING_AGG(CONCAT("A",'@#$%'),'@#$%'ORDER BY TO_DATE("A", 'YYYYMMDD') DESC) AS "A" ,

STRING_AGG(CONCAT("B",'@#$%'),'@#$%'ORDER BY TO_DATE("A",'YYYYMMDD') DESC) as "B" ,

"C",

"D" as "D",

"E",

STRING_AGG(CONCAT("F",'@#$%'),'@#$%' ORDER BY TO_DECIMAL(LTRIM(RTRIM("F"))) DESC) AS "F" FROM TABLENAME

GROUP BY "C" ,"D", "E";

var2=select STRING_AGG(CONCAT("A",'@#$%'),'@#$%'ORDER BY TO_DATE("A", 'YYYYMMDD') DESC) AS "A" ,

STRING_AGG(CONCAT("B",'@#$%'),'@#$%'ORDER BY TO_DATE("A",'YYYYMMDD') DESC) as "B" ,

"C",

STRING_AGG(CONCAT("D",'@#$%'),'@#$%'ORDER BY "D" DESC) as "D",

"E",

STRING_AGG(CONCAT("F",'@#$%'),'@#$%' ORDER BY TO_DECIMAL(LTRIM(RTRIM("F"))) DESC) AS "F" FROM TABLENAME

GROUP BY "C" , "E";

var_out = select

substr_before("A",'@#$%') as "A",

substr_before("B",'@#$%') as "B",

"C",

"D",

"E",

substr_before("F",'@#$%') as "F" from :var1

union all

select

substr_before("A",'@#$%') as "A",

substr_before("B",'@#$%') as "B",

"C",

substr_before("D",'@#$%') as "D",

"E",

substr_before("F",'@#$%') as "F" from :var2;

In above query I am getting 5000 rows in var1 and 0 rows in var2 but in var_out I am 1100 rows how is this possible.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers