Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL View Syntax returns error 156

Posted on 2004-10-12
11
Medium Priority
?
294 Views
Last Modified: 2012-06-21
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




0
Comment
Question by:jlaqualia
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12292194
A create view can not have an IF Clause
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12292219
You can only do what you doing in a SP or Query Analyzer.
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12292224
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12292274
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 2000 total points
ID: 12292321
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12292341
This should do it more simply:


CREATE VIEW dbo.GL00100
AS
SELECT *
FROM dbo.GL00100x
WHERE SYSTEM_USER = 'sa' OR (userdef1 = SYSTEM_USER)
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12292349
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12292478
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12292488
Yeah, sorry BillAn1, didn't see your answer when I posted mine or I wouldn't have :-) .
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12292574
Hey Scott, it happens all the time, crossposting -  it's the nature of the beast.
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12298068
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question