case statement using cast

Hi guys,

The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'

I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".

In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.

Please Help.

"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,

Aldo.

Hi,

What is the errormessage?

Is it possible that there are non-nummeric values at these positions?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

the data in the field is a string containing nummeric characters...

I solved the problem using the code below:

SLC = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)= 0 then 'Zero'" _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)>= 1 " _
& "And CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)<= 699 then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values', "

The ERROR in the code I uploaded earlier was using the word "Case" in both cases:

... Case When ...

...Case When...

instead of:

... Case

When

When

This code is working too:

sqlString = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)>= '001' And SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)<= '699' then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values' "

I'll be glad to learn some other good idea.

Thanks,

Aldo.|||Just an FYI, this is a transact-sql question, not an SSIS question. To tie this into SSIS, you could avoid doing the case statement in your SQL and do it in a derived column transformation.

CASE Query

Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL.
For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?? Query:

SELECT CASE PositionType
WHEN PositionType '1' THEN 'BUY'
WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY ContractFor starters, there's no need to repeat PositionType in the WHEN lines. You've specified it in the CASE line. Secondly, if the 1 or -1 is a numeric value, they shouldn't be surrounded by quotes. Third, the "}" is wrong. Fourth, no END.|||Hi,

You can re-edit the CASE statement as
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],

SELECT
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],
CAST(TradePrice as float(20,8) ) AS [Price],
Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike,
expiringdate,
comment,
SUBSTRING(contract+CONVERT(varchar,expiringdate),1 ,20) AS [FEEDCODE]
FROM TS_Positions
WHERE
(Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY Contract

Eralper
http://www.kodyaz.com|||Thanks Eralper! That worked but i decided to do it this way.

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S]...

Is there anyway where i can put on there to NOT show the NONE for the else? So i would only show the B(1) and S(-1).

Also on my query i would like to add 2 new columns at the end for example:

select col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets

Soo i should put that at the end of my query so it will look like this correct?

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE], col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY PositionType

2 new columns for my query would be Portfolio and Markets at the end..
Right now the columns without the config has:
B/S comment Price Volume Kind Strike Expiringdate Feedcode

New query would include 2 columns
B/S comment Price Volume Kind Strike Expiringdate Feedcode Portfolio Markets