• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

UNION query in ORACLE

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
Evan Cutler
Asked:
Evan Cutler
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Evan CutlerAuthor Commented:
That worked great, but why to_char?
and how does '-' force the requirement?
0
 
slightwv (䄆 Netminder) Commented:
'-' 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
 
Evan CutlerAuthor Commented:
You are AWESOME.
Thanks...

Another question with more points coming...lol
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now