SQL Script with case and calculation
I need a SQL script that will look at two columns, determine which column is not zero and then multiply that column by a third column.
Example: Select A, B, C from mytable. On each row either B will have a non-zero value or C will, but never both. The end result is that I need to get the one non-zero value and mulitply it by column A. I currently have what is below but I cannot get the syntax right for it to allow the mulitplication. If there is a better way than the Case please let me know.
SELECT Itemnmbr,'PostingCost' =
CASE
WHEN ext_assemble_qty_posting_c
ELSE ext_assemble_qty_posting_c
END
FROM bm30300
Example: Select A, B, C from mytable. On each row either B will have a non-zero value or C will, but never both. The end result is that I need to get the one non-zero value and mulitply it by column A. I currently have what is below but I cannot get the syntax right for it to allow the mulitplication. If there is a better way than the Case please let me know.
SELECT Itemnmbr,'PostingCost' =
CASE
WHEN ext_assemble_qty_posting_c
ELSE ext_assemble_qty_posting_c
END
FROM bm30300
try a script like this
SELECT result =
CASE WHEN column1 <> 0 and column2 = 0 THEN column1
WHEN column2 <> 0 and column1 = 0 THEN column2
END * column3
FROM TableName
SELECT result =
CASE WHEN column1 <> 0 and column2 = 0 THEN column1
WHEN column2 <> 0 and column1 = 0 THEN column2
END * column3
FROM TableName
SELECT Itemnmbr,'PostingCost' =
CASE
WHEN b = 0 THEN a*c
WHEN c= 0 then a*b
END
FROM bm30300
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select *, valfield = (case when afield > 0 then afield else bfield end) * cfield
from tablename