|
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 (MIS) |
10 Feb 10 6:46 |
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: CODESELECT 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. |
|
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 (MIS) |
24 Feb 10 8:25 |
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. |
|
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 (MIS) |
24 Feb 10 14:03 |
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. CODESQL> 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. |
|
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. |
|
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.
|
|
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 (MIS) |
24 Feb 10 18:42 |
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. |
|
Amazing! Efficient! Superb! |
|
|
 |
Talk To Other Members
Read More Threads Like This One