March 3, 2010 at 10:53 pm
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..
March 3, 2010 at 11:06 pm
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 3, 2010 at 11:11 pm
Can Also Use Top .
March 3, 2010 at 11:52 pm
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 4, 2010 at 12:15 am
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..
March 4, 2010 at 1:47 am
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 .
March 4, 2010 at 2:27 am
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
March 4, 2010 at 4:06 am
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..
March 4, 2010 at 4:55 am
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...
March 4, 2010 at 9:40 am
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)
March 4, 2010 at 9:54 am
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...
March 4, 2010 at 10:03 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply