Home » SQL & PL/SQL » SQL & PL/SQL » 2 separate select works but when union all gives error (oracle XE apex)
2 separate select works but when union all gives error [message #352928] Fri, 10 October 2008 04:53 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
i know i am doing some silly mistake but i am not able to find immediately can any one help me.
my first select statement and result

select '01-may-04' as t_date, null as t_vou_no, e.code0 as t_desc, e.debit0 as t_debit,
e.credit0 as t_credit, '0' as flag1, substr(e.code0,1,5) as flag0 from
(select d.code0 as code0,  to_char(
(case when sum(nvl(d.debit0,0))>sum(nvl(d.credit0,0)) then sum(nvl(d.debit0,0))-sum(nvl(d.credit0,0))  end)
,'99999999G99G990D00') as debit0, 
TO_CHAR(
(case when sum(nvl(d.credit0,0))>sum(nvl(d.debit0,0)) then sum(nvl(d.credit0,0))-sum(nvl(d.debit0,0)) end)
,'99G99G990D00') as credit0 from
(select a.dt_fin_code||'-'|| b.fin_desc || ' ' || 'Opening Balance ' as code0, 
a.dt_vou_debit as debit0, a.dt_vou_credit as credit0 from vou_detail a, fin_mst b, vou_header c
where a.dt_fin_code = b.fin_code and
c.hd_vou_no = a.dt_vou_no and
c.hd_vou_dt < '01-may-04' ) d 
group by (d.code0)) e
union all
select '31-may-04' as t_date, null as t_vou_no, e.code0 as t_desc, e.debit0 as t_debit,
e.credit0 as t_credit, '3' as flag1, substr(e.code0,1,5) as flag0 from
(select d.code0 as code0,  to_char(
(case when sum(nvl(d.debit0,0))>sum(nvl(d.credit0,0)) then sum(nvl(d.debit0,0))-sum(nvl(d.credit0,0))  end)
,'99999999G99G990D00') as debit0, 
TO_CHAR(
(case when sum(nvl(d.credit0,0))>sum(nvl(d.debit0,0)) then sum(nvl(d.credit0,0))-sum(nvl(d.debit0,0)) end)
,'99G99G990D00') as credit0 from
(select a.dt_fin_code||'-'|| b.fin_desc || ' ' || 'Closing Balance ' as code0, 
a.dt_vou_debit as debit0, a.dt_vou_credit as credit0 from vou_detail a, fin_mst b, vou_header c
where a.dt_fin_code = b.fin_code and
c.hd_vou_no = a.dt_vou_no and
c.hd_vou_dt <= '31-may-04' ) d 
group by (d.code0)) e

T_DATE T_VOU_NO T_DESC T_DEBIT T_CREDIT FLAG1 FLAG0 
01-may-04 -  L4151-BONUS Opening Balance  4,850.00 -  0 L4151 
01-may-04 -  L3601-PURCHASE - CLEANING MATERIALS Opening Balance  97.00 -  0 L3601 
01-may-04 -  L5001-PRINTING & STATIONARY Opening Balance  7.00 -  0 L5001 
01-may-04 -  L6101-RENTAL RECEIPTS Opening Balance  -  10,939.00 0 L6101 
01-may-04 -  L3631-"PURCHASE - H/W CIVIL,MECH,ELEC" Opening Balance  93.60 -  0 L3631 
01-may-04 -  L2401-CASH Opening Balance  5,891.40 -  0 L2401 
31-may-04 -  L6101-RENTAL RECEIPTS Closing Balance  -  23,876.00 3 L6101 
31-may-04 -  L4101-SALARIES Closing Balance  17,950.00 -  3 L4101 
31-may-04 -  L3601-PURCHASE - CLEANING MATERIALS Closing Balance  97.00 -  3 L3601 
31-may-04 -  L5001-PRINTING & STATIONARY Closing Balance  7.00 -  3 L5001 
 



my second select

select g.hd_vou_dt as t_date, f.dt_vou_no as t_vou_no, f.dt_vou_narate as t_desc,
to_char(nvl(f.dt_vou_debit,0),'99G99G99G999D99') as t_debit, to_char(nvl(f.dt_vou_credit,0),'99G99G99G999D99')
 as t_credit, '1' as flag1, f.dt_fin_code as flag0
from vou_detail f, vou_header g where f.dt_vou_no = g.hd_vou_no and  g.hd_vou_dt between '01-may-04' and '31-may-04'

T_DATE T_VOU_NO T_DESC T_DEBIT T_CREDIT FLAG1 FLAG0 
05-MAY-04 41 03/2004 17,950.00 .00 1 L4101 
05-MAY-04 42 painting 1,020.00 .00 1 L4801 
05-MAY-04 43 -  3,240.00 .00 1 L5111 
05-MAY-04 44 No of Receipt 1 1,000.00 .00 1 L2401 
07-MAY-04 100 No of Receipt 1 3,119.00 .00 1 L2401 
07-MAY-04 101 No of Receipt 4 2,267.00 .00 1 L2401 
09-MAY-04 168 No of Receipt 6 3,500.00 .00 1 L2401 
09-MAY-04 169 No of Receipt 5 3,051.00 .00 1 L2401 
05-MAY-04 41 03/2004 .00 17,950.00 1 L2401 
05-MAY-04 42 painting .00 1,020.00 1 L2401 




my combined above two selects and error message

select '01-may-04' as t_date, null as t_vou_no, e.code0 as t_desc, e.debit0 as t_debit,
e.credit0 as t_credit, '0' as flag1, substr(e.code0,1,5) as flag0 from
(select d.code0 as code0,  to_char(
(case when sum(nvl(d.debit0,0))>sum(nvl(d.credit0,0)) then sum(nvl(d.debit0,0))-sum(nvl(d.credit0,0))  end)
,'99999999G99G990D00') as debit0, 
TO_CHAR(
(case when sum(nvl(d.credit0,0))>sum(nvl(d.debit0,0)) then sum(nvl(d.credit0,0))-sum(nvl(d.debit0,0)) end)
,'99G99G990D00') as credit0 from
(select a.dt_fin_code||'-'|| b.fin_desc || ' ' || 'Opening Balance ' as code0, 
a.dt_vou_debit as debit0, a.dt_vou_credit as credit0 from vou_detail a, fin_mst b, vou_header c
where a.dt_fin_code = b.fin_code and
c.hd_vou_no = a.dt_vou_no and
c.hd_vou_dt < '01-may-04' ) d 
group by (d.code0)) e
union all
select '31-may-04' as t_date, null as t_vou_no, e.code0 as t_desc, e.debit0 as t_debit,
e.credit0 as t_credit, '3' as flag1, substr(e.code0,1,5) as flag0 from
(select d.code0 as code0,  to_char(
(case when sum(nvl(d.debit0,0))>sum(nvl(d.credit0,0)) then sum(nvl(d.debit0,0))-sum(nvl(d.credit0,0))  end)
,'99999999G99G990D00') as debit0, 
TO_CHAR(
(case when sum(nvl(d.credit0,0))>sum(nvl(d.debit0,0)) then sum(nvl(d.credit0,0))-sum(nvl(d.debit0,0)) end)
,'99G99G990D00') as credit0 from
(select a.dt_fin_code||'-'|| b.fin_desc || ' ' || 'Closing Balance ' as code0, 
a.dt_vou_debit as debit0, a.dt_vou_credit as credit0 from vou_detail a, fin_mst b, vou_header c
where a.dt_fin_code = b.fin_code and
c.hd_vou_no = a.dt_vou_no and
c.hd_vou_dt <= '31-may-04' ) d 
group by (d.code0)) e
union all
select g.hd_vou_dt as t_date, f.dt_vou_no as t_vou_no, f.dt_vou_narate as t_desc,
to_char(nvl(f.dt_vou_debit,0),'99G99G99G999D99') as t_debit, to_char(nvl(f.dt_vou_credit,0),'99G99G99G999D99')
 as t_credit, '1' as flag1, f.dt_fin_code as flag0
from vou_detail f, vou_header g where f.dt_vou_no = g.hd_vou_no and  g.hd_vou_dt between '01-may-04' and '31-may-04'

ORA-01790: expression must have same datatype as corresponding expression 




please help me to locate the error
yours
dr.s.raghunathan
Re: 2 separate select works but when union all gives error [message #352931 is a reply to message #352928] Fri, 10 October 2008 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really expect we have a look at this mess?
Don't you know SQL formater?

FOLLOW THE GUIDELINES.

Regards
Michel
Re: 2 separate select works but when union all gives error [message #352942 is a reply to message #352931] Fri, 10 October 2008 05:25 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear michel,

so far i was using code, quotes from the icon displayed in the formatting tool.

Quote:


Don't you know SQL formater?





yes. i do not know the sql formater of course i have not mentioned table description . i simply cut and pasted my workings from the browser. Anyway i will try to learn that before posting

yours
dr.s.raghunathan
Re: 2 separate select works but when union all gives error [message #352943 is a reply to message #352928] Fri, 10 October 2008 05:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
ORA-01790: expression must have same datatype as corresponding expression

I really wonder what you do not understand in this error message. Did you bother to check data types of all corresponding columns?
For your information, '01-may-04' constant has VARCHAR2 data type. Not sure about data type of NULL, however you may (shall) CAST it to the desired data type.
Re: 2 separate select works but when union all gives error [message #352944 is a reply to message #352928] Fri, 10 October 2008 05:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
select '31-may-04' as t_date

Something tells me joy_division is about to jump in...

What is the datatype of g.hd_vou_dt?
Re: 2 separate select works but when union all gives error [message #352946 is a reply to message #352928] Fri, 10 October 2008 05:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First field is '31-may-04' ( CHARACTER) in first query and g.hd_vou_dt (might be DATE ) in the second query .

Thumbs Up
Rajuvan
Re: 2 separate select works but when union all gives error [message #352947 is a reply to message #352928] Fri, 10 October 2008 05:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Frank you beat me again ..

Thumbs Up
Rajuvan.
Re: 2 separate select works but when union all gives error [message #352950 is a reply to message #352944] Fri, 10 October 2008 05:37 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
Quote:


What is the datatype of g.hd_vou_dt?




yes it is date type. I used to_char. still it gives error.

null type data t_vou_no in the table it is number. is it necessary to convert this number data type too to_char. since i use to store apex page item with numeric value though by default is the text item.

yours
dr.s.raghunathan

Re: 2 separate select works but when union all gives error [message #352951 is a reply to message #352942] Fri, 10 October 2008 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
yes. i do not know the sql formater

I assume that with 240 posts you should know the one mentioned in OraFAQ Forum Guide: SQL Formatter

Regards
Michel
Re: 2 separate select works but when union all gives error [message #352958 is a reply to message #352928] Fri, 10 October 2008 05:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Now ,

It is not easy to find the actual issue from the "code Mess" .
Another solution ( that I follow usually Smile )

Make the union of two select statement for One column first , then two columns , then three columns and so on till 7 columns. You will get to know where actually the problem comes from . . Another form of trial and error method .

Thumbs Up
Rajuvan.
Re: 2 separate select works but when union all gives error [message #352961 is a reply to message #352951] Fri, 10 October 2008 05:56 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear micheal,
it is very interesting to know one other tool. is it possible to create one more icon for sql formater under formating tools so that we shall write it there and cut and paste it in post form. there is one quote your place is very prestigious and people use to envy and like to visit that place atleast once in life term. Whereas the person resides at that location may not aware of it. it is true to me.
thanks
yours
dr.s.raghunathan



Re: 2 separate select works but when union all gives error [message #352983 is a reply to message #352961] Fri, 10 October 2008 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good idea.
Post this in "Feedback & Suggestion" forum then Frank (Naude) will read it. He is the one that can modify this site.

Regards
Michel
Re: 2 separate select works but when union all gives error [message #352999 is a reply to message #352944] Fri, 10 October 2008 08:23 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Fri, 10 October 2008 06:28
Quote:
select '31-may-04' as t_date

Something tells me joy_division is about to jump in...



Looks like others have already covered it, but I will additionally point out
Quote:
c.hd_vou_dt < '01-may-04'
is not valid as this is comparing a DATE column to a character string.
Previous Topic: Confusions regarding the code
Next Topic: Single quote problem
Goto Forum:
  


Current Time: Sat Dec 10 12:43:43 CST 2016

Total time taken to generate the page: 0.10982 seconds