Solved

sql where clause

Posted on 2012-04-09
10
379 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
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 51

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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