CASE function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

CASE function

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-27 : 10:04:15

The following SQL extracts two columns, LXLTYP and LXTYP

If you run this and look at locations 8400 and 8601, the values are “W” and “AS”

What we want is when LXLTYP=’W’ and SUBSTRING(LXTYP,2,1)=’S’ then LXLTYP should be “S”

Note that the “S” in LXTYP is in the second byte

I should be able to do this with another CASE statement (how do i wriet another case statement?)

The test will be the count of records by LXLTYP and LXTYP before and after the change.

------

SELECT SUBSTR(CHAR(NOW()-1 DAY),1,4) || SUBSTR(CHAR(NOW()-1 DAY),6,2) || SUBSTR(CHAR(NOW()-1 DAY),9,2) AS CTLDATE,
MST.LCLCN, MST.LCNAM, MST.LCALP, MST.LCADR1, MST.LCADR2, MST.LCADR3, MST.LCADR4, MST.LCSTA, MST.LCLTC, MST.LCOTC,
MST.LCMGR, MST.LCRGN, REG.RGDSC, MST.LCDST, DST.RGDSC AS RGDSC1, MST.LCPHC1, MST.LCACD1, MST.LCEXG1, MST.LCPHN1,
MST.LCEXT1, MST.LCPHD1, MST.LCPHC2, MST.LCACD2, MST.LCEXG2, MST.LCPHN2, MST.LCEXT2, MST.LCPHD2, MST.LCCNT,
MST.LCCMT, MST.LCGLD, MST.LCCHGM, MST.LCCHGD, MST.LCCHGY, MST.LCOPNM, MST.LCOPND, MST.LCOPNY, MST.LCRTA,
MST.LCSHA, MST.LCDMGL, MST.LCPKT, MST.LCDCHG, EXT.LXSMN, EXT.LXCMN, EXT.LXTYP, EXT.LXACTF, EXT.LXVND,
EXT.LXCCST, EXT.LXCSR, EXT.LXCSRX, EXT.LXLTYP, EXT.LXDCC, EXT.LXDCC2, EXT.LXRCC, EXT.LXRCC2, EXT.LXPCC,
EXT.LXMLCD, EXT.LXCORS, EXT.LXDM, EXT.LXMFPP, EXT.LXCRPT, EXT.LXPKG, TMP.NWACQC, TMP.NWCOMP, TMP.NWPFFS,
CST.CSEML, DM.CSNAM AS DMNAME, EXT.LXRM, RM.CSNAM AS RMNAME,
CASE
WHEN LCLCN IN (3252,3262,3269,4351,4521,4600,7004,7324,7325,7350,7355,7365,7379,7385,7386,7400,8203,8205,8451,8657) THEN '0'
WHEN LXLTYP = 'S' AND SUBSTR(LXTYP,2,1)='I' THEN '0'
ELSE '1'
END AS ALLOWS_RENTAL_STORE_PICKUP,
(TCPCTS + TCPCTL + TCPCTO) AS TAXRATE
FROM AICDTAMAC.AICLCNM MST
LEFT JOIN AICDTAMAC.AICLCNX EXT ON MST.LCLCN =EXT.LXLCN
LEFT JOIN AICDTAMAC.AICLCNW TMP ON MST.LCLCN=TMP.NWLCN
LEFT JOIN AICDTAMAC.AICRGNM REG ON MST.LCRGN=REG.RGRGN
LEFT JOIN AICDTAMAC.AICRGNM DST ON MST.LCDST=DST.RGDST
LEFT JOIN AARDTAMAC.AARCSTM01 CST ON CST.CSCST=MST.LCLCN
LEFT JOIN AARDTAMAC.AARCSTM DM ON DM.CSCST=EXT.LXDM
LEFT JOIN AARDTAMAC.AARCSTM RM ON RM.CSCST=EXT.LXRM
LEFT JOIN ASODTAMAC.ASOTXCM TX ON MST.LCSTA = TX.TCTXCS AND MST.LCLTC = TX.TCTXCL AND MST.LCOTC = TX.TCTXCO
WHERE REG.RGDST = ' ' AND DST.RGDST <> ' '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 05:05:55

put another case statement for it like

CASE WHEN EXT.LXLTYP=’W’ and SUBSTRING(EXT.LXTYP,2,1)=’S’ THEN 'W' ELSE EXT.LXLTYP END

instead of EXT.LXLTYP in select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-28 : 05:47:58

Still using ORACLE, huh?

quote:

SUBSTR(CHAR(NOW()-1 DAY),1,4) || SUBSTR(CHAR(NOW()-1 DAY),6,2) || SUBSTR(CHAR(NOW()-1 DAY),9,2) AS CTLDATE

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources