Solved

Trimming a number forces failure on MERGE

Posted on 2011-09-27
5
359 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Evan Cutler
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36712870
Jut like the last question:  TRIM returns a varchar2.

Change to:
NVL(to_number(TRIM(K3A.BSA_ID)), 0)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36712878
Trim removes leading a
nd trailing spaces
Which causes an implicit conversion from number to string
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36712881
>> 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
 
LVL 34

Expert Comment

by:johnsone
ID: 36712893
Also, what datatype is BSA_ID?  If it is a NUMBER, there is no need for the trim.
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 36712894
AWESOME...
Thanks Much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

914 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now