Home » SQL & PL/SQL » SQL & PL/SQL » Union query need to add extra dummy field to one side of the query (Ora-01790 error)
Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #150505] Thu, 08 December 2005 04:03 Go to next message
Codheadred
Messages: 7
Registered: December 2005
Location: manchester
Junior Member
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field).

However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)


however when I run the union query i get the error

Ora-01790 expression must have same datatype as corresponding expression

I may be going the wrong way about this any and would appreciate any help available as its pretty urgent

Thankls Steven

[Updated on: Thu, 08 December 2005 04:10]

Report message to a moderator

Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #150536 is a reply to message #150505] Thu, 08 December 2005 05:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In the second part of the query you should add a column of the same datatype as in the first part (in this case a date).
If there is no relevant date in the second part, you could add a "to_date(null)"

hth
Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #150552 is a reply to message #150536] Thu, 08 December 2005 06:50 Go to previous messageGo to next message
Codheadred
Messages: 7
Registered: December 2005
Location: manchester
Junior Member
Hi Frank

Thanks for your prompt response thats worked as I wanted!

Steven



Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #150739 is a reply to message #150505] Fri, 09 December 2005 06:25 Go to previous messageGo to next message
source
Messages: 14
Registered: December 2005
Location: Australia
Junior Member

didn't know you could to that Frank... Thx!! Smile
Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #212138 is a reply to message #150536] Wed, 03 January 2007 23:48 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Hi Frank,

What is the datatype of this column.

select 2 from dual;
union
select null from dual;

because some error is firing in COGNOS 8 reports when this query is executed there.


Thanks in advance.

Ananth
Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #212150 is a reply to message #212138] Thu, 04 January 2007 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And which exactly is that 'some error'?

[Edit: typo]


Hm, I see that you have an extra semicolon ( ; ) at the end of the first line. That would be an error.

[Updated on: Thu, 04 January 2007 00:51]

Report message to a moderator

Re: Union query need to add extra dummy field to one side of the query (Ora-01790 error) [message #579032 is a reply to message #150536] Thu, 07 March 2013 01:57 Go to previous message
oracle.murugesan
Messages: 1
Registered: July 2012
Location: Bangalore
Junior Member

Thank you

Frank

Previous Topic: Materialized View
Next Topic: column Ambiguity
Goto Forum:
  


Current Time: Sat Dec 10 20:41:28 CST 2016

Total time taken to generate the page: 0.07417 seconds