Only one expression can be specified in the select list when the subquery is not

Question

Only one expression can be specified in the select list when the subquery is not

mnr123

SSC-Addicted

Points: 427
More actions
April 8, 2020 at 2:15 pm
Go to Answer
#3741441

I am trying to check if a username exists in the table , if the username exits in a table then display data from another table or display a message saying : You do not have permissions , Please send an email:
I tried to write a case statement and getting an error :
SELECT CASE WHEN USERNAME in (SELECT username FROM ITRM_ITBF.[Dflt].[MANUAL_LSV_PERMISSIONS] WHERE PMD ='0')
THEN ( SELECT * FROM ITRM_ITBF.[PMD].[PMD_HR])
ELSE
( select Message from itrm_itbf.temp.security) END
FROM ITRM_ITBF.[Dflt].[MANUAL_LSV_PERMISSIONS] WHERE username = SUBSTRING(current_user, CHARINDEX('\', current_user) + 1, LEN(current_user))
Error :Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I am not sure how to proceed with this :
Any help or inputs is appreciated .
Phil Parkin

SSC Guru

Points: 246962
More actions
April 8, 2020 at 3:41 pm
Answer
#3741473
You're nearly there. Try this structure instead
```
IF EXISTS (SELECT 1 FROM tbl WHERE username = 'whatever')
BEGIN
    --Username found code
    SELECT 1;
END;
ELSE
BEGIN
    --Username not found code
    SELECT 1;
END;
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe this could work too

select 
  coalesce(ph.username, (select Message from itrm_itbf.temp.security))
from
  ITRM_ITBF.[PMD].[PMD_HR] ph
 join
  ITRM_ITBF.[Dflt].[MANUAL_LSV_PERMISSIONS] mlp on ph.username=mlp.username
                                                   and mlp.PMD ='0';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

This would probably be written better as an "IF" statement rather than a CASE... something along the lines of:

IF EXISTS (SELECT 1 FROM [ITRM_ITBF].[Dflt].[MANUAL_LSV_PERMISSIONS] WHERE USERNAME = SUBSTRING(current_user), CHARINDEX('\', current_user) +1, LEN(current_user)) AND USERNAME in (SELECT [USERNAME] FROM [ITRM_ITBF].[Dflt].[MANUAL_LSV_PERMISSIONS] WHERE [PMD] ='0'
     ))
BEGIN
    SELECT *
    FROM [ITRM_ITBF].[PMD].[PMD_HR]
END
ELSE
BEGIN
    SELECT Message
    FROM [ITRM_ITBF].[TEMP].[SECURITY]
END

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

mnr123 SSC-Addicted Points: 427 More actions · Answer 3

thank you it worked , i was struggling with the case statement