Trimming a number forces failure on MERGE

Greetings,
With this SQL, (in both select statements, the datatype is number(15,0))
 
SELECT NVL(TRIM(K3A.BSA_ID), 0) as FINANCIAL_INST_BRANCH_SEQ_NUM
FROM CASH_8300_3A K3A
UNION
SELECT 0 AS FINANCIAL_INST_BRANCH_SEQ_NUMB
FROM CTR_1A

Open in new window


I get the following error:
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

Why does TRIM fail the MERGE?  If it's job is to just remove leading zeros, I am not understanding the fail here.
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Jut like the last question:  TRIM returns a varchar2.

Change to:
NVL(to_number(TRIM(K3A.BSA_ID)), 0)
0
 
sdstuberCommented:
Trim removes leading a
nd trailing spaces
Which causes an implicit conversion from number to string
0
 
slightwv (䄆 Netminder) Commented:
>> If it's job is to just remove leading zeros

Not quite what it does.

From the docs:

TRIM enables you to trim leading or trailing characters (or both) from a character string

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions219.htm#SQLRF06149
0
 
johnsoneSenior Oracle DBACommented:
Also, what datatype is BSA_ID?  If it is a NUMBER, there is no need for the trim.
0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
AWESOME...
Thanks Much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.