Case Statement Review 2

[hramos25]

Dear Sir or Madam,

I have the below query that pulls Direct Deposit Info by Employee. I am trying to place a case statement that will replace if then 01 etc. but I am not getting the results in the case, would you please review the statement, I really appreciate your help in advance James

declare @empid as nvarchar(10)
declare @Bankacct00 as nvarchar(17)
declare @Bankacct01 as nvarchar(17)
declare @Bankacct02 as nvarchar(17)
declare @Bankacct03 as nvarchar(17)
declare @Bankacct04 as nvarchar(17)
declare @Bankacct05 as nvarchar(17)
declare @transit00 as nvarchar(9)
declare @transit01 as nvarchar(9)
declare @transit02 as nvarchar(9)
declare @transit03 as nvarchar(9)
declare @transit04 as nvarchar(9)
declare @transit05 as nvarchar(9)
declare @fulldeposit as nvarchar(1) -- Leave blank for No, and 'Y' for yes
declare @Amount00 as float
declare @Amount01 as float
declare @Amount02 as float
declare @Amount03 as float
declare @Amount04 as float
declare @accttype00 as nvarchar(2)
declare @accttype01 as nvarchar(2)
declare @accttype02 as nvarchar(2)
declare @accttype03 as nvarchar(2)
declare @accttype04 as nvarchar(2)
declare @sql as nvarchar(255)
declare @counter as int
declare @acctcol as nvarchar(2)
declare @ssn as nvarchar(9);
set @sql = @sql + 'select rtrim(ltrim(accttype00' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype01' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype02' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype03' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype04' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype05' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit00' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit01' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit02' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit03' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit04' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit05' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct00' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct01' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct02' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct03' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct04' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct05' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' amount00' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount01' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount02' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount03' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount04' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + '''''' + ','
set @sql = @sql + @empid + ','
set @sql = @sql + ' from dddepositor where empid = ' + @empid;
select EmpID as FileNBR,AcctType00 as accttype00,AcctType01 as accttype01,AcctType03 as accttype03,AcctType04 as accttype04,
AcctType05 as accttype05,BankTransit00 as TransitNBR00,BankTransit01 as TransitNBR01,BankTransit03 as TransitNBR00BankAcct03,BankTransit04 as TransitNBR04,BankTransit05 as TransitNBR05,BankAcct00 as ACCTNBR00,BankAcct01 as ACCTNBR01,
BankAcct02 as ACCTNBR00Amount02,BankAcct03 as ACCTNBR03,BankAcct04 as ACCTNBR04,BankAcct05 as ACCTNBR05,
Amount00 as DepositAMT00,Amount01 as DepositAMT01,Amount02 as DepositAMT02,Amount03 as DepositAMT03,Amount04 as DepositAMT04,
Case
When AcctType00 = 'c' then 01
When AcctType01 = 's' then 12
When AcctType02 = 'c' then 10
When AcctType03 = 's' then 13
When AcctType04 = 'c' then 11
When AcctType05 = 's' then 14
Else ' '
End
from dddepositor
Order by EmpId

 

[genomon]

Try:

Case AcctType00
    When 'c' then 01
    When 's' then 12......

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[gmmastros]

I only see 1 potential problem with your query.

[tt]
Case
When AcctType00 = 'c' then [blue]01[/blue]
When AcctType01 = 's' then [blue]12[/blue]
When AcctType02 = 'c' then [blue]10[/blue]
When AcctType03 = 's' then [blue]13[/blue]
When AcctType04 = 'c' then [blue]11[/blue]
When AcctType05 = 's' then [blue]14[/blue]
Else [green]' '[/green]
End
[/tt]

Notice the parts highlighted in blue. When this statement runs, those values will be converted to Integer (data type). The green part will ALSO be converted to an integer. When you convert a space to integer, the value returned will be 0.

[tt][blue]Select Convert(Int, ' ') [green]-- Returns 0[/green][/blue][/tt]

If you want to preserve the leading 0, then you must return a 'string' data type (like char, nchar, varchar, nvarchar etc...). I would suggest this....

[COLOR=blue]Case[/color]
    [COLOR=blue]When[/color] AcctType00 = [COLOR=red]'c'[/color] [COLOR=blue]then[/color] N[COLOR=red]'01'[/color]
    [COLOR=blue]When[/color] AcctType01 = [COLOR=red]'s'[/color] [COLOR=blue]then[/color] N[COLOR=red]'12'[/color]
    [COLOR=blue]When[/color] AcctType02 = [COLOR=red]'c'[/color] [COLOR=blue]then[/color] N[COLOR=red]'10'[/color]
    [COLOR=blue]When[/color] AcctType03 = [COLOR=red]'s'[/color] [COLOR=blue]then[/color] N[COLOR=red]'13'[/color]
    [COLOR=blue]When[/color] AcctType04 = [COLOR=red]'c'[/color] [COLOR=blue]then[/color] N[COLOR=red]'11'[/color]
    [COLOR=blue]When[/color] AcctType05 = [COLOR=red]'s'[/color] [COLOR=blue]then[/color] N[COLOR=red]'14'[/color]
[COLOR=blue]Else[/color] N[COLOR=red]' '[/color]
[COLOR=blue]End[/color]

By returning a string, you will preserve the leading zero and if there are no matches, you will return a space (from the ELSE part).

Make sense?

-George

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