Case Statement Issues

[PRUSA]

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

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

 

[gmmastros]

You should be able to replace your case statement with this...

[tt][blue]SubString(Right('00000000' + acc.custom_data_91, 8), 1, 3) + SubString(Right('00000000' + acc.custom_data_91, 8), 6, 3) As ColumnA[/blue][/tt]

If this works for you, and you'd like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Actually, that can be reduced down to this...

[tt][blue]
SubString(Right('00000000' + acc.custom_data_91, 8), 1, 3) + Right(acc.custom_data_91, 3) As ColumnA
[/blue][/tt]

or even this...

[tt][blue]
Left(Right('00000000' + acc.custom_data_91, 8), 3) + Right(acc.custom_data_91, 3) As ColumnA
[/blue][/tt]

** These suggestions are more about readability than anything else.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PRUSA]

thanks gmmastros
for getting back to me i really appreciate,

I'm going to test this out on Monday, due to the heavy snow everyone is getting out early

I'll let you know monday

thanks again,

 

[PRUSA]

Actually this is not working, all its adding is leading 0'z to my columns for for example a column that has 1234567 now becomes, 000000001234567, and when i do a substring a position one for 3 spaces I'm not getting the information I would need?

Any suggestions let me know if I need to clarify more


Thanks Again,
Sergio

 

[PRUSA]

The code worked!!

SubString(Right('00000000' + RTrim(acc.custom_data_91), 8), 1, 3) + SubString(Right('00000000' + RTrim(acc.custom_data_91), 8), 6, 3) As ColumnA

It wasn't working before because it didnt realize my existing field already had a blank spaces in it, so basically I put the RTrim function

Thanks for your help again with this.