Solved

SQL - Add negative sign if matlfer.xtype = 'INVSUB'

Posted on 2014-02-21
3
196 Views
Last Modified: 2014-02-21
 SELECT 
        CASE matlxfer.xtype
          WHEN 'ADDINV' THEN '552'
          WHEN 'INVSUB' THEN '551'
        END AS ADJUSTMENT ,
             matlxfer.xfer_qty 

Open in new window


Add negative sign if matlfer.xtype = 'INVSUB'
Data

 matlfer.xtype matlxfer.xfer_qty
ADDINV   1
INVSUB  2
ADDINV 3
INVSUB  4

Open in new window


Our database doesn't have negatives, but I need to send data with them and i've tried variables and other things, but none have worked as I wanted.  Basically I need it to show as this.

matlxfer.xfer_qty
1
-2
 3
-4

Open in new window


Thanks
0
Comment
Question by:gpsdh
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39877370
That would be another CASE block that refers to xtype..
SELECT 
   CASE matlxfer.xtype WHEN 'ADDINV' THEN '552' WHEN 'INVSUB' THEN '551' END AS ADJUSTMENT,
   CASE matlfer.xtype WHEN 'INVSUB' THEN -1 * matlxfer.xfer_qty ELSE matlxfer.xfer_qty  END as xfer_qty 

Open in new window

0
 

Author Closing Comment

by:gpsdh
ID: 39877503
That works!  Thanks!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39877560
btw I have an article out there on SQL Server CASE Solutions that has a wompload of CASE examples.  Hit the 'Yes' button if it helps you out.

Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

777 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