sql where clause

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
Latzi_MarianSystems IntegratorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
use:

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

which gives the same behaviour that you want...
0
 
Anthony PerkinsCommented:
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
 
Anoo S PillaiCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Latzi_MarianSystems IntegratorAuthor Commented:
why didn't I think about this? It works perfectly just tested it.

Thank you for the prompt answer
0
 
Latzi_MarianSystems IntegratorAuthor Commented:
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
 
Anthony PerkinsCommented:
Extremely quick and accurate answer
I am reopening the question so the author can award points appropriately.
0
 
HainKurtSr. System AnalystCommented:
Extremely quick and accurate answer

is this for my post :)
0
 
Latzi_MarianSystems IntegratorAuthor Commented:
Yes HainKurt. It was for your post. Unfortunately in my haste I forgot to award u the points.

Sorry for the mistake
0
 
Latzi_MarianSystems IntegratorAuthor Commented:
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
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.