Quick SQL Question 2

[cojiro]

I'm writing a query that consists of an expense approval sumission table, an approver table, and an associative table connecting the two. Some submissions have two approvers, and some have only one. The way my query works right now, I get two duplicate rows, except for the approver field, when there are two aprovers:

SubmissionID SubmitDate Approver
------------ ---------- --------
999999999999 09092002 Bill
999999999999 09092002 Bob

I would like this data to just come out like this:

SubmissionID SubmitDate Approver 1 Approver 2
------------ ---------- ---------- ----------
999999999999 09092002 Bill Bob

Right now I just have a standard select/from/where structure. I'm guessing I might have to change that up. Does anyone know what I'm going to have to do?

 

[carp]

There's probably a more efficient way to do this, but this was the first thing that came to mind:

select a1.submissionid, a1.submitdate, a1.approver approver1, a2.approver approver2
from approvals a1, approvals a2
where a1.submissionid = a2.submissionid(+)
and a1.approver <> a2.approver(+)
and a1.rowid = (select min(rowid) from approvals where submissionid = a1.submissionid)
order by submitdate;

Unfortunately, with a self join, an outer join, and a correlated subquery, this is liable to take a while if your table is large. Also, if you ever have more than two approvers, you'll also be back to getting multiple rows per submissionid.

But it's a start.

 

[BJCooperIT]

In the past I had a similar need. What I did was write a function that contained a cursor that read all the rows (your approvers) for the key and concatenated them into a string that was returned by the function. Your SQL can look something like:

select submissionid, submitdate,
       F_GET_APPROVERS(submissionid) approvers
from   approvals
order  by submitdate;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[cojiro]

Thank you both for the suggestions. I'll try them both and see which one will work better.

 

[sem]

Try this one:

select
SubmissionID
, SubmitDate
, max(Approver) Approver1
, decode(min(Approver) , max(Approver),null, max(Approver)) Approver2
from grp_t
group by SubmissionID, SubmitDate

Regards, Dima

 

[carp]

Oh, I REALLY like that!

 

[cojiro]

Thanks for the input sem. I tried it out and it works only I had to change the decode line to:
decode(min(approver) , max(approver),null, min(approver)) Approver2

I have one issue with this though. I have no idea what this decode function does. Do you think you could explain the function and its parameters to me? It would be a huge help.
Thanks!

 

[carp]

DECODE is something like an inline IF..THEN..ELSE statement.
So let's look at the elegant code SEM gave you:
select
SubmissionID
, SubmitDate
, max(Approver) Approver1
, decode(min(Approver) , -- LOOK AT THE VALUE min(Approver)
max(Approver), -- IF IT EQUALS max(APPROVER) THEN
null, -- RETURN NULL
max(Approver) -- ELSE RETURN max(Approver)
) Approver2
from grp_t
group by SubmissionID, SubmitDate

Note that you can have any number of comparisons:

DECODE(userid, -- LOOK AT THE VALUE OF userid
1, 'SYS', -- IF IT IS 1, THEN RETURN 'SYS'
2, 'SYSTEM', -- IF IT IS 2, THEN RETURN 'SYSTEM'
27, 'carp', -- IF IT IS 27, THEN RETURN 'carp'
'Who Cares') -- ELSE RETURN 'Who Cares'

 

[sem]

Unfortunately, my query fails if 1 person approved expences twice and this is important :-(

Regards, Dima

 

[cojiro]

Thanks for the explination Carp.
So it's like a case statement in a function.
That's really useful.

Sem, what your saying is that if the same person approves an expense twice, then the name will only show under approver 1 and approver 2 will show null right?
If this is the case then, it works fine for my purposes.