Home » SQL & PL/SQL » SQL & PL/SQL » Cobines two queries and using one uniform column (Sql,9.2.0.8.0,XP)
| Cobines two queries and using one uniform column [message #430731] |
Thu, 12 November 2009 01:27  |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Dear All
I have to combine my two queries, the tables structure are:
desc ro_mst (Master table)
RO_CUST_NM VARCHAR2(60)
RO_CUST_ADD VARCHAR2(100)
RO_CUST_PHN VARCHAR2(15)
RO_NO NOT NULL VARCHAR2(5)
desc ro_con_dtl (Detail Table)
RO_CON_CD VARCHAR2(5)
RO_NO VARCHAR2(5)
APPR_AMT NUMBER(5)
desc ro_lub_dtl (Detail Table)
RO_LUB_CD VARCHAR2(5)
RO_NO VARCHAR2(5)
APPR_AMT NUMBER(5)
My queries and their output are:
First Query
Select
nvl(sum(a.appr_amt),0) Con_Amt,
TO_CHAR(b.ro_clo_dt,'MON,RRRR')dt1,
to_char(b.ro_clo_dt,'MM')
from ro_con_dtl a,ro_mst b
where a.ro_no=b.ro_no
and b.ro_clo_dt between '01JAN09' AND '31OCT09'
group by TO_CHAR(b.ro_clo_dt,'MON,RRRR'),
TO_CHAR(b.ro_clo_dt,'MM')
ORDER BY 3
Result is
CON_AMT DT1 TO
--------- -------- --
5010 JAN,2009 01
6345 FEB,2009 02
3225 MAR,2009 03
7306 APR,2009 04
9375 MAY,2009 05
7086 JUN,2009 06
9983 JUL,2009 07
7818 AUG,2009 08
2350 SEP,2009 09
3528 OCT,2009 10
Second Query
Select nvl(sum(C.APPR_AMT),0) Lub_AMT,
TO_CHAR(D.ro_clo_dt,'MON,RRRR'),
TO_CHAR(D.ro_clo_dt,'MM')
FROM RO_LUB_DTL C,RO_MST D
WHERE D.ro_clo_dt between '01JAN09' AND '31OCT09'
AND C.RO_NO=D.RO_NO
GROUP BY TO_CHAR(D.ro_clo_dt,'MON,RRRR'),
TO_CHAR(D.ro_clo_dt,'MM')
order by 3
Result is.
LUb_AMT TO_CHAR( TO
--------- -------- --
183411 JAN,2009 01
91800 FEB,2009 02
105583 MAR,2009 03
106685 APR,2009 04
143162 MAY,2009 05
128213 JUN,2009 06
109040 JUL,2009 07
195486 AUG,2009 08
146605 SEP,2009 09
174534 OCT,2009 10
Please advice me how can i combine these queries so that my result is like this:
Date Con_Amt Lub_Amt
--------- -------- --
JAN,2009 5010 183411
FEB,2009 6345 91800
MAR,2009 3225 105583
APR,2009 7306 106685
MAY,2009 9375 143162
JUN,2009 7086 128213
JUL,2009 9983 109040
AUG,2009 7818 195486
SEP,2009 2350 146605
OCT,2009 3528 174534
Total 62026 1384519
Please help me to achieve the above output.
Regards
Zuhair
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430761 is a reply to message #430731] |
Thu, 12 November 2009 04:41  |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Dear Michel Cadot
First of All, Thanks for the suggestion regardng date issue, at the present when i am trying to merge these query by using ro column joing then the result is not not correct.
My Query is (after joing three tables)
Select nvl(sum(a.appr_amt),0) Con_Amt,
nvl(sum(c.appr_amt),0) Lub_Amt,
TO_CHAR(b.ro_clo_dt,'MON,RRRR')dt1,
to_char(b.ro_clo_dt,'MM')
from ro_con_dtl a,ro_mst b,ro_lub_dtl c
where a.ro_no=b.ro_no
and a.ro_no=c.ro_no
and b.ro_clo_dt between '01JAN2009' AND '31OCT2009'
group by TO_CHAR(b.ro_clo_dt,'MON,RRRR'),TO_CHAR(b.ro_clo_dt,'MM')
ORDER BY 4
The output of the query is:
CON_AMT LUB_AMT DT1 TO
--------- --------- -------- --
2970 21055 JAN,2009 01
3930 28780 FEB,2009 02
2765 23235 MAR,2009 03
3370 13010 APR,2009 04
10525 41805 MAY,2009 05
7613 34825 JUN,2009 06
5087 35725 JUL,2009 07
10639 52610 AUG,2009 08
3270 31395 SEP,2009 09
5540 35141 OCT,2009 10
I have joined these tables by using RO column that is my primary key in RO_MST table but unfortunately result is not correct.
I want to create such type of query through which i can use ro_clo_dt column as a common column and with reference to ro_clo_dt column monthly sum of Lub and Con will show accordingly .kindly help me.
Regards
Zuhair
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430766 is a reply to message #430731] |
Thu, 12 November 2009 05:09 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can't sum up amounts from multiple child tables using a simple join.
Remove the group by and sum from your query and you'll see why it won't work. You might want to add the pk columns for the detail tables to the select for clarity.
One solution is to use sub-queries in the select to get the sums.
And you really need to fix the date issue Michel pointed out. It's a bug waiting to happen.
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430801 is a reply to message #430731] |
Thu, 12 November 2009 06:30 |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Please find below test case for table creation:
SQL> create table ro_mst
2 (
3 RO_CUST_NM VARCHAR2(60),
4 RO_CUST_ADD VARCHAR2(100),
5 RO_CUST_PHN VARCHAR2(15),
6 RO_NO VARCHAR2(5),
7 ro_clo_dt date);
Table created.
SQL> ALTER TABLE ro_mst
2 ADD CONSTRAINT pk_ro_no
3 PRIMARY KEY (ro_no);
SQL>
1 create table ro_con_dtl
2 (
3 RO_CON_CD VARCHAR2(5),
4 RO_NO VARCHAR2(5),
5 APPR_AMT NUMBER(5));
SQL> /
Table created.
SQL> alter table ro_con_dtl
2 add constraint fk_ro_no
3 foreign key (ro_no)
4 referencing ro_mst(ro_no);
Table altered.
1 create table ro_lub_dtl
2 (
3 RO_LUB_CD VARCHAR2(5),
4 RO_NO VARCHAR2(5),
5 APPR_AMT NUMBER(5)
6* )
SQL> /
Table created.
1 alter table ro_lub_dtl
2 add constraint fk_ro_no_lub
3 foreign key (ro_no)
4* referencing ro_mst(ro_no)
SQL> /
Table altered.
Please find below test case for insertion:
1 insert into ro_mst
2 (
3 ro_cust_nm,ro_no,ro_clo_dt)
4 values
5* ('zuh','01','02JAN2009')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_mst
2 (
3 ro_cust_nm,ro_no,ro_clo_dt)
4 values
5* ('ali','02','01JAN2009')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_mst
2 (
3 ro_cust_nm,ro_no,ro_clo_dt)
4 values
5* ('ali','03','01FEB2009')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_mst
2 (
3 ro_cust_nm,ro_no,ro_clo_dt)
4 values
5* ('ali','04','01MAR2009')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_con_dtl
2 (ro_con_cd,ro_no,appr_amt)
3 values
4* ('a1','01',100)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_con_dtl
2 (ro_con_cd,ro_no,appr_amt)
3 values
4* ('a2','02',200)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_con_dtl
2 (ro_con_cd,ro_no,appr_amt)
3 values
4* ('a3','02',300)
SQL>
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_con_dtl
2 (ro_con_cd,ro_no,appr_amt)
3 values
4* ('a3','03',300)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_con_dtl
2 (ro_con_cd,ro_no,appr_amt)
3 values
4* ('a3','04',300)
SQL> /
1 row created.
SQL> insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4 ('L1','01',100);
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4* ('L1','01',100)
SQL>
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4* ('L1','02',100)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4* ('L1','03',100)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4* ('L1','04',100)
SQL> /
1 row created.
SQL> commit;
My desired output is :
Date Con Lub
Jan,2009 600 300
Feb,2009 300 100
Mar,2009 300 100
Kindly help
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430819 is a reply to message #430766] |
Thu, 12 November 2009 07:15 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Thu, 12 November 2009 11:09You can't sum up amounts from multiple child tables using a simple join.
Remove the group by and sum from your query and you'll see why it won't work. You might want to add the pk columns for the detail tables to the select for clarity.
One solution is to use sub-queries in the select to get the sums.
And you really need to fix the date issue Michel pointed out. It's a bug waiting to happen.
Did you try following either of my suggestions?
The first will give you a much better understanding of why you are having a problem.
And next time you post a test case can you:
a) Not use a copy and paste from sqplus complete with line numbers. It should be in the form of a script that the rest of use can run without editing.
b) to_date all dates. This is something you should always do. Just because leaving out the to_date works with your current date format doesn't mean it's a good idea.
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430897 is a reply to message #430731] |
Thu, 12 November 2009 23:45 |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Thanks cookiemonster,I will be careful next time and using to_date with all date type columns,I was trying to use subquery but didnt get correct output. My subquery is:
Select sum(a.appr_amt),
c.Lub,
TO_CHAR(B.RO_CLO_DT,'Mon,RRRR')
from ro_con_dtl a,
ro_mst b,
(Select sum(a.appr_amt) Lub,
TO_CHAR(B.RO_CLO_DT,'Mon,RRRR')
from ro_lub_dtl a,
ro_mst b
where a.ro_no=b.ro_no
and to_date(b.ro_clo_dt) between to_date ('01JAN2009') AND to_date('31OCT2009')
Group by to_char(B.RO_CLO_DT,'Mon,RRRR')
)c
where a.ro_no=b.ro_no
and to_date(b.ro_clo_dt) between to_date('01JAN2009') AND to_date('31OCT2009')
GROUP BY TO_CHAR(B.RO_CLO_DT,'Mon,RRRR'),c.Lub
and its result is :
SUM(A.APPR_AMT) LUB TO_CHAR(
--------------- ---------- --------
600 100 Feb,2009
300 300 Feb,2009
1200 100 Jan,2009
600 300 Jan,2009
600 100 Mar,2009
300 300 Mar,2009
Dear Michel
I hadn't got any error(like literal does not match) while inserting records in ro_mst.
I have tried one more time by inserting record into ro_mst but didn't face any error literal error and insert records successfully .
Regards
Zuhair
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430901 is a reply to message #430897] |
Fri, 13 November 2009 00:26 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
Quote:My desired output is :
Date Con Lub
Jan,2009 600 300
Feb,2009 300 100
Mar,2009 300 100
SQL> SELECT a.con, con_amt, lub_amt
2 FROM (SELECT NVL (SUM (c.appr_amt), 0) lub_amt,
3 TO_CHAR (d.ro_clo_dt, 'MON,RRRR') con,
4 TO_CHAR (d.ro_clo_dt, 'MM')
5 FROM ro_lub_dtl c, ro_mst d
6 WHERE d.ro_clo_dt BETWEEN '01JAN09' AND '31OCT09'
7 AND c.ro_no = d.ro_no
8 GROUP BY TO_CHAR (d.ro_clo_dt, 'MON,RRRR'),
9 TO_CHAR (d.ro_clo_dt, 'MM')) a,
10 (SELECT NVL (SUM (a.appr_amt), 0) con_amt,
11 TO_CHAR (b.ro_clo_dt, 'MON,RRRR') con,
12 TO_CHAR (b.ro_clo_dt, 'MM')
13 FROM ro_con_dtl a, ro_mst b
14 WHERE a.ro_no = b.ro_no
15 AND b.ro_clo_dt BETWEEN '01JAN09' AND '31OCT09'
16 GROUP BY TO_CHAR (b.ro_clo_dt, 'MON,RRRR'),
17 TO_CHAR (b.ro_clo_dt, 'MM')) b
18 WHERE a.con = b.con
19 ORDER BY 2 DESC
20 /
CON CON_AMT LUB_AMT
-------- ---------- ----------
JAN,2009 600 300
FEB,2009 300 100
MAR,2009 300 100 |
|
|
|
| Re: Cobines two queries and using one uniform column [message #430908 is a reply to message #430897] |
Fri, 13 November 2009 00:47 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
gozuhair wrote on Fri, 13 November 2009 06:45Thanks cookiemonster,I will be careful next time and using to_date with all date type columns,I was trying to use subquery but didnt get correct output. My subquery is:
and to_date(b.ro_clo_dt) between to_date ('01JAN2009') AND to_date('31OCT2009')
to_date should be used in its form with TWO parameters. Always add an explicit format mask.
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430927 is a reply to message #430731] |
Fri, 13 November 2009 03:52 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's a lot simpler if you just put the sub-queries in the select part:
SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt,
FROM ro_mst m
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY');
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430936 is a reply to message #430731] |
Fri, 13 November 2009 04:56 |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Thank you very much for your support,Mr. AYush query is working fine but there is a little problem in ayush_anand query, the problem is that if i have inserted one more record in ro_mst table and ro_lub_dtl like this:
--insert into ro_mst table
insert into ro_mst
(ro_cust_nm,ro_no,ro_clo_dt)
values
('Kashan','05','01APR2009');
--insert into ro_lub_dtl table
insert into ro_lub_dtl
(ro_lub_cd,ro_no,appr_amt)
values
('L5','05',100);
The result is display same as earlier and the new inserted record not shown due to ro_clo_dt equijoin.
The second query of Mr.cookiemonster is not working because in this query group by clause is omitted and if i am trying to insert group by clause then query is giving the following error.
ERROR at line 2:
ORA-00979: not a GROUP BY expression
I have written my group by clause is like this:
group by to_char(a.ro_clo_dt,'MON,RRRR'),Con_Amt,Lub_Amt
if i am using group by only on ro_clo_dt then the following error appear on screen.
ERROR at line 2:
ORA-00979: not a GROUP BY expression
Please suggest
[Updated on: Fri, 13 November 2009 05:54] Report message to a moderator |
|
|
|
| Re: Cobines two queries and using one uniform column [message #430939 is a reply to message #430936] |
Fri, 13 November 2009 05:36 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> insert into ro_mst
2 (ro_cust_nm,ro_no,ro_clo_dt)
3 values
4 ('Kashan','05','01APR2009');
('Kashan','05','01APR2009')
*
ERROR at line 4:
ORA-01861: literal does not match format string
Yes, the result is the same.
Regards
Michel
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430940 is a reply to message #430927] |
Fri, 13 November 2009 05:38 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
cookiemonster wrote on Fri, 13 November 2009 03:52It's a lot simpler if you just put the sub-queries in the select part:
SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt,FROM ro_mst m
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY');
Gives
ERROR at line 4:
ORA-00936: missing expression
Of course its not an issue  ...But the Query gives..the output like
SQL> ED
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
4 FROM ro_mst m
5* WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
SQL> /
TO_CHAR( CON_AMT LUB_AMT
-------- ---------- ----------
JAN,2009 100 200
JAN,2009 500 100
FEB,2009 300 100
MAR,2009 300 100
APR,2009 0 100
OP wants the sum amount amt ....As this is Ok for OP,this is just to show you...  ...& Ignore it
[Updated on: Fri, 13 November 2009 05:43] Report message to a moderator |
|
|
|
| Re: Cobines two queries and using one uniform column [message #430942 is a reply to message #430939] |
Fri, 13 November 2009 05:47 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
It is ok for me
SQL> --insert into ro_mst table
SQL> insert into ro_mst
2 (ro_cust_nm,ro_no,ro_clo_dt)
3 values
4 ('Kashan','05','01APR2009');
1 row created.
SQL> --insert into ro_lub_dtl table
SQL> insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4 ('L5','05',100);
1 row created.
SQL> commit;
Commit complete.
SQL>
sriram
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430944 is a reply to message #430936] |
Fri, 13 November 2009 06:14 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
see the cases....
SQL> ed
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt,
4 FROM ro_mst m
5* WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY')
SQL> /
FROM ro_mst m
*
ERROR at line 4:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
4 FROM ro_mst m
5* WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY')
SQL> /
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY')
*
ERROR at line 5:
ORA-01810: format code appears twice
SQL> ed
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
4 FROM ro_mst m
5* WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
SQL> /
TO_CHAR( CON_AMT LUB_AMT
-------- ---------- ----------
JAN,2009 100 200
JAN,2009 500 100
FEB,2009 300 100
MAR,2009 300 100
MAY,2009 0 700
APR,2009 0 100
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
4 FROM ro_mst m
5 WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
6* GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR')
SQL> /
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
SQL> ED
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
3 nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
4 FROM ro_mst m
5 WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
6* GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR'),
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl
WHERE ro_no = m.ro_no), 0),
nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0)
SQL> /
GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR'),nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0),nvl((SELECT SUM(appr_amt) FROM ro_lub_
dtl WHERE ro_no = m.ro_no), 0)
*
ERROR at line 6:
ORA-22818: subquery expressions not allowed here
SQL> ED
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl(SUM((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no)), 0) con_amt,
3 nvl(SUM((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no)), 0) lub_amt
4 FROM ro_mst m
5 WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
6* GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR'),nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0),nvl((SELECT SUM(appr_amt) FROM ro
_lub_dtl WHERE ro_no = m.ro_no), 0)
SQL> /
GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR'),nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0),nvl((SELECT SUM(appr_amt) FROM ro_lub_
dtl WHERE ro_no = m.ro_no), 0)
*
ERROR at line 6:
ORA-22818: subquery expressions not allowed here
SQL> ED
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl(SUM((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no)), 0) con_amt,
3 nvl(SUM((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no)), 0) lub_amt
4 FROM ro_mst m
5 WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
6* GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR')
SQL> /
TO_CHAR( CON_AMT LUB_AMT
-------- ---------- ----------
JAN,2009 600 300
MAY,2009 0 700
APR,2009 0 100
MAR,2009 300 100
FEB,2009 300 100
SQL>
SQL> ed
Wrote file afiedt.buf
1 SELECT to_char (m.ro_clo_dt, 'MON,RRRR'),
2 nvl(SUM((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no)), 0) con_amt,
3 nvl(SUM((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no)), 0) lub_amt
4 FROM ro_mst m
5 WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY')
6 GROUP BY to_char (m.ro_clo_dt, 'MON,RRRR')
7* order by 2 desc
SQL> /
TO_CHAR( CON_AMT LUB_AMT
-------- ---------- ----------
JAN,2009 600 300
MAR,2009 300 100
FEB,2009 300 100
MAY,2009 0 700
APR,2009 0 100
sriram
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #430946 is a reply to message #430942] |
Fri, 13 November 2009 06:21 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
This should work. I forgot there was two entries in ro_mst for Jan and I didn't bother testing it since you gave us a test case that required lots of editing:
SELECT ro_month, SUM(con_amt), SUM(lub_amt)
FROM (SELECT to_char (m.ro_clo_dt, 'MON,RRRR') ro_month, m.ro_no,
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
FROM ro_mst m
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY'))
GROUP BY ro_month;
ramoradba wrote on Fri, 13 November 2009 11:47It is ok for me
SQL> --insert into ro_mst table
SQL> insert into ro_mst
2 (ro_cust_nm,ro_no,ro_clo_dt)
3 values
4 ('Kashan','05','01APR2009');
1 row created.
SQL> --insert into ro_lub_dtl table
SQL> insert into ro_lub_dtl
2 (ro_lub_cd,ro_no,appr_amt)
3 values
4 ('L5','05',100);
1 row created.
SQL> commit;
Commit complete.
SQL>
sriram
So? If it doesn't work for some of us then it proves there's a problem. Plus even if you don't get an error it doesn't mean it worked properly. I get this:
SQL> insert into ro_mst
2 (
3 ro_cust_nm,ro_no,ro_clo_dt)
4 values
5 ('zuh','01','02JAN2009');
1 row created.
SQL> select to_char(ro_clo_dt, 'DD-MON-YYYY') from ro_mst;
TO_CHAR(RO_CLO_DT
-----------------
20-JAN-0002
SQL>
Completely different date.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #431052 is a reply to message #430731] |
Fri, 13 November 2009 23:52 |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Thank you very much specially cookie and Anand ,provided query is working perfectly.
SELECT ro_month, SUM(con_amt), SUM(lub_amt)
FROM (SELECT to_char (m.ro_clo_dt, 'MON,RRRR') ro_month, m.ro_no,
nvl((SELECT SUM(appr_amt) FROM ro_con_dtl WHERE ro_no = m.ro_no), 0) con_amt,
nvl((SELECT SUM(appr_amt) FROM ro_lub_dtl WHERE ro_no = m.ro_no), 0) lub_amt
FROM ro_mst m
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY'))
GROUP BY ro_month;
Thank you very much for suporting me
Regards
Zuhair
|
|
|
|
| Re: Cobines two queries and using one uniform column [message #431070 is a reply to message #431052] |
Sat, 14 November 2009 05:12 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
gozuhair wrote on Sat, 14 November 2009 06:52
SELECT to_char (m.ro_clo_dt, 'MON,RRRR')
Using RRRR makes no sense when converting a date to a string. RRRR (with 4 Rs) does not make sense anyway; RR was invented to circumvent the year 2000 problem, where dates were stored as strings with 2 digit years.
10 years after, RR should NOT be used. Instead, dates should be entered with 4 digit years.
Quote:
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMONYY') AND to_date('31OCT09', 'DDMONYY'))
Same here. Years have FOUR digits, not two.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:43:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
