OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » union (sql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

union [message #641158]

Tue, 11 August 2015 05:56

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

Can anyone please help me to solve this error, actually i would like to make Union all to create one view from these two query, but i am getting error ora_01789 and i am also known why i am getting this error but i don't know how could i solve it.
help please?

select rid, r_pr, r_ml
  from rm
  where exists ( select 1 from rjs
                           where rjs.rid = rm.rid and rjs.text in ('TPD'))
UNION ALL
select rid
  from rjs
  where not in (select rm.rid from rm, rjf
                               where rjf.rid = rm.rid
                               and rjf.r_pr = rm.r_pr)
 and rjs.text in ('TPD');

Should it be have same field in both table while making Union? In my case there is no same fields in the table, but some of rid are not in the first table, thats why i would like to get These rid in the first table too.

thank u

Report message to a moderator

Re: union [message #641160 is a reply to message #641158]

Tue, 11 August 2015 06:20

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You must have the same number and type of fields in both queries you union.

Report message to a moderator

Re: union [message #641162 is a reply to message #641160]

Tue, 11 August 2015 06:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If there is no equivalent in the second query then null will do.

Report message to a moderator

Re: union [message #641163 is a reply to message #641160]

Tue, 11 August 2015 06:35

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

Thank you Michel. Ya, I also checked it Smile

well, then can i do this my query as:

select rid, r_pr, r_ml
  from rm
  where exists ( select 1 from rjs
                           where rjs.rid = rm.rid and rjs.text in ('TPD'))
UNION ALL
select * from rm
where (
       select rid
       from rjs
       where not in (select rm.rid from rm, rjf
                               where rjf.rid = rm.rid
                               and rjf.r_pr = rm.r_pr)
       and rjs.text in ('TPD'));

I changed it so.. n got error ORA-00936.
any Suggestion please?

Report message to a moderator