Solved

UNION query in ORACLE

Posted on 2011-09-27
4
710 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)
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

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