Home » SQL & PL/SQL » SQL & PL/SQL » Union error
Union error [message #211997] Wed, 03 January 2007 07:31 Go to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Hi,

Can anyone help me.

I am using union to club 3 quries. All the 3 queries are running fine form backend and from reports side(COGNOS Cool.But in one particular case only one query is subitted to the report. Then it is firing one error and the error is " Data type mismatch". And that particular query is working from backend.

So, what should I do to resolve this error.


Thanks in advance

Ananth.
Re: Union error [message #212000 is a reply to message #211997] Wed, 03 January 2007 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Can you describe that "particular case" (including sample data)? Can you post those queries along with table descriptions? Does, perhaps, implicit datatype conversion work correctly until it runs into an unexpected condition?
Re: Union error [message #212002 is a reply to message #212000] Wed, 03 January 2007 07:50 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
"Particular Case" is nothing but we have different plants. I need to show the data when the plants are locked. when the plants are not locked then no need to show the data.

example data:

select TN.plant_id , to_number(null) month_value from table_name TN
union
select TN.plant_id , TB.month_value from table_name TN,table_name TB where TN.column_name = TB.column_name
union
select TN.plant_id, TN.month_value from table_name TN,table_name TB where TN.column_name = TB.column_name;

The above runs when the plants are locked. When those are not locked then only first query runs.

I am getting error when first query runs. I am not getting error when all the 3 queries are running.

Thanks
Ananth
Re: Union error [message #212035 is a reply to message #212002] Wed, 03 January 2007 10:08 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is "tb.month_value"? Date? Number? Its name suggests that it is a DATE, but TO_NUMBER function suggests that it should be a NUMBER.

TO_NUMBER(NULL) is NULL. An "ordinary" Oracle SQL statement works fine:
SQL> select to_number(null) month_value from dual
  2  union
  3  select 2 month_value from dual;

MONTH_VALUE
-----------
          2


SQL> select to_number(null) month_value from dual
  2  union
  3  select sysdate month_value from dual;

MONTH_VA
--------
03.01.07


SQL>
I don't know what COGNOS does in such a case.

Could you test it and remove TO_NUMBER function and replace it with either a number or a date?
Re: Union error [message #212129 is a reply to message #212035] Wed, 03 January 2007 22:16 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
yah,

I have tried that. If I replace To_number(null) with NUll then fires an error when ever the query returns nulls.


Re: Union error [message #212147 is a reply to message #212129] Thu, 04 January 2007 00:44 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It probably won't help much, but ... perhaps you'll fool COGNOS.
Quote:

I said : replace it with either a number or a date
You did: replaced To_number(null) with NULL
Previous Topic: Sql query performance
Next Topic: External Tables
Goto Forum:
  


Current Time: Sat Dec 10 12:57:16 CST 2016

Total time taken to generate the page: 0.22275 seconds