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_cost = 0.00 THEN ext_stock_qty_posting_cost
                  ELSE ext_assemble_qty_posting_cost
     END
FROM bm30300
rwheeler23Asked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:

rwheeler23, There is nothing wrong with your case query..just add the multiplication..please check if your need cast your columns to the proper datatype.
SELECT Itemnmbr,'PostingCost' = 
     CASE
          WHEN ext_assemble_qty_posting_cost = 0.00 THEN ext_stock_qty_posting_cost
                  ELSE ext_assemble_qty_posting_cost
     END * Acolumn
FROM bm30300

Open in new window

0
 
chapmandewCommented:

select *, valfield = (case when afield > 0 then afield else bfield end) * cfield
from tablename

0
 
tigin44Commented:
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
0
 
MohammedUCommented:

SELECT Itemnmbr,'PostingCost' =
     CASE
          WHEN b = 0 THEN a*c
          WHEN c= 0 then a*b  
     END
FROM bm30300
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.