?
Solved

SQL Case Statement

Posted on 2010-11-08
5
Medium Priority
?
361 Views
Last Modified: 2012-05-10
I have the following SQL query. I need to write a case statement that converts LOBtotal which is an int field to a negative number when it is positive and a positive number to negative. I am not certain how to do this.

SELECT LOBTOB
            ,LOB_LOB
            ,BatchID = 'IS4WPAWP'
            ,DocNbr = 'CUEPrem'
            ,[Account Nbr] = CASE
                                           WHEN LOB_LOB = 'CM' THEN '01-5100-00CM00-010000-00'
                                           WHEN LOB_LOB = 'OC' THEN '01-5100-00OC00-010000-00'
                                           WHEN LOB_LOB = 'GL' THEN '01-5100-00GL00-010000-00'
                                           WHEN LOB_LOB = 'Tails' THEN '01-5100-00TL00-010000-00'
                                           WHEN LOB_LOB = 'Mini-Tails' THEN '01-5100-00MTL0-010000-00'
                                           WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN '01-5100-00PTL0-010000-00'
                                           WHEN LOB_LOB = 'Adjustment' THEN ''
                                          
                                      END
            ,LOBtotal
            ,[Description] = CASE
                                           WHEN LOB_LOB = 'CM' THEN 'CM CUEP'
                                           WHEN LOB_LOB = 'OC' THEN 'OC CUEP'
                                           WHEN LOB_LOB = 'GL' THEN 'GL CUEP'
                                           WHEN LOB_LOB = 'Tails' THEN 'TL CUEP'
                                           WHEN LOB_LOB = 'Mini-Tails' THEN 'MTL CUEP'
                                           WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN 'PTL CUEP'
                                           WHEN LOB_LOB = 'Adjustment' THEN ''
                                          
                                      END
                              
FROM dbo.LOB
WHERE lobtob = 'Direct Written'
0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 8

Expert Comment

by:OBonio
ID: 34083854
0 - LOBtotal

If LOBtotal = 5
0 - 5 = -5

If LOBtotal = -5
0 - - 5 = 5 (two minuses = a plus)
0
 

Author Comment

by:mburk1968
ID: 34083876
How would that look with the case tag wrapped around it. I apologize in advance for my ignorance.
0
 
LVL 8

Accepted Solution

by:
OBonio earned 2000 total points
ID: 34083897
LOBtotal isn't in the CASE statements, it's on its own.

SELECT LOBTOB
             ,LOB_LOB
            ,BatchID = 'IS4WPAWP'
             ,DocNbr = 'CUEPrem'
            ,[Account Nbr] = CASE
                                            WHEN LOB_LOB = 'CM' THEN  '01-5100-00CM00-010000-00'
                                            WHEN LOB_LOB = 'OC' THEN '01-5100-00OC00-010000-00'
                                            WHEN LOB_LOB = 'GL' THEN  '01-5100-00GL00-010000-00'
                                            WHEN LOB_LOB = 'Tails' THEN '01-5100-00TL00-010000-00'
                                            WHEN LOB_LOB = 'Mini-Tails' THEN  '01-5100-00MTL0-010000-00'
                                            WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN  '01-5100-00PTL0-010000-00'
                                            WHEN LOB_LOB = 'Adjustment' THEN ''
                                           
                                      END
             ,0 - LOBtotal
            ,[Description] = CASE
                                            WHEN LOB_LOB = 'CM' THEN 'CM CUEP'
                                            WHEN LOB_LOB = 'OC' THEN 'OC CUEP'
                                            WHEN LOB_LOB = 'GL' THEN 'GL CUEP'
                                            WHEN LOB_LOB = 'Tails' THEN 'TL  CUEP'
                                           WHEN LOB_LOB =  'Mini-Tails' THEN 'MTL CUEP'
                                            WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN 'PTL  CUEP'
                                           WHEN LOB_LOB =  'Adjustment' THEN ''
                                           
                                       END
                             
FROM  dbo.LOB
WHERE lobtob = 'Direct Written'
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34083911
Either as above or multiply the LOBtotal by -1.

CASE statement is not needed, just  (LOBtotal*-1) AS LOBtotal
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34084084
This would also do:

,-LOBtotal
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

718 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