Can the Subquery be replaced with decode? How? 1

AndersonCJA · Feb 9, 2010

Hi,
I would like to use the decode statement instead of subqueries.

The only difference between each subquery below is the value that is checked in fld3 and fld4.
The query pulls the amount for that value for the keyfield in each query.
The field names have been changed to submit this post, but prior to that the query will run in sql talk and produce the results I want.
How can it be changed to use the decode statement instead of the subqueries? I am using Oracle 10g.
the query returns subquery_a Through F in a line that looks like this.
sub_a Sub_b sub_c sub_d sub_e sub_f
$10.15 $20.00 $3.35 $4.50 $2.00 $40.00

SELECT
(SELECT
LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
FROM
A1 A1, A2 A2, A3 A3, A4 A4
WHERE
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A4.fld3= 'AAA' AND
A4.fld4 = 'AA' AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_A
(SELECT
LTRIM(TO_CHAR( SUM(A3.Amt), '$999,999.99'))
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A4.fld3= 'BBB AND
A4.fld4 = 'BB’ AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_B
(SELECT
LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
FROM
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A4.fld3= 'CCC' AND
A4.fld4 = 'CC' AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_C ,
(SELECT
LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
FROM
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A4.fld3= 'dddd' AND
A4.fld4 = 'dd' AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_d,
(SELECT
LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
FROM
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A4.fld3= 'EEE' AND
A4.fld4 = 'EE' AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_E,
(SELECT
LTRIM(TO_CHAR( SUM(A3.Amt), '$999,999.99'))
(A1.fld1key = A2.fld1key AND
(A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
(A3.fld2=A4.fld2)) AND
A3.fld1key = uniquevalue AND
A2. Date = A3.Date) sub_F
FROM
DUAL

Thank you so much for your help. I just can't figure it out and this query doesn't work in the environment I need it to.

Dagon · Feb 10, 2010

I doubt if your query would work well in any environment. I seems to be a cartesian product of the 6 subqueries. Even assuming each subquery returns a unique row, you will get six rows rather than six columns.

I'm guessing that what you want is something like:

Code:

SELECT LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'AAA' AND
           fld4 = 'AA' THEN AMT END), '$999,999.99')) as A_AMT,
LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'BBB' AND
           fld4 = 'BB' THEN AMT END), '$999,999.99')) as B_AMT,
LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'CCC' AND
           fld4 = 'CC' THEN AMT END),'$999,999.99')) as C_AMT,
LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'DDD' AND
           fld4 = 'DD' THEN AMT END), '$999,999.99')) as D_AMT,
LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'EEE' AND
           fld4 = 'EE' THEN AMT END), '$999,999.99')) as E_AMT,
LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'FFF' AND
           fld4 = 'FF' THEN AMT END), '$999,999.99')) as F_AMT
