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

  • mnr123

    SSC-Addicted

    Points: 423

     

    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: 244449

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • scdecade

    SSC Eights!

    Points: 807

    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';
  • Mr. Brian Gale

    SSC-Insane

    Points: 22790

    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

     

  • mnr123

    SSC-Addicted

    Points: 423

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

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

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