Prev: bcp & DB name with embedded blank
Next: New bie to SQL, confused about SQL collation
From: Terry Olsen on 2 Nov 2009 17:31
I'm trying to create a view where a number will changes based on a
couple of fields. First of all, are IF statements valid in a VIEW? I
keep getting syntax errors near the keyword IF. My attemped code
follows:

CREATE VIEW [dbo].[UPS_GLOBALSHIP_TEST] AS

SELECT
COMPANY,ShipToName,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY
,EMial,EMAIL_FLAG,PHNUMBR1,
SHIPMTHD,VALUESPECIFIED,SUBTOTAL,USERDEF2,SOPNUMBE,
IF ((USERDEF2<2) AND (SHIPMTHD LIKE '%GROUND%'))
BEGIN
IF ((ADDRESS1 LIKE '%P O BOX%') OR (ADDRESS1 LIKE '%PO BOX%') OR
(ADDRESS1 LIKE '%POBOX%') OR
(ADDRESS1 LIKE '%P.O. BOX%') OR (ADDRESS1 LIKE '%P.O.BOX%'))
BEGIN
CASE COMPANY WHEN 0 THEN '000000' WHEN 1 THEN '111111' WHEN 2 THEN
'222222' WHEN 3 THEN '333333' END
END
ELSE
BEGIN
CASE COMPANY WHEN 0 THEN '000000' WHEN 1 THEN '000000' WHEN 2 THEN
'111111' WHEN 3 THEN '333333' END
END
END
ELSE
BEGIN
CASE COMPANY WHEN 0 THEN '000000' WHEN 1 THEN '111111' WHEN 2 THEN
'222222' WHEN 3 THEN '333333' END
END
AS ShprNumbr
FROM UPS_GLOBALSHIP

*** Sent via Developersdex http://www.developersdex.com ***
From: Plamen Ratchev on 3 Nov 2009 00:22
The IF statement is a control of flow statement and cannot be used in view definitions (or SQL expressions in general).
you have to use CASE expressions:

....
CASE WHEN ((USERDEF2<2) AND (SHIPMTHD LIKE '%GROUND%'))
THEN
CASE WHEN ((ADDRESS1 LIKE '%P O BOX%')
OR (ADDRESS1 LIKE '%PO BOX%')
OR (ADDRESS1 LIKE '%POBOX%')
OR (ADDRESS1 LIKE '%P.O. BOX%')
OR (ADDRESS1 LIKE '%P.O.BOX%'))
THEN
CASE COMPANY WHEN 0 THEN '000000'
WHEN 1 THEN '111111'
WHEN 2 THEN '222222'
WHEN 3 THEN '333333'
END
....

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: bcp & DB name with embedded blank
Next: New bie to SQL, confused about SQL collation