Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql where clause

Posted on 2012-04-09
10
Medium Priority
?
385 Views
Last Modified: 2012-04-13
Hi Gents,

I have a very simple question to which unfortunately I cannot see the answer

I have the following querry:

  SELECT DISTINCT a.[PROGRAM_NAME]
  FROM [GF_MAINT].[dbo].LOGIN_PANELVIEW AS c
  inner join [GF_MAINT].[dbo].[PLC_PROGRAM] as a
  on a.[SITE] = c.[SITE]
  WHERE c.[USER] = '{USER_ID}'
  Group By a.[PROGRAM_NAME]

My issue is that I don't want any where clause at all if the '{USER_ID}' is 'administrator'.If the '{USER_ID}' is anything different than the "administrator' I must have the where clause in the above query otherwise I need no where clause at all.

Thanks
0
Comment
Question by:Latzi_Marian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 37825974
use:

WHERE (c.[USER] = '{USER_ID}' or '{USER_ID}'='administrator')

which gives the same behaviour that you want...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37825983
Something like this perhaps:
SELECT  -- DISTINCT			Pointless, as there is a GROUP BY clause
        a.[PROGRAM_NAME]
FROM    [GF_MAINT].[dbo].LOGIN_PANELVIEW AS c
        INNER JOIN [GF_MAINT].[dbo].[PLC_PROGRAM] AS a ON a.[SITE] = c.[SITE]
WHERE   c.[USER] = @User
        OR @User = 'administrator'
GROUP BY a.[PROGRAM_NAME]

Open in new window

0
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 37825987
Just noting down the three options that come to my mind:-

1) Use dynamic SQL
2) Use a plain IF .. ELSE .. condition
3) Use a dummy equality condition  in where clause when user is  'administrator' ; The last one needs an explanation, believe the following modification on your query is enough for it.

  SELECT DISTINCT a.[PROGRAM_NAME]
  FROM [GF_MAINT].[dbo].LOGIN_PANELVIEW AS c
  inner join [GF_MAINT].[dbo].[PLC_PROGRAM] as a
  on a.[SITE] = c.[SITE]
  WHERE c.[USER] = CASE WHEN '{USER_ID}' =  'administrator' THEN  c.[USER] ELSE  '{USER_ID}' END
Group By a.[PROGRAM_NAME]

Hope this helps
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Latzi_Marian
ID: 37825996
why didn't I think about this? It works perfectly just tested it.

Thank you for the prompt answer
0
 

Author Comment

by:Latzi_Marian
ID: 37826014
I've requested that this question be closed as follows:

Accepted answer: 0 points for Latzi_Marian's comment #37825996

for the following reason:

Extremely quick and accurate answer
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37826015
Extremely quick and accurate answer
I am reopening the question so the author can award points appropriately.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 37826064
Extremely quick and accurate answer

is this for my post :)
0
 

Author Comment

by:Latzi_Marian
ID: 37826098
Yes HainKurt. It was for your post. Unfortunately in my haste I forgot to award u the points.

Sorry for the mistake
0
 

Author Comment

by:Latzi_Marian
ID: 37845454
Hi thermoduric

This is the answer ID which needs to be accepted.ID: 37825974 The only issue here is probably the fact that I accepted an answer which was actually a question from the person who gave me that answer.

If you follow the thread it is I think obvious enough what he answer is ...

In any case I made a mistake if we want to stick to the rules then my appologies and please accept answer ID: 37825974.

Thanks
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 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