asked on

select case change 2 column values

I am using the below SQL sytax to add a dummy column and move vales to that column.
SELECT ID, WO_Number, ItemID, QtyUsed, Units, Feet, CASE WHEN Feet > 0 THEN Feet ELSE QtyUsed END AS QtyUsed2
FROM TRX_ROW_INV_DRI

what also need to do is that when Feet > 0, also move Units to another dummy column 'Units2' and then change the value to 'FT'.

Any idea on how to accomplish this?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vwalla

ASKER

Got. It was easier than I thought.

SELECT ID, WO_Number, ItemID, QtyUsed, Units, Feet, CASE WHEN Feet > 0 THEN Feet ELSE QtyUsed END AS QtyUsed2,
CASE WHEN Feet > 0 THEN 'FT' ELSE Units END AS Units2
FROM TRX_ROW_INV_DRI

vwalla

ASKER

We posted at the same time, but you were quicker! Thanks!

gplana

Not sure if I fully understood your question, but you can execute this select:

SELECT ID, WO_Number, ItemID, QtyUsed, Units, Feet, CASE WHEN Feet > 0 THEN Feet ELSE QtyUsed END AS QtyUsed2, CASE WHEN Feet > 0 THEN Units ELSE NULL END AS Units2
FROM TRX_ROW_INV_DRI

What do you mean about change the value to FT ?

Sean Stuber

your conditions don't seem to correspond to your explanation.

>>>>> when Feet > 0, also move Units to another dummy column 'Units2' and then change the value to 'FT'.

>>CASE WHEN Feet > 0 THEN 'FT' ELSE Units END AS Units2

This will move units to unit2 when feet <= 0 or when feet is null

is that really what you want?

in any case, it seems you're on the right track, just make sure your > and < are correct.