Problem with IF statement in a View 1

[aruba]

Can anyone tell me why the following view doesn't work? The SELECT statements work fine on their own, but they won't work when I add the IF statement. Thanks to anyone who can solve my problem!

ALTER VIEW WKCTL.V_P3_BACKLOGS
AS

IF substring(ACT,3,1) in ('A','B','C','D','E','F','G','H','J','K')
SELECT CONVERT(numeric(9), WKCTL.scrub_numbers(SUBSTRING(WKCTL.Act_convertToPP(ACT), 3, 7) + CONVERT(VARCHAR(2),
WKCTL.scrub_numbers(SUBSTRING (ACT, 10, 2))))) AS PARENTWO, SUBSTRING(WKCTL.Act_convertToPP(ACT), 1, 2)
AS P3_BACKLOG
FROM WKCTL.P3ACTBBOP
WHERE (SCOP NOT LIKE '%M%')
ELSE


SELECT convert(numeric(9),WKCTL.SCRUB_NUMBERS( substring(ACT, 3, 6)
+ convert(varchar(2), WKCTL.SCRUB_NUMBERS(substring(ACT, 9, 2))))) PARENTWO,
substring(ACT, 1, 2) P3_BACKLOG
FROM WKCTL.P3ACTBBOP
WHERE SCOP NOT LIKE '%M%'


GO

 

[donutman]

To return different data within a Select list, you should use the Case statement. You can find that in BOL. The If statement cannot be used in your situation, because the conditional part of the If statement has column data from the subsequent Select statement.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[aruba]

Thanks! I would appreciate it if you could get me started on the correct syntax. Thanks again!

 

[JamesLean]

You can't use IF in a view. Try this:

ALTER VIEW WKCTL.V_P3_BACKLOGS
AS

SELECT
	CASE WHEN SUBSTRING(ACT, 3, 1) IN ('A','B','C','D','E','F','G','H','J','K')
		THEN CONVERT(numeric(9), WKCTL.scrub_numbers(SUBSTRING(WKCTL.Act_convertToPP(ACT), 3, 7) + CONVERT(VARCHAR(2), WKCTL.scrub_numbers(SUBSTRING (ACT, 10, 2)))))
		ELSE CONVERT(numeric(9), WKCTL.SCRUB_NUMBERS(SUBSTRING(ACT, 3, 6) + CONVERT(varchar(2), WKCTL.SCRUB_NUMBERS(SUBSTRING(ACT, 9, 2)))))
		END AS PARENTWO,
	CASE WHEN SUBSTRING(ACT, 3, 1) IN ('A','B','C','D','E','F','G','H','J','K')
		THEN SUBSTRING(WKCTL.Act_convertToPP(ACT), 1, 2)
		ELSE SUBSTRING(ACT, 1, 2)
		END AS P3_BACKLOG
FROM WKCTL.P3ACTBBOP
WHERE SCOP NOT LIKE '%M%'
GO

--James

 

[aruba]

I can't thank you enough. It works perfectly!!!

 

[donutman]

A star would go a long way.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]