Hi Everyone,
I have the following case statement in my query, and I am looking to see if anyone could give me an idea of how to rework this so I dont get multiple rows in my output
Basically I have a field which should ideally be 8 characters in length, then its not I add leading zero's so i can do my corect substring
As you can tell I'm testng when the lenght is 6, 7, and 8
If its not any of these 3 then i use a defaault value of 0 for my column.
My problem is when i do my group by , i get 0 thress times because I'm its meeting my conditions three times how can i restructure this so in my ouput I only have one column for 0's
Any help would be apprciated
I have the following case statement in my query, and I am looking to see if anyone could give me an idea of how to rework this so I dont get multiple rows in my output
Basically I have a field which should ideally be 8 characters in length, then its not I add leading zero's so i can do my corect substring
As you can tell I'm testng when the lenght is 6, 7, and 8
If its not any of these 3 then i use a defaault value of 0 for my column.
My problem is when i do my group by , i get 0 thress times because I'm its meeting my conditions three times how can i restructure this so in my ouput I only have one column for 0's
Any help would be apprciated
Code:
case
when len(acc.custom_data_91) = 6 then substring('00' + acc.custom_data_91,1,3) + substring('00' + acc.custom_data_91,6,3)
when len(acc.custom_data_91) = 7 then substring('0' + acc.custom_data_91,1,3) + substring('0' + acc.custom_data_91,6,3)
when len(acc.custom_data_91) = 8 then substring(acc.custom_data_91,1,3) + substring(acc.custom_data_91,6,3)
else '0' end as ColumnA