Solved

UNION query in ORACLE

Posted on 2011-09-27
4
706 Views
Last Modified: 2012-05-12
Greetings...
I am trying to execute the below SQL, but get the following error (all datatypes are number(15,0):
ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 -  "expression must have same datatype as corresponding expression"
*Cause:    
*Action:
Error at Line: 1 Column: 7

 
SELECT K2B.FI_LOCATION_SEQ_NUM AS FINANCIAL_INST_BRANCH_SEQ_NUM
FROM UCTR_2B K2B
UNION
SELECT NVL(TRIM(K3A.FINANCIAL_INST_BRANCH_SEQ_NUM), '-') as FINANCIAL_INST_BRANCH_SEQ_NUM
FROM SARSF_3A K3A
UNION
SELECT '-' as FINANCIAL_INST_BRANCH_SEQ_NUM
FROM RMSB_3A K3A

Open in new window


Could use a hand here...Thanks :)
0
Comment
Question by:Evan Cutler
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Change to:
select to_char(K2B.FI_LOCATION_SEQ_NUM)
...


All values need to be the same so value1 in ALL selects must be varchar2, number etc...
0
 
LVL 9

Author Comment

by:Evan Cutler
Comment Utility
That worked great, but why to_char?
and how does '-' force the requirement?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
'-' is a string literal and it is impossible to turn that into a number with to_number().

Oracle tries to to implicit data type conversions for you when it can.  In this case it saw K2B.FI_LOCATION_SEQ_NUM as a number so it tried to convert all the other first columns to a number.  It could not.
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
Comment Utility
You are AWESOME.
Thanks...

Another question with more points coming...lol
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now