case or order by? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

case or order by?

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-10 : 11:13:15

I have these records in a simplified table:

icn userid approvallevel
1 user 0
1 user 1
2 user 1
2 user 2
3 user 2
3 user 1
3 user 0
4 user 0
4 user 2
5 user 0

I do this: select approvallevel from table where
icn = '2' and userid = 'user'. I get 1 and 2. Or where icn = '3', I get 1 and 2; icn = '1', I get 0 and 1.

A user could have multiple approval levels which the table shows. What I want a query to do is give me 1 if the multiple-approval user has 1 in his approval levels, or 2 if he has 2 but not 1. You see 1 means primary approval, 2 means secondary and 0 means no approval level. I just want the highest approval level if a user has multiple approval levels.

To explain this further: if icn = '1' I should get 1, icn = '2' I should get 1, if icn = '3' I should get 1, if icn = '4' I should get 2.

I thought about doing a case or order by to get what I want, but I'm not even sure what my logic would be.

Pls help.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-10 : 11:40:04

Just in case, it's not clear: the primary approver has the highest authority.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 11:42:33

so you want the lowest value in the Approval Level column per user that is not a zero?

this will give you the MIN of the approval level per user, unless they have only a 0 in which case a 99 will be returned:

select icn, MIN(CASE ApprovalLevel WHEN 0 THEN 99 ELSE ApprovalLevel END) as ApprovalLevel
from yourtable
group by icn

from the above, you can query from THAT to get the value you wish:

select icn, CASE when ApprovalLevel = 99 THEN 0 ELSE ApprovalLevel END as ApprovalLevel
from
(above SQL) A

Something like that should work for you. If you make "no approval" a high value instead of a 0, this would be easier because you wouldn't need to swap back and forth between 99 and 0 if that makes any sense to you.

- Jeff

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 11:46:22

Jeff, that doesn't follow the logic specified here:

"if icn = '1' I should get 1, icn = '2' I should get 1, if icn = '3' I should get 1, if icn = '4' I should get 2. "

It's a bit confusing... I'm thinking something like:

SELECT icn,
CASE SUM(CASE approvallevel WHEN 0 THEN 0 ELSE 1 END)
WHEN 1 THEN MAX(approvallevel)
WHEN 2 THEN MAX(approvallevel) - 1
ELSE 0
END
FROM YourTable
GROUP BY ICN

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-10 : 11:47:13

This would be more intuitive if the approvallevel was numerically ordered. But because 1 means primary approval, 2 means secondary and 0 means no approval level - it is more complicated to perform.

One option would be to create an approvallevel table which defines the level numerically and could be used to sort
something like:

create table approvallevel ( level int, precedence int, description varchar(30))
insert into approvallevel
select 0,0,'No Approval' union all
select 2,1,'Secondary Approval' union all
select 1,2,'Primary Approval'

--create some sample data
create table t (icn int, userid char(4), approvallevel int)
insert into t
select 1, 'user', 0 union all
select 1, 'user', 1 union all
select 2, 'user', 1 union all
select 2, 'user', 2 union all
select 3, 'user', 2 union all
select 3, 'user', 1 union all
select 3, 'user', 0 union all
select 4, 'user', 0 union all
select 4, 'user', 2 union all
select 5, 'user', 0

select top 1 approvallevel
from t t
join approvallevel al on al.level = t.approvallevel
where icn = 3 and userid = 'user'
order by precedence desc

drop table t, approvallevel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 11:47:38

(yes -- it was wrong. ... see my revised post .. still not sure if that is right, though!)

- Jeff

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-10 : 11:47:50

Thanks for the reply, Jeff. THis query however will give me approvallevel 2 in the case of icn = '3'. Since for this icn and user, user has multiple approval levels (2,1,0) I only want 1 (primary) and not 2 (secondary).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 12:04:40

check out my revised solution (same post, it was edited) and also the other replies, they may work for you.

- Jeff

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-10 : 13:08:05

[code]declare @t table(icn int,userid varchar(10),approvallevel int)
insert @t

select 1, 'user', 0 union
select 1 ,'user', 1 union
select 2, 'user', 1 union
select 2, 'user', 2 union
select 3, 'user', 2 union
select 3 ,'user' ,1 union
select 3 ,'user', 0 union
select 4, 'user', 0 union
select 4 ,'user', 2 union
select 5, 'user', 0

select icn,userid
,coalesce(max(case when approvallevel=1 then 'level1' else null end)
,max(case when approvallevel=2 then 'level2' else null end)
,max(case when approvallevel=0 then 'level0' else null end)) ApprovalLevel
from @t t
group by icn,userid
-----------------------

