 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » Are IF statemens legal in a VIEW?... |
|
Page 1 of 1 |
|
| Author |
Message |
| Terry Olsen... |
 Posted: Tue Nov 03, 2009 3:31 am |
|
|
|
Guest
|
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 *** |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Tue Nov 03, 2009 6:15 am |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Nov 30, 2009 11:59 am
|
|