Home » SQL & PL/SQL » SQL & PL/SQL » merging of sql scripts (10.2.0.1.0)
merging of sql scripts [message #407401] Wed, 10 June 2009 01:47 Go to next message
stingers23
Messages: 2
Registered: June 2009
Location: Makati Philippines
Junior Member
hi,
i have 3 scripts to which they have different third column output
now i do want to merge them for a corresponding output.
here are my scripts:
script 1:
select p.name principal, cl.name, count(*) "OR_COUNT"
from iteneraries i,receipts r,customers p,customers c,collectors cl
where i.pcode=p.code
and i.pcode = p.pcode
and i.bank_provider = p.bank_provider
and i.cust_code=c.code
and i.pcode = c.pcode
and i.bank_provider = c.bank_provider
and i.code=r.it_no
and i.pcode=r.pcode
and i.coll_code=cl.code
and (i.status is null or i.status='S')
and to_char(i.coll_date,'YYYYMMDD') >= 'date'
and to_char(i.coll_date,'YYYYMMDD') <= 'date'
and (substr(cl.name,1,3)='COL'
group by p.name,cl.name

script 2:
select p.name principal, cl.name,count(t.chk_no) "CHECKS"
from iteneraries i,transmittals t,customers p,customers c,collectors cl
where i.pcode=p.code
and i.pcode = p.pcode
and i.bank_provider = p.bank_provider
and i.cust_code=c.code
and i.pcode = c.pcode
and i.pcode= t.pcode
and i.code= t.it_no
and i.bank_provider = c.bank_provider
and i.coll_code=cl.code
and (i.status is null or i.status='S')
and to_char(i.coll_date,'YYYYMMDD') >= 'date'
and to_char(i.coll_date,'YYYYMMDD') <= 'date'
and (substr(cl.name,1,3)='COL'
group by p.name,cl.name

script 3:
select p.name principal,cl.name,count(b.ref_no) "INV_COUNT"
from iteneraries i,receipts r,payments b,customers p,customers c,collectors cl
where i.pcode=p.code
and i.pcode = p.pcode
and i.bank_provider = p.bank_provider
and i.cust_code=c.code
and i.pcode = c.pcode
and i.bank_provider = c.bank_provider
and i.code=r.it_no
and i.pcode=r.pcode
and r.code=b.rct_code
and i.coll_code=cl.code
and (i.status is null or i.status='S')
and to_char(i.coll_date,'YYYYMMDD') >= 'date'
and to_char(i.coll_date,'YYYYMMDD') <= 'date'
and (substr(cl.name,1,3)='COL'
group by p.name,cl.name) inv
where or.name=chk.name
and chk.name=inv.name
order by or.name

[B]new output:

PRINCIPAL NAME OR_COUNT CHECKS INV_COUNT
xxxxxxx xxxxxx 123 123 123


thanks,
Re: merging of sql scripts [message #407406 is a reply to message #407401] Wed, 10 June 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add inb one query the columns of the others.

In addition, "to_char(i.coll_date,'YYYYMMDD') >= 'date'" is something really wrong. Convert your date string constant into a date not the opposite.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: merging of sql scripts [message #407438 is a reply to message #407406] Wed, 10 June 2009 03:28 Go to previous messageGo to next message
stingers23
Messages: 2
Registered: June 2009
Location: Makati Philippines
Junior Member
i did previously add the other columns in the other queries but i get a wrong data..
and the edited line ,and to_char(i.coll_date,'YYYYMMDD') >= 'YYYYMMDD'

whats next?
Re: merging of sql scripts [message #407440 is a reply to message #407438] Wed, 10 June 2009 03:41 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove your claims and repost the queries and what you tried in formatted way.

Quote:
but i get a wrong data..

And maybe you did it wrong.

Quote:
and the edited line ,and to_char(i.coll_date,'YYYYMMDD') >= 'YYYYMMDD'

Do the opposite.

Regards
Michel

[Updated on: Wed, 10 June 2009 03:42]

Report message to a moderator

Previous Topic: Writing EXECUTE IMMEDIATE
Next Topic: ORA-00907: missing right parenthesis while creating the table with datatype timestamp
Goto Forum:
  


Current Time: Fri Dec 09 23:16:00 CST 2016

Total time taken to generate the page: 0.09164 seconds