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

  •  

    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 .

     

     

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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.

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

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

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