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 Go to next message
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 #430734 is a reply to message #430731] Thu, 12 November 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first need to fix:
b.ro_clo_dt between '01JAN09' AND '31OCT09'

Do you realize that 30OCT00 is between the values you gave?

To get the result you want, just join the 2 queries on date.
If you want to be more efficient, merge them.
What is exactly your problem in doing so?

Regards
Michel
Re: Cobines two queries and using one uniform column [message #430761 is a reply to message #430731] Thu, 12 November 2009 04:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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 #430771 is a reply to message #430761] Thu, 12 November 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
First of All, Thanks for the suggestion regardng date issue,

Quote:
 b.ro_clo_dt between '01JAN2009' AND '31OCT2009'

That is: thanks but I don't care of what you say.

Quote:
at the present when i am trying to merge these query by using ro column joing then the result is not not correct.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Cobines two queries and using one uniform column [message #430801 is a reply to message #430731] Thu, 12 November 2009 06:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Thu, 12 November 2009 11:09
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.


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 #430828 is a reply to message #430801] Thu, 12 November 2009 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> insert into ro_mst
  2   (
  3   ro_cust_nm,ro_no,ro_clo_dt)
  4   values
  5   ('zuh','01','02JAN2009')
  6  /
 ('zuh','01','02JAN2009')
             *
ERROR at line 5:
ORA-01861: literal does not match format string

Regards
Michel
Re: Cobines two queries and using one uniform column [message #430897 is a reply to message #430731] Thu, 12 November 2009 23:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
gozuhair wrote on Fri, 13 November 2009 06:45
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:
               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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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 #430928 is a reply to message #430927] Fri, 13 November 2009 03:54 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
WHERE m.ro_clo_dt BETWEEN to_date('01JAN09', 'DDMMMMYY') AND to_date('31OCT09', 'DDMMMMYY');


Good approach just mind the date format
I already missed that in my post Smile

[Updated on: Fri, 13 November 2009 03:55]

Report message to a moderator

Re: Cobines two queries and using one uniform column [message #430936 is a reply to message #430731] Fri, 13 November 2009 04:56 Go to previous messageGo to next message
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.
WHERE a.con = b.con

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 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
cookiemonster wrote on Fri, 13 November 2009 03:52
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');


Gives

ERROR at line 4:
ORA-00936: missing expression
.

Of course its not an issue Wink...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... Smile ...& Ignore it Smile


[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 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It is ok for me Smile
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 #430943 is a reply to message #430942] Fri, 13 November 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It is ok for me

Which does not prove anything.

It's NOT ok for me which proves the statement is wrong.

Regards
Michel

[Updated on: Fri, 13 November 2009 06:06]

Report message to a moderator

Re: Cobines two queries and using one uniform column [message #430944 is a reply to message #430936] Fri, 13 November 2009 06:14 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 #430945 is a reply to message #430943] Fri, 13 November 2009 06:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Fri, 13 November 2009 06:06
Quote:
It is ok for me

Which does not prove anything.

It's NOT ok for me which proves the statement is wrong.

Regards
Michel


Michel sir,If the query not executed,How anand,cookie,me posting the sql sessions here?

I posted the result from sqlplus cmd promt

Or shall i post the screen shot to prove?

Sriram Smile
Re: Cobines two queries and using one uniform column [message #430946 is a reply to message #430942] Fri, 13 November 2009 06:21 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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:47
It is ok for me Smile
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 #430947 is a reply to message #430944] Fri, 13 November 2009 06:25 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And dont get confuse for May record i have that too

SQL> desc ro_mst
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RO_CUST_NM                                         VARCHAR2(60)
 RO_CUST_ADD                                        VARCHAR2(100)
 RO_CUST_PHN                                        VARCHAR2(15)
 RO_NO                                     NOT NULL VARCHAR2(5)
 RO_CLO_DT                                          DATE

SQL> select RO_CUST_NM||'  '|| RO_CUST_ADD||'  '||RO_CUST_PHN||'  '||RO_NO||'  '||RO_CLO_DT
  2  from ro_mst;

RO_CUST_NM||''||RO_CUST_ADD||''||RO_CUST_PHN||''||RO_NO||''||RO_CLO_DT
--------------------------------------------------------------------------------
zuh      01  02-JAN-09
ali      02  01-JAN-09
ali      03  01-FEB-09
ali      04  01-MAR-09
SRIRAM      06  01-MAY-09
Kashan      05  01-APR-09

6 rows selected.

SQL>


sriram
Re: Cobines two queries and using one uniform column [message #430948 is a reply to message #430946] Fri, 13 November 2009 06:31 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i agree.. Smile

And I really didn`t posted that for poining others....

And Michel..Please dont take it in a nagetive manner...I am sorry If it directly pointing you...Please ignore.

sriram. Smile

[Updated on: Fri, 13 November 2009 06:31]

Report message to a moderator

Re: Cobines two queries and using one uniform column [message #430950 is a reply to message #430945] Fri, 13 November 2009 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel sir,If the query not executed,How anand,cookie,me posting the sql sessions here?

I posted the result from sqlplus cmd promt

Or shall i post the screen shot to prove?

You do not have to prove me it works for you, I trust you.
But as it does not work for me it means that the query is not correct, otherwise it'd work for anyone.

Regards
Michel
Re: Cobines two queries and using one uniform column [message #430951 is a reply to message #430948] Fri, 13 November 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramoradba wrote on Fri, 13 November 2009 13:31
i agree.. Smile

And I really didn`t posted that for poining others....

And Michel..Please dont take it in a nagetive manner...I am sorry If it directly pointing you...Please ignore.

sriram. Smile

I don't take it negatively (so please don't mine either), I just wanted to emphasize on a very important point which is "does the application will work for every one or will it fail from time to time without any clue for the one that will maintain the code?".

Regards
Michel

Re: Cobines two queries and using one uniform column [message #431052 is a reply to message #430731] Fri, 13 November 2009 23:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Cobines two queries and using one uniform column [message #431421 is a reply to message #430731] Tue, 17 November 2009 05:11 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
ok ,will takecare next time,thanks for your support
Previous Topic: Pull out the values which is there in Listing but not there in Table
Next Topic: Run Shell Script
Goto Forum:
  


Current Time: Fri Sep 30 17:28:34 CDT 2016

Total time taken to generate the page: 0.21681 seconds