select l.icn ,l.userid,coalesce(l1.a,l2.a,l0.a) ApprovalLevel
from
(select distinct icn,userid
from @t
) l
left join
(select icn,userid,'level1' a
from @t
where approvallevel=1
) l1 on l.icn=l1.icn and l.userid=l1.userid
left join
(select icn,userid,'level2' a
from @t
where approvallevel=2
) l2 on l.icn=l2.icn and l.userid=l2.userid
left join
(select icn,userid,'level0' a
from @t
where approvallevel=0
) l0 on l.icn=l0.icn and l.userid=l0.userid
[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 15:57:55

VIG -- the main problem with techniques like those are they are very tightly bound to those 3 possible security levels. what if there should be a level 3 or a level 4 created at some point?

Ehorn has the same approach as mine, but his is even better -- using values in a table to ultimately determine the security level as opposed to a hard-coded CASE statement, which is also inflexible.

- Jeff

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-10 : 16:47:58

2 jsmith8858
I unless argue?

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-10 : 16:53:16

Ok, for now I think VIG's syntax would work.
Here's my stored procedure, but this returns recordcount = empty when called from my asp code. When ran from query analyzer using execute i get a recordset. I can't spot what the problem is?

CREATE PROCEDURE dbo.SelectUserInfo
@txtUserId varchar(12),
@txtPassword varchar(12),
@txtCompanyNumber varchar(10)
AS

DECLARE @approvalLevel int

--if user has multiple approval levels, get the highest level (i.e., 1 is higher than 2 and 2 is higher than 0)
select @approvalLevel = coalesce(max(case when intApprovalLevel=1 then 1 else null end)
,max(case when intApprovalLevel=2 then 2 else null end)
,max(case when intApprovalLevel=0 then 0 else null end))
from dbo.ApprovalGroup where txtinternetnum = @txtCompanyNumber and txtuserid = @txtUserId

SELECT DISTINCT A.txtAccountNum, A.txtName, A.txtPassword, A.bitNewsViewed,
A.lngUniqueId, AG.intViewGroup, A.dteLastUsed,
A.txtUserID, A.txtCompanyNum, A.blnHasProfile, A.txtNewsUpdate,
C.lngProgramOrigin, CN.ProfPath, CN.ProfServer, CN.IncludeL3, CN.AutomateStmts,
@approvalLevel as intApprovalLevel, CN.blnWorkflow
FROM dbo.Account A LEFT OUTER JOIN dbo.ApprovalGroup AG ON A.txtUserID = AG.txtUserID AND A.txtInternetCompNum = AG.txtInternetNum
INNER JOIN ClientInfo C on C.txtInternetCompNum = A.txtInternetCompNum
INNER JOIN CompNames CN on CN.InternetNumber = C.txtInternetCompNum
WHERE (A.txtUserID = @txtUserId) AND (A.txtPassword = @txtPassword) AND
(A.txtInternetCompNum =@txtCompanyNumber)
ORDER BY A.lngUniqueId DESC
GO

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-10 : 17:00:53

If I comment out the first select I get a recordset/recordcount. what's wrong with the first select?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-10 : 21:28:36

using VIG's DDL and DML

select icn,case when [low]=1 then 1
when [low]=0 and total=2 then high
when [low]=0 and total>2 then (select top 1 approvallevel from
@t where icn<>0 order by icn asc)
else high end as [approval level]
from
(select icn, min(approvallevel) as [low], max(approvallevel) as [high],count(*) as Total
from @t
group by icn) t2

--------------------
keeping it simple...

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-12 : 11:15:21

Thanks, Jen.

This query returns 0 for icn = 3, which should be 1. User has approval levels 0,1,2 and 1 being the highest should be returned.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-11-12 : 11:22:43

Using VIG's coalesce syntax, I get an empty recordcount when executed from my asp page. It returns a recordset when executed in query analyzer, HOWEVER, I get a "Warning: Null value eliminated from aggregate." Maybe this is causing my asp page to be confused? How do I fix this and how do I get the recordset to my asp page?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-12 : 11:24:27

quote:
Originally posted by PGG123

Thanks, Jen.

This query returns 0 for icn = 3, which should be 1. User has approval levels 0,1,2 and 1 being the highest should be returned.

really? i ran the query and it returned 1. weird....

icn approvallevel
1 1
2 1
3 1
4 2
5 0

--------------------
keeping it simple...

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 14:44:19

[code]declare @t table(icn int, userid char(4), approvallevel int)
insert into @t
select 1, 'user', 0 union all
select 1, 'user', 1 union all
select 2, 'user', 1 union all
select 2, 'user', 2 union all
select 3, 'user', 2 union all
select 3, 'user', 1 union all
select 3, 'user', 0 union all
select 4, 'user', 0 union all
select 4, 'user', 2 union all
select 5, 'user', 0

select icn, min(approvalLevel) as approvalLevel
from
(
select icn, max(approvalLevel) as approvalLevel
from @t
where userid= 'user'
group by icn
union
select icn, approvalLevel
from @t
where userid= 'user' and approvalLevel = 1
) approvalLevel
group by icn

icn approvalLevel
----------- -------------
1 1
2 1
3 1
4 2
5 0[/code]

ehorn's query also gives the correct results (one at a time)
or
[code]select icn, approvallevel
from t t inner join
(
Select level
from approvallevel l
where l.level =t.approvallevel
group by precedence
having precedence=max(precedence)
) t2 on t.approvallevel= t2.level
Where userid = 'user'[/code]

for all icn of a user

and i tested jen's solution and it also worked
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 15:27:25

which of the solutions will be preferable?? Why?? ethorn's??

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources