SQL View Syntax returns error 156

Trying to create a view, when i click Check Syntax in Enterprise Manager, error 156 is returned.  The SQL statement is:

CREATE VIEW dbo.GL00100
AS
IF SYSTEM_USER = 'sa'
      BEGIN
      SELECT * FROM dbo.GL00100x
      END
ELSE
      BEGIN
      SELECT  * FROM dbo.GL00100x WHERE (USERDEF1 = SYSTEM_USER)
      END




jlaqualiaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BillAn1Connect With a Mentor Commented:
a less contorted method (and a bit quicker) :

CREATE VIEW dbo.GL00100
AS

select * from
dbo.GL00100x
where SYSTEM_USER = 'sa'
or USERDEF1 = SYSTEM_USER
0
 
pra_kumar03Commented:
A create view can not have an IF Clause
0
 
apirniaCommented:
You can only do what you doing in a SP or Query Analyzer.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
pra_kumar03Commented:
try this instead

CREATE VIEW dbo.GL00100
AS

select * from
dbo.GL00100x
where USERDEF1  like (  case
                  when SYSTEM_USER = 'sa' then '%'
                  else char(39)+SYSTEM_USER+char(39)
end )
0
 
pra_kumar03Commented:
sorry it must have not gone thru
try this

CREATE VIEW dbo.GL00100
AS

select * from
dbo.GL00100x
where USERDEF1  like (  case
               when SYSTEM_USER = 'sa' then '%'
               else ''
end )
or USERDEF1 = SYSTEM_USER
0
 
Scott PletcherSenior DBACommented:
This should do it more simply:


CREATE VIEW dbo.GL00100
AS
SELECT *
FROM dbo.GL00100x
WHERE SYSTEM_USER = 'sa' OR (userdef1 = SYSTEM_USER)
0
 
pra_kumar03Commented:
BillAn1
your sql would not return the whole table when the system user is sa. I think what jlagulia is trying to do is to get the whole table is sa is the user else only one user.
0
 
BillAn1Commented:
yes it will - as with ScottPletcher's similar answer.

where SYSTEM_USER = 'sa'
or USERDEF1 = SYSTEM_USER
if the first part of the the condition is true , i.e. SUSTEM_USER = 'sa' then you will get all rows.
otherwise, you rely on the second part of the condition, USERDEF1 = SYSTEM_USER to be true, i.e. you only get rows for that particular user if the user is not sa.
0
 
Scott PletcherSenior DBACommented:
Yeah, sorry BillAn1, didn't see your answer when I posted mine or I wouldn't have :-) .
0
 
BillAn1Commented:
Hey Scott, it happens all the time, crossposting -  it's the nature of the beast.
0
 
pra_kumar03Commented:
Hi scott and BillAn1
I did see your query again and I realize that is correct... sorry for overlooking.. this surely wud be a faster and better query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.