Home » SQL & PL/SQL » SQL & PL/SQL » ora 01722 invalid number error
ora 01722 invalid number error [message #611760] |
Tue, 08 April 2014 10:53 |
|
ashwanth77
Messages: 95 Registered: April 2013 Location: India
|
Member |
|
|
where i am going wrong
UPDATE CUSTOMER c SET(CITY,DESCRIPTION,STATE,POSTAL_CODE_1,
ADDRESS_1,GCDB_SOURCE_KEY,GCDB_SOURCE_KEY_ORG,
TRUE_GCDB_SOURCE_KEY) = (SELECT CITY,NAME,SUB_CNTRY,POSTL_CODE,STRET_ADDR_LINE_1,
--error at this place near -- to_number(CUST_ID),to_number(CUST_ID),CUST_ID FROM CUST_RDS CR
WHERE CR.CUST_ID=C.TRUE_GCDB_SOURCE_KEY)
where c.true_gcdb_source_key is not null and
c.true_gcdb_source_key in ( select cust_id from cust_rds);
table definition for customer
CREATE TABLE CUSTOMER
(
CLASS VARCHAR2(40 BYTE) DEFAULT 'TEMPORARY',
CREATED DATE DEFAULT sysdate NOT NULL,
CITY VARCHAR2(40 BYTE),
COUNTRY VARCHAR2(40 BYTE),
CUST_CUST_ID NUMBER(10),
DESCRIPTION VARCHAR2(40 BYTE),
STATE VARCHAR2(40 BYTE),
DS_DS_ID NUMBER(10) NOT NULL,
EXPIRES DATE,
POSTAL_CODE_1 VARCHAR2(20 BYTE),
POSTAL_CODE_2 VARCHAR2(20 BYTE),
ADDRESS_1 VARCHAR2(40 BYTE),
ADDRESS_2 VARCHAR2(40 BYTE),
SOURCE_KEY VARCHAR2(15 BYTE),
CUST_ID NUMBER(10) NOT NULL,
SECONDARY_SOURCE_KEY VARCHAR2(15 BYTE),
GCDB_SOURCE_KEY NUMBER(10),
GCDB_SOURCE_KEY_ORG NUMBER(10),
TRUE_GCDB_SOURCE_KEY VARCHAR2(20 BYTE)
)
table definition for cust_rds
CREATE TABLE CUST_RDS
(
CUST_ID VARCHAR2(15 BYTE) NOT NULL,
TRADE_CHANL_ID VARCHAR2(15 BYTE),
NAME VARCHAR2(30 BYTE) NOT NULL,
LONG_NAME VARCHAR2(60 BYTE),
LONG_DESC VARCHAR2(2000 BYTE),
STRET_ADDR_LINE_1 VARCHAR2(30 BYTE),
STRET_ADDR_LINE_2 VARCHAR2(30 BYTE),
CITY VARCHAR2(30 BYTE),
SUB_CNTRY VARCHAR2(30 BYTE),
POSTL_CODE VARCHAR2(10 BYTE),
ISO_CNTRY_NUM VARCHAR2(3 BYTE),
STTUS_CODE VARCHAR2(1 BYTE),
DELET_DATE DATE,
CUST_ACCT_GRP_CODE VARCHAR2(4 BYTE) NOT NULL,
TRNSP_ZONE VARCHAR2(30 BYTE)
)
|
|
|
|
|
Re: ora 01722 invalid number error [message #611768 is a reply to message #611760] |
Tue, 08 April 2014 13:35 |
|
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
And...The number of columns from the sub-query do not match the number of column to update
(unless you have TO_NUMBER ( Cust_Id) twice?):
UPDATE Customer C
SET
(
City
, Description
, State
, Postal_Code_1
, Address_1
, Gcdb_Source_Key
, Gcdb_Source_Key_Org
, True_Gcdb_Source_Key
) =
(SELECT
City
, Name
, Sub_Cntry
, Postl_Code
, Stret_Addr_Line_1
, TO_NUMBER ( Cust_Id)
, Cust_Id
FROM Cust_Rds Cr
WHERE Cr.Cust_Id = C.True_Gcdb_Source_Key)
WHERE C.True_Gcdb_Source_Key IS NOT NULL
AND C.True_Gcdb_Source_Key IN (SELECT Cust_Id FROM Cust_Rds);
[Updated on: Tue, 08 April 2014 14:34] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: ora 01722 invalid number error [message #611845 is a reply to message #611822] |
Wed, 09 April 2014 06:48 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
rc3d wrote on Wed, 09 April 2014 14:48
No need to learn SQL format rules. Toad (and other IDE) will do this automatically for you.
And that would be a very bad coding practise. Imagine a situation where multiple developers are working on a project, and every developer depends on tools like TOAD etc. for code formatting and indentation. Then the code standards might differ for each developer since they might not have similar settings. And let's say if the entire code when merged into a package, it would look horrible. So, as suggested it is always better to follow formatting rules.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 09:39:08 CDT 2024
|