[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
4
Medium Priority
?
583 Views
Last Modified: 2012-05-05
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
Comment
Question by:rwheeler23
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22979343

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

0
 
LVL 26

Expert Comment

by:tigin44
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

by:MohammedU
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

by:
Ernariash earned 2000 total points
ID: 22979576

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question