asked on

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

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

Anoo S Pillai

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

Latzi_Marian

ASKER

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

Thank you for the prompt answer

Latzi_Marian

ASKER

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

Anthony Perkins

Extremely quick and accurate answer
I am reopening the question so the author can award points appropriately.

HainKurt

Extremely quick and accurate answer

is this for my post :)

Latzi_Marian

ASKER

Yes HainKurt. It was for your post. Unfortunately in my haste I forgot to award u the points.

Sorry for the mistake

Latzi_Marian

ASKER

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