[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL Script with case and calculation

Posted on 2008-11-17
Medium Priority
583 Views
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
0
Question by:rwheeler23

LVL 60

Expert Comment

ID: 22979343

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

0

LVL 26

Expert Comment

ID: 22979359
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

LVL 15

Expert Comment

ID: 22979362

SELECT Itemnmbr,'PostingCost' =
CASE
WHEN b = 0 THEN a*c
WHEN c= 0 then a*b
END
FROM bm30300
0

LVL 9

Accepted Solution

Ernariash earned 2000 total points
ID: 22979576

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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
###### Suggested Courses
Course of the Month18 days, 18 hours left to enroll