If..else.combine 2 selections into 1 during select

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

General SQL Server Forums

New to SQL Server Programming

If..else.combine 2 selections into 1 during select

Author

Topic

pchuen
Starting Member

11 Posts

Posted - 2013-05-20 : 00:21:16

I tried using the if, then else statement to combine 1 particular field where SWADDS in ('LBN', 'SBH') but got an error below. Anyone has any idea where went wrong? Thks so much in advance.
QL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword IF not expected. Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. Cause . . . . . : The keyword IF was not expected here. A syntax error was detected at keyword IF. The partial list of valid tokens is ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
--------------------------------------------------------------------
IF proddta.SWADDS in ('LBN', 'SBH')
BEGIN
SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand,
SWSRP2 AS SubBrand, SWSRP4 AS Range, SUM(SWC9QTY1) AS JanQty,
SUM(SWC9QTY2) AS FebQty
FROM PRODDTA.F55SWF where SWADDS in ('LBN', 'SBH')
GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0
ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC,
(CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
END
ELSE
BEGIN
SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand,
SWSRP2 AS SubBrand, SWSRP4 AS Range, SUM(SWC9QTY1) AS JanQty,
SUM(SWC9QTY2) AS FebQty
FROM PRODDTA.F55SWF where SWADDS NOT IN ('LBN', 'SBH')
GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0
ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC,
(CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
END

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 00:32:01

-- I think the following fix can solve your problem.. If not see the note at the end

IF EXISTs ( SELECT 1 FROM PRODDTA.F55SWF WHERE SWADDS in ('LBN', 'SBH')) THEN
	SELECT SWFYEAR AS FisYear
		,SWAC01 AS Agency
		,SWADDS AS State
		,SWSRP1 AS Brand
		,SWSRP2 AS SubBrand
		,SWSRP4 AS Range
		,SUM(SWC9QTY1) AS JanQty
		,SUM(SWC9QTY2) AS FebQty
	FROM PRODDTA.F55SWF 
	where SWADDS in ('LBN', 'SBH')
	GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0
	ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, 
	(CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
ELSE
	SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand, 
	SWSRP2 AS SubBrand, SWSRP4 AS Range, SUM(SWC9QTY1) AS JanQty, 
	SUM(SWC9QTY2) AS FebQty
	FROM PRODDTA.F55SWF where SWADDS NOT IN ('LBN', 'SBH')
	GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0
	ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, 
	(CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
END IF

NOTE: This is SQL Server Forum... You can post DB2 queries in the following forums
www.dbforums.com/db2
http://www.idug.org/p/fo/et/topic=19

--
Chandu

pchuen
Starting Member

11 Posts

Posted - 2013-05-20 : 01:42:27

Hi Chandra,

thank you so much for your quick response...
I tried using the new script , I still encounter the same issue :(. Did I miss out something?

Processing ended because the highlighted statement did not complete successfully

> IF EXISTS ( SELECT 1 FROM PRODDTA.F55SWF WHERE SWADDS in ('LBN', 'SBH')) THEN SELECT SWFYEAR AS FisYear ,SWAC01 AS Agency ,SWADDS AS State ,SWSRP1 AS Brand ,SWSRP2 AS SubBrand ,SWSRP4 AS Range ,SUM(SWC9QTY1) AS JanQty ,SUM(SWC9QTY2) AS FebQty FROM PRODDTA.F55SWF where SWADDS in ('LBN', 'SBH') GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0 ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, (CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END) ELSE SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand, SWSRP2 AS SubBrand, SWSRP4 AS Range, SUM(SWC9QTY1) AS JanQty, SUM(SWC9QTY2) AS FebQty FROM PRODDTA.F55SWF where SWADDS NOT IN ('LBN', 'SBH') GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0 ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, (CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END) END IF

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword IF not expected. Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. Cause . . . . . : The keyword IF was not expected here. A syntax error was detected at keyword IF. The partial list of valid tokens is ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Processing ended because the highlighted statement did not complete successfully

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:43:55

Did you read the link. I think you're using DB2 and might be better off trying your luck at other forums. This is MS SQL Server forum and solutions given here are guaranteed to work only in SQL Server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 01:56:50

Can you tell complete requirement?
In DB2, you can't return result set directly...
you should work on procedure/function...

--
Chandu

pchuen
Starting Member

11 Posts

Posted - 2013-05-20 : 01:57:29

ok..sorry, will do, thanks again.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 02:05:02

quote:
Originally posted by pchuen

ok..sorry, will do, thanks again.

Welcome

--
Chandu

pchuen
Starting Member

11 Posts

Posted - 2013-05-20 : 03:11:58

Hi Chandu,

I have reposted in DB2 forum. thanks.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 04:32:36

quote:
Originally posted by pchuen

Hi Chandu,
I have reposted in DB2 forum. thanks.

I don't have account there....

--Try this one
	SELECT SWFYEAR AS FisYear
		,SWAC01 AS Agency
		,SWADDS AS State
		,SWSRP1 AS Brand
		,SWSRP2 AS SubBrand
		,SWSRP4 AS Range
		,SUM(CASE WHEN SWADDS in ('LBN', 'SBH') THEN SWC9QTY1 END) AS JanQty1
		,SUM(CASE WHEN SWADDS NOT IN ('LBN', 'SBH') THEN SWC9QTY1 END) AS JanQty2
		,SUM(CASE WHEN SWADDS in ('LBN', 'SBH') THEN SWC9QTY2 END) AS FebQty1
		,SUM(CASE WHEN SWADDS NOT IN ('LBN', 'SBH') THEN SWC9QTY2 END) AS FebQty2
	FROM PRODDTA.F55SWF 
	GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0
	ORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, 
	(CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)

--
Chandu

pchuen
Starting Member

11 Posts

Posted - 2013-05-20 : 05:19:39

Hi Chandu,

the above modified script works perfectly. You are such a talent. Thanks a million for your great help.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 05:42:19

quote:
Originally posted by pchuen

Hi Chandu,

the above modified script works perfectly. You are such a talent. Thanks a million for your great help.

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources