Subquery in case statement

  • i have a problem with the sql query using case statement. the problem is returning multiple rows from the subquery.

    select AppId,UserPermission= case when CHARINDEX('%',permissionval)<>0

    then SUBSTRING(permissionval,1,CHARINDEX('%',permissionval)-1)

    case when CHARINDEX('%',permissionval)=1

    then (select adminpermission from appadmin where addId=1)

    else permissionval

    end

    from userpermissions;

    i have identified the problem i.e. returinig multiple rows with the subquery

    ex: select adminpermission from appadmin where addId=1

    This query will have 2 or more rows

    can any one guide me

    Thanks

    Rock..

  • Run this part:

    select adminpermission from appadmin where addId=1

    If the returned values are different, then you will have to decide which to use, using MIN()/MAX() or ROW_NUMBER() with OVER() or GROUP BY. If they are the same, then you could use DISTINCT or GROUP BY with MIN() or MAX() to return only one row.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Can Also Use Top .

  • vbprogrammer1986 (3/3/2010)


    Can Also Use Top .

    TOP1, preferably with ORDER BY regardless of whether or not the returned values are the same. Just good practice.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • using the below query

    select adminpermission from appadmin where addId=1

    i will get two or more unique values and i need to use all these values.

    how can i handle now?

    Thanks

    Rock..

  • select adminpermission from appadmin where addId=1

    you can add coalesce keyword and take values comma delimited by using a function and can use then .

  • If the posted query is just a little part of a big query, I request you to post the entire query with some explanation about the requirement.

    I see a logical problem in your query. This part of the query (select adminpermission from appadmin where addId=1) will never be executed for any value.

    See this in this example.

    select( case

    when CHARINDEX('%',permissionval)<>0 then SUBSTRING(permissionval,1,CHARINDEX('%',permissionval)-1)

    else ( case when CHARINDEX('%',permissionval)=1 then (select 'a' union select 'b' ) else permissionval end )

    end )

    from (

    select'person' as permissionval

    union all

    select'' as permissionval

    union all

    select'per%son' as permissionval

    union all

    select'%person' as permissionval

    ) a

    --Ramesh


  • ramesh thanks for the reply

    select ( case

    when CHARINDEX('%',permissionval)<>0 then SUBSTRING(permissionval,1,CHARINDEX('%',permissionval)-1)

    else ( case when CHARINDEX('%',permissionval)=1 then (select 'a' union select 'b' ) else permissionval end )

    end )

    from (

    select 'person' as permissionval

    union all

    select '' as permissionval

    union all

    select 'per%son' as permissionval

    union all

    select '%person' as permissionval

    ) a

    i can not keep individual queries like that.because there may be a chance of having many values. values will come based on user permissions. any other idea?

    Thanks

    Rock..

  • i tried with CTE. it works. but, is it a good idea to keep this in a sp which is used by a Big application. because the below query(CTE) will hit database three times to retrieve data. and any performance issues by using CTE. Why can't we get with Case statements?

    ex:

    with test(userper) as

    (

    select SUBSTRING(permissionval,1,CHARINDEX('%',permissionval)-2) from userpermissions

    where CHARINDEX('%',permissionval)not in (0,1) ;

    union

    select distinct b.adminpermission from userpermissions as a inner join from appadmin as b on (a.userid=b.userid)

    where b.appId=1 and CHARINDEX('%',a.permissionval)=1 ;

    union

    select permissionval from userpermissions where CHARINDEX('%',permissionval)=0;

    )

    select * from test;

    Thanks

    Rock...

  • I'm not sure if this would give you the same result since there are no data available to test again...

    SELECT

    CASE

    WHEN b.userid IS NOT NULL AND b.appId=1 AND CHARINDEX('%',a.permissionval)=1

    THEN b.adminpermission

    WHEN b.userid IS NULL AND CHARINDEX('%',a.permissionval)NOT IN (0,1)

    THEN SUBSTRING(permissionval,1,CHARINDEX('%',permissionval)-2)

    WHEN b.userid IS NULL AND CHARINDEX('%',permissionval)=0

    THEN permissionval

    ELSE 'exception'

    END

    FROM userpermissions AS a

    LEFT OUTER appadmin AS b ON (a.userid = b.userid)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • hello lmu92,

    i have wasted lot of time and struggled for this logic. finally i got the solution which was same as you said. but i didn't mention like "userid is not null and userid is null in the case statements". my code works.

    Thank you for the replying

    Regards

    Rock...

  • rockingadmin (3/4/2010)


    hello lmu92,

    i have wasted lot of time and struggled for this logic. finally i got the solution which was same as you said. but i didn't mention like "userid is not null and userid is null in the case statements". my code works.

    Thank you for the replying

    Regards

    Rock...

    Yes, you're right, it's not required to check for b.userid. I just "rephrased" the three statements without completely resolving the logic.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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