Solved

# SQL Case Statement

Posted on 2010-11-08
Medium Priority
365 Views
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
Question by:mburk1968

LVL 8

Expert Comment

ID: 34083854
0 - LOBtotal

If LOBtotal = 5
0 - 5 = -5

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

Author Comment

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

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

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

ID: 34084084
This would also do:

,-LOBtotal
0

## Featured Post

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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 .
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll