SQL & PL/SQL

I'm trying to convert a Stored proc into a simple SQL script.

I am currently working on a horrendous SQL statement that has nested queries within nested queries as part of the from clause. I hope to rewrite this as a sinlge view or a couple views.

This section is from one of the nested subqueries. Can anyone tell me what effect Max() would have on a decode that only has 1 possible value? (or NULL)

SELECT DISTINCT mandtj, job_no, job_name,
MAX(decode(partner,'||'''ZB'''||',ZS007.nachn || '' '' || ZS007.vorna)) lockup_holder,
MAX(decode(partner,'||'''ZA'''||',ZS007.nachn || '' '' || ZS007.vorna)) eng_partner,
MAX(decode(partner,'||'''ZC'''||',ZS007.nachn || '' '' || ZS007.vorna)) lead_partner
FROM BLA BLA BLA...

I've converted the above 3 decodes to use 3 case statements (see below), but i'm still wondering what effect the max() would have.

SELECT
b.mandt mandtj,
b.vbeln job_no,
b.ktext job_name,
(case when n.partner = 'ZB' then (a.nachn || ' ' || a.vorna) else NULL end) as lockup_holder,
(case when n.partner = 'ZA' then (a.nachn || ' ' || a.vorna) else NULL end) as eng_partner,
(case when n.partner = 'ZC' then (a.nachn || ' ' || a.vorna) else NULL end) as lead_partner,
FROM BLA BLA BLA...

I've been staring at this for quite some time wondering if the orignal developer was doing something really clever... Maybe this was an "old skool" way of doing something clever.. (But i'm pretty sure they're not.. ). Is the fact that it's part of a subquery significant?

As the case/decode only return a single value per field/line, I can't see what effect the max would have.

Any suggestions? Any thoughts or ideas would be greatly appreciated.

Cheers

Pete

Edited by: peetmoore on 28-May-2009 17:05