OraFAQ Forum: SQL & PL/SQL » Union query need to add extra dummy field to one side of the query (Ora-01790 error)

Home » SQL & PL/SQL » SQL & PL/SQL » Union query need to add extra dummy field to one side of the query (Ora-01790 error)

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

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

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

Frank
Messages: 7901
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

Report message to a moderator

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

Codheadred
Messages: 7
Registered: December 2005
Location: manchester

Junior Member

Hi Frank

Thanks for your prompt response thats worked as I wanted!

Steven

Report message to a moderator

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

source
Messages: 14
Registered: December 2005
Location: Australia

Junior Member

didn't know you could to that Frank... Thx!! Smile

Report message to a moderator

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

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

Report message to a moderator

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

Frank
Messages: 7901
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

oracle.murugesan
Messages: 1
Registered: July 2012
Location: Bangalore

Junior Member

Thank you

Frank

Report message to a moderator

Previous Topic:	Materialized View
Next Topic:	column Ambiguity

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 25 18:26:52 CDT 2025