Tuesday, March 27, 2012

CASE statement

Trying to calculate a value using a CASE statement - any thoughts?
Thanks.
SELECT
CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
'VAL'
WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
as 'VAL'
FROM
T1
JOIN T2 ON T2.TICKNUM = T1.TICKNUM
JOIN C1 ON C1.CURR_CODE = T1.CURRENCYPlease post the complete table DDL and perhaps a little more detail about wh
at you are attempting to accomplish. Some sample data in the form of INSERT
statements and what your expected output looks like would be helpful.
I'm about valuex and value1.
General comments about using CASE. The form is usually like this:
SELECT
CASE
WHEN {expression} THEN {value}
WHEN {expression} THEN {value}
ELSE {Value}
END AS 'VAL'
FROM ...
You seem to have left out the [END]
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<RBurns6@.gmail.com> wrote in message news:1151419826.520556.136270@.i40g2000cwc.googlegroups
.com...
> Trying to calculate a value using a CASE statement - any thoughts?
> Thanks.
>
> SELECT
> CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
> 'VAL'
> WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
> as 'VAL'
> FROM
> T1
> JOIN T2 ON T2.TICKNUM = T1.TICKNUM
> JOIN C1 ON C1.CURR_CODE = T1.CURRENCY
>|||RBurns6@.gmail.com wrote:
> Trying to calculate a value using a CASE statement - any thoughts?
> Thanks.
> SELECT
> CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
> 'VAL'
> WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
> as 'VAL'
> FROM
> T1
> JOIN T2 ON T2.TICKNUM = T1.TICKNUM
> JOIN C1 ON C1.CURR_CODE = T1.CURRENCY
>
Based on what you've given us, I'm guessing at column names for the
where clause, but this should be close:
SELECT
CASE
WHEN valuex = 1 THEN cost * spot
WHEN valuex = 0 THEN cost / spot
END AS Val
FROM T1
INNER JOIN T2
ON T1.ticknum = T2.ticknum
INNER JOIN C1
ON T1.curr_code = T2.curr_code
WHERE currency = curr_code
AND valuex IN (0, 1)

No comments:

Post a Comment