FROM
(SELECT A3.Amt
 FROM
 A1 A1, A2 A2, A3 A3, A4 A4
WHERE
  (A1.fld1key = A2.fld1key AND
 (A1.fld1key=A3.fld1key) AND
((A3.fld1key=A4.fld1key) AND
 (A3.fld2=A4.fld2)) AND
A3.fld1key =  uniquevalue  AND
 A2. Date = A3.Date)

Retired (not by choice) Oracle contractor.

AndersonCJA · Feb 23, 2010

Dagon, I appreciate your taking the time to help me. I tried the format that you provided and got several rows. I continued to seek help and now have a working a query. The query does not use the decode statement, however, it works the way I need it to. I am sharing the query format in the slim chance that it is helpful to someone.

SELECT
SUBQRY1.SUBQRY1 AS QRY1,
SUBQRY2.SUBQRY2 AS QRY2,
SUBQRY3.SUBQRY3 AS QRY3,
SUBQRY4.SUBQRY4 AS QRY4,
SUBQRY5.SUBQRY5 AS QRY5
FROM
(SELECT
NVL(( SELECT LTRIM(TO_CHAR( SUM(A3.COST), '9,999,999.99')) AS SUBQRY1
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE
( A1.FLD1KEY= A2.FLD1KEY ) AND
(A3.FLD1KEY = A2.FLD1KEY) AND
((A3.FLD1KEY= A4.FLD1KEY ) AND ( A3.FLD2KEY= A4.FLD2KEY) ) AND
A4.FLD4 = 'AAA' AND A4.FLD5 = 'AA' AND
A3.FLD4 = 'Q' AND A3.FLD1KEY =(PARM) AND A2.FLD6 = A3.FLD7 ),0) SUBQRY1,
A6.FLD1KEY FROM A6 A6 WHERE A6.FLD1KEY = (PARM) SUBQRY1,
(SELECT
NVL((SELECT LTRIM(TO_CHAR( SUM(A3.COST), '9,999,999.99')) AS SUBQRY2
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE
( A2.FLD1KEY= A1.FLD1KEY) AND
(A3.FLD1KEY = A2.FLD1KEY) AND ( A1.FLD1KEY= A3.FLD1KEY) AND (A3.FLD1KEY = A4.FLD1KEY ) AND
( A3.FLD2KEY= A4.FLD2KEY) AND A3.FLD3 = 'Q'
AND A4.FLD4 = 'BBB' AND A4.FLD5 = 'BB' AND A3.FLD1KEY =(PARM) AND A2.FLD6 = A3.FLD7),0) SUBQRY2,
A6.FLD1KEY FROM A6 A6 WHERE A6.FLD1KEY = (PARM) SUBQRY2,
(SELECT
NVL((SELECT LTRIM(TO_CHAR(A3.COST, '9,999,999.99')) SUBQRY3
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE ( A2.FLD1KEY= A1.FLD1KEY ) AND ( A1.FLD1KEY= A3.FLD1KEY ) AND
(A3.FLD1KEY = A2.FLD1KEY) and
( ( A3.FLD1KEY= A4.FLD1KEY ) AND ( A3.FLD2KEY= A4.FLD2KEY) ) AND
A4.FLD4 = 'CCC' AND A4.FLD4 = 'CC' AND
A3.FLD4 = 'Q' AND A3.FLD1KEY =(PARM) AND A2.FLD6 = A3.FLD7),0) SUBQRY3,
A6.FLD1KEY FROM A6 A6. WHERE A6.FLD1KEY = (PARM) SUBQRY3,
(SELECT
NVL((SELECT LTRIM(TO_CHAR(A3.COST, '9,999,999.99')) SUBQRY4
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE ( A2.FLD1KEY= A1.FLD1KEY) AND (A1.FLD1KEY= A3.FLD1KEY ) AND
(A3.FLD1KEY = A2.FLD1KEY) and
( ( A3.FLD1KEY= A4.FLD1KEY ) AND ( A3.FLD2KEY= A4.FLD2KEY) ) AND A4.FLD4 = 'DDD'
AND A4.FLD4 = 'DD' AND A3.FLD4 = 'Q' AND A3.FLD1KEY =(PARM) AND
A2.FLD6 = A3.FLD7 ),0) SUBQRY4,
A6.FLD1KEY FROM A6 A6 WHERE A6. FLD1KEY = (PARM) SUBQRY4,
(SELECT
NVL(( SELECT LTRIM(TO_CHAR( SUM(A3.COST), '9,999,999.99')) SUBQRY5
FROM A1 A1, A2 A2, A3 A3, A4 A4
WHERE (A2.FLD1KEY= A1.FLD1KEY ) AND ( A1.FLD1KEY= A3.FLD1KEY) AND
(A3.FLD1KEY = A2.FLD1KEY) and
( A3.FLD1KEY = A4.FLD1KEY ) AND ( A3.FLD2KEY= A4.FLD2KEY) AND
A3.FLD3 = 'Q' AND A3.FLD1KEY = (PARM) AND
A2.FLD6 = A3.FLD7),0) SUBQRY5,
A6.FLD1KEY FROM A6 A6 WHERE A6.FLD1KEY = (PARM) SUBQRY5
WHERE
SUBQRY1.FLD1KEY = SUBQRY2.FLD1KEY and
SUBQRY1.FLD1KEY = SUBQRY3.FLD1KEY and
SUBQRY1.FLD1KEY = SUBQRY4.FLD1KEY and
SUBQRY1.FLD1KEY = SUBQRY5.FLD1KEY

Dagon · Feb 24, 2010

I'm glad you found a solution, but I think the query is awful. Also, as it stands, I don't even see how it works. You are not selecting fld1key in any of the subqueries, so I don't see how you can be using it in the join conditions at the end. Also, logically fld1key must be returning a single row for each of the 5 subqueries, as otherwise you would be getting a cartesian product. Therefore, I can't see the point in joining on it when each subquery is only returning a single row anyway.

If you can provide table definitions and values for some of the rows and parameters, I will be glad to prove that my solution can work.

Retired (not by choice) Oracle contractor.

AndersonCJA · Feb 24, 2010

Before I explain myself, I want to say with the utmost sincerity that your query is great, easy to understand and I learned from it. I have copied it to use later when my need fits the query. If I came across to sound as though I was putting it down in any way, I apologize because that is far from what I am in any position to do. I completly appreciate your help. In answer to your reply, You are correct. My query does indeed return one record per subquery and it is through the PARM that is passed to it, and it is that parm that it is tied together with. Without the parm, and without getting the unique record in each subquery, I would get several records per key.
table A1 = unique record per key
table A2 = unique record per key
table A3 = several records per key
table A4 – several records per key
Thank you - thank you for helping me.

Dagon · Feb 24, 2010

AndersonCJA,

I don't mind being told my solution doesn't work for a particular reason. What annoys me is just being told it doesn't work with no attempt to explain why.

I've mimicked your tables with some test data that fits the query and tested it against your original SQL (although I had to made a lot of corrections in that). Admittedly, I did have to make a couple of corrections to my own, which was written as a suggestion without testing, so it's possible that was what was causing you problems.

I hope what I've done helps you.

Code:

SQL> create table a1 (fld1key number);

Table created.

SQL> create table a2 (fld1key number, thedate date);

Table created.

SQL> create table a3 (fld1key number, thedate date, amt number, fld2 number);

Table created.

SQL> create table a4 (fld1key number, fld3 varchar2(10), fld4 varchar2(10), fld2 number);

Table created.

SQL> insert into a1 values (1);

1 row created.

SQL> insert into a2 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'));

1 row created.

SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 10.15, 10);

1 row created.

SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 20, 20);

1 row created.
 
SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 3.35, 30);

1 row created.
 
SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 4.50, 40);

1 row created.

SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 2, 50);

1 row created.

SQL> insert into a3 values (1, to_date('24-feb-2010', 'DD-MON-YYYY'), 40, 60);

1 row created.

SQL> insert into a4 values (1, 'AAA', 'AA', 10);

1 row created.

SQL> insert into a4 values (1, 'BBB', 'BB', 20);

1 row created.

SQL> insert into a4 values (1, 'CCC', 'CC', 30);

1 row created.
 
SQL> insert into a4 values (1, 'DDD', 'DD', 40);

1 row created.

SQL> insert into a4 values (1, 'EEE', 'EE', 50);

1 row created.

SQL> insert into a4 values (1, 'FFF', 'FF', 60);

1 row created.

SQL> SELECT
  2  (SELECT LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
  3  FROM A1 A1, A2 A2, A3 A3, A4 A4
  4  WHERE A1.fld1key = A2.fld1key AND
  5  	   A1.fld1key=A3.fld1key AND
  6  	   A3.fld1key=A4.fld1key AND
  7  	   A3.fld2=A4.fld2 AND
  8  	   A4.fld3= 'AAA' AND
  9  	   A4.fld4 = 'AA' AND
 10  	   A3.fld1key =  1  AND
 11  	   A2.TheDate = A3.TheDate)   sub_A,
 12  (SELECT LTRIM(TO_CHAR( SUM(A3.Amt), '$999,999.99'))
 13  FROM  A1	A1,  A2 A2,  A3 A3,  A4 A4
 14  WHERE A1.fld1key = A2.fld1key AND
 15  	   A1.fld1key=A3.fld1key AND
 16  	   A3.fld1key=A4.fld1key AND
 17  	   A3.fld2=A4.fld2 AND
 18  	   A4.fld3= 'BBB' AND
 19  	   A4.fld4 = 'BB' AND
 20  	   A3.fld1key =  1  AND
 21  	   A2.TheDate = A3.TheDate)   sub_B,
 22  (SELECT LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
 23  FROM  A1	A1,  A2 A2,  A3 A3,  A4 A4
 24  WHERE A1.fld1key = A2.fld1key AND
 25  	   A1.fld1key=A3.fld1key AND
 26  	   A3.fld1key=A4.fld1key AND
 27  	   A3.fld2=A4.fld2 AND
 28  	   A4.fld3= 'CCC' AND
 29  	   A4.fld4 = 'CC' AND
 30  	   A3.fld1key =  1  AND
 31  	   A2.TheDate = A3.TheDate)   sub_C ,
 32  (SELECT LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
 33  FROM  A1	A1,  A2 A2,  A3 A3,  A4 A4
 34  WHERE A1.fld1key = A2.fld1key AND
 35  	   A1.fld1key=A3.fld1key AND
 36  	   A3.fld1key=A4.fld1key AND
 37  	   A3.fld2=A4.fld2 AND
 38  	   A4.fld3= 'DDD' AND
 39  	   A4.fld4 = 'DD' AND
 40  	   A3.fld1key =  1  AND
 41  	   A2.TheDate = A3.TheDate)   sub_d,
 42  (SELECT LTRIM(TO_CHAR(A3.Amt, '$999,999.99'))
 43  FROM  A1	A1,  A2 A2,  A3 A3,  A4 A4
 44  WHERE A1.fld1key = A2.fld1key AND
 45  	   A1.fld1key=A3.fld1key AND
 46  	   A3.fld1key=A4.fld1key AND
 47  	   A3.fld2=A4.fld2 AND
 48  	   A4.fld3= 'EEE' AND
 49  	   A4.fld4 = 'EE' AND
 50  	   A3.fld1key =  1  AND
 51  	   A2.TheDate = A3.TheDate)   sub_E,
 52  (SELECT LTRIM(TO_CHAR( SUM(A3.Amt), '$999,999.99'))
 53  FROM  A1	A1,  A2 A2,  A3 A3,  A4 A4
 54  WHERE A1.fld1key = A2.fld1key AND
 55  	   A1.fld1key=A3.fld1key AND
 56  	   A3.fld1key=A4.fld1key AND
 57  	   A3.fld2=A4.fld2 AND
 58  	   A4.fld3= 'FFF' AND
 59  	   A4.fld4 = 'FF' AND
 60  	   A3.fld1key =  1  AND
 61  	   A2.TheDate = A3.TheDate)   sub_F
 62  FROM
 63  DUAL
 64  /

SUB_A        SUB_B        SUB_C        SUB_D        SUB_E        SUB_F          
------------ ------------ ------------ ------------ ------------ ------------   
$10.15       $20.00       $3.35        $4.50        $2.00        $40.00         
 
SQL> SELECT LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'AAA' AND
  2  		fld4 = 'AA' THEN AMT END), '$999,999.99')) as A_AMT,
  3  LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'BBB' AND
  4  		fld4 = 'BB' THEN AMT END), '$999,999.99')) as B_AMT,
  5  LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'CCC' AND
  6  		fld4 = 'CC' THEN AMT END),'$999,999.99')) as C_AMT,
  7  LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'DDD' AND
  8  		fld4 = 'DD' THEN AMT END), '$999,999.99')) as D_AMT,
  9  LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'EEE' AND
 10  		fld4 = 'EE' THEN AMT END), '$999,999.99')) as E_AMT,
 11  LTRIM(TO_CHAR(SUM(CASE WHEN fld3= 'FFF' AND
 12  		fld4 = 'FF' THEN AMT END), '$999,999.99')) as F_AMT
 13  FROM
 14  (SELECT A3.Amt,
 15  	     a4.fld3,
 16  	     a4.fld4
 17   FROM
 18   A1 A1, A2 A2, A3 A3, A4 A4
 19  WHERE A1.fld1key = A2.fld1key AND
 20  	   A1.fld1key=A3.fld1key AND
 21  	   A3.fld1key=A4.fld1key AND
 22  	   A3.fld2=A4.fld2 AND
 23  	   A3.fld1key =  1  AND
 24   A2.TheDate = A3.TheDate)
 25  /

