SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 If..else.combine 2 selections into 1 during select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/20/2013 :  00:21:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  00:32:01  Show Profile  Reply with Quote
-- 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

Edited by - bandi on 05/20/2013 00:56:08
Go to Top of Page

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/20/2013 :  01:42:27  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/20/2013 :  01:43:55  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  01:56:50  Show Profile  Reply with Quote
Can you tell complete requirement?
In DB2, you can't return result set directly...
you should work on procedure/function...

--
Chandu
Go to Top of Page

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/20/2013 :  01:57:29  Show Profile  Reply with Quote
ok..sorry, will do, thanks again.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  02:05:02  Show Profile  Reply with Quote
quote:
Originally posted by pchuen

ok..sorry, will do, thanks again.


Welcome

--
Chandu
Go to Top of Page

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/20/2013 :  03:11:58  Show Profile  Reply with Quote
Hi Chandu,

I have reposted in DB2 forum. thanks.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  04:32:36  Show Profile  Reply with Quote
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
Go to Top of Page

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/20/2013 :  05:19:39  Show Profile  Reply with Quote
Hi Chandu,

the above modified script works perfectly. You are such a talent. Thanks a million for your great help.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  05:42:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000