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 Go to next message
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 #611761 is a reply to message #611760] Tue, 08 April 2014 11:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CUST_RDS.CUST_ID contains values that aren't numbers so TO_NUMBER fails to convert these values into numbers. You'll have to fix it first, otherwise that UPDATE statement won't work.
Re: ora 01722 invalid number error [message #611762 is a reply to message #611760] Tue, 08 April 2014 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you should learn how to format a statement otherwise you won't ever write good SQL.

If you don't know how to do it, learn it using SQL Formatter.

Re: ora 01722 invalid number error [message #611768 is a reply to message #611760] Tue, 08 April 2014 13:35 Go to previous messageGo to next message
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 #611799 is a reply to message #611768] Wed, 09 April 2014 02:07 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
the no.of columns in subquery matches with main query and i have two to_number(cust_id)
Re: ora 01722 invalid number error [message #611801 is a reply to message #611799] Wed, 09 April 2014 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Tue, 08 April 2014 18:06
CUST_RDS.CUST_ID contains values that aren't numbers so TO_NUMBER fails to convert these values into numbers. You'll have to fix it first, otherwise that UPDATE statement won't work.

Re: ora 01722 invalid number error [message #611822 is a reply to message #611762] Wed, 09 April 2014 04:18 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Michel Cadot wrote on Tue, 08 April 2014 11:31

And you should learn how to format a statement otherwise you won't ever write good SQL.

If you don't know how to do it, learn it using SQL Formatter.



No need to learn SQL format rules. Toad (and other IDE) will do this automatically for you.
Re: ora 01722 invalid number error [message #611827 is a reply to message #611822] Wed, 09 April 2014 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No need to think there is someone that will do it for you on the web.

Re: ora 01722 invalid number error [message #611845 is a reply to message #611822] Wed, 09 April 2014 06:48 Go to previous message
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.
Previous Topic: Sort Join understanding
Next Topic: Oracle - generating combination of numbers
Goto Forum:
  


Current Time: Thu Mar 28 09:39:08 CDT 2024