Solved

UNION query in ORACLE

Posted on 2011-09-27
4
715 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 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

717 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