A_AMT        B_AMT        C_AMT        D_AMT        E_AMT        F_AMT          
------------ ------------ ------------ ------------ ------------ ------------   
$10.15       $20.00       $3.35        $4.50        $2.00        $40.00

Retired (not by choice) Oracle contractor.

AndersonCJA · Feb 24, 2010

Thank you - one thing though, I'm getting a message, "missing expression" - I am sorry I can not find what is missing. I have checked and rechecked to be sure my parenthesis match up.

AndersonCJA · Feb 24, 2010

OOps that was me misstyping something. terribly sorry. This is what is happening now.
I get a message that says, a4.Fld4 is an invalid identifier.

I removed the portion of code in the case that is
"AND a4.fld4 = 'AA'" for each line and I still get the message.
If I run the lower query seperate,it runs fine.
SELECT A3.Amt, a4.fld3,a4.fld4
FROM
A1 A1, A2 A2, A3 A3, A4 A4
WHERE A1.fld1key = A2.fld1key AND
A1.fld1key=A3.fld1key AND
A3.fld1key=A4.fld1key AND
A3.fld2=A4.fld2 AND
A3.fld1key = 1 AND
A2.TheDate = A3.TheDate
Still plugging away to find my mistakes.

AndersonCJA · Feb 24, 2010

Ok. I see what is happening. The first message was
saying that a4.fld4 was and invalid Identifier, when I removed the A4.FLD4 from the case statement, The message is now a4.FLD3 is an invalid identifier.
CASE WHEN fld3= 'AAA' AND fld4 = 'AA' THEN AMT
Thank you again for all of your help.

Dagon · Feb 24, 2010

That's right. You don't need the qualifiers in the outer select because I haven't given any alias to the subquery.

Retired (not by choice) Oracle contractor.

AndersonCJA · Feb 24, 2010

Amazing! Efficient! Superb!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Can the Subquery be replaced with decode? How? 1

AndersonCJA

Programmer

Dagon

MIS

AndersonCJA

Programmer

Dagon

MIS

AndersonCJA

Programmer

Dagon

MIS

AndersonCJA

Programmer

AndersonCJA

Programmer

AndersonCJA

Programmer

Dagon

MIS

AndersonCJA

Programmer

Similar threads

Part and Inventory Search

Sponsor