Re: One Query

Mtek <mtek_at_mtekusa.com> wa:
>I saw a similar post by someone else but there was no replies.

>I need to write a query that will return columns in the same result
>row. The trick is that these columns must be decoded, and I think
>that the decode statement must have a fallback if no items match. This
>will not work if I want to decode each column with a single value and
>display it in the same row.....

>Like this:

>// DATA IN TABLE
>SQL> select * from test;

>C1 C2 C3
>---------- ---------- ---------
>REC1 50 01-JAN-07
>REC2 50 01-JAN-07
>REC1 100 01-MAY-07
>REC2 10 01-JUN-07

>SELECT TO_CHAR(c3,'MM/DD/YYYY') a, DECODE(c1, 'REC1','Record 1') b,
>SUM(C2), DECODE(c1,'REC2','Record 2') c, SUM(C2)
>FROM test
>GROUP BY c3, c1;

>A B SUM(C2) C SUM(C2)
>---------- -------- ---------- -------- ----------
>01/01/2007 Record 1 50 50
>01/01/2007 50 Record 2 50
>05/01/2007 Record 1 100 100
>06/01/2007 10 Record 2 10

>This is not even close to what I want. What I am looking for is a
>display like this:

>A B SUM(C2) C SUM(C2)
>---------- -------- ---------- -------- ----------
>01/01/2007 Record1 50 Record2 50
>05/01/2007 Record1 100
>06/01/2007 Record2 10

>The key here is that I want to put things in one row and group them
>and sum the proper columns.

>I do not think this can be done without pl/sql.

You can nest decode statements since they resolve to a value.

decode( decode(c1, 'REC1', 'Record 1', c1), 'REC2', 'Record 2', null )

Of course this amounts to hardcoding something that may be dynamic and you probably wouldn't want to do this for more than two unique values for c1. And if you don't know how many unique values you have for c1, then you wouldn't want to do this at all.

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator