?
Solved

UNION query in ORACLE

Posted on 2011-09-27
4
Medium Priority
?
718 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36712521
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
ID: 36712558
That worked great, but why to_char?
and how does '-' force the requirement?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36712632
'-' 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
ID: 36712847
You are AWESOME.
Thanks...

Another question with more points coming...lol
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 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