Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: ORA-01722: invalid number...on view

    hi,
    i created this view:

    CREATE OR REPLACE VIEW TEST( UE,
    COD_IMM, DENOM_AZ, CODE_LE, LE,
    SUP_LE, SUP_TOT_IMM, TIP_CLI ) AS ( select substr(LS.LS_ID, 1, 2) UE,
    substr(LS.LS_ID, 3) COD_IMM,
    LS.NAME DENOM_AZ,
    DV.CODE_LE CODE_LE,
    DV.DV_ID LEGAL_ENTITY,
    ROUND(nvl(SUM(nvl(RM.AREA_CHARGABLE,0)),0),2),
    ROUND(LS.AREA_NEGOTIATED,2) SUP_TOT_IMM,
    DECODE (DV.BU_ID, 'TELECOM ITALIA S.P.A.', 'I', 'E') TIP_CLI
    FROM LS, RM, DV
    WHERE LS.LS_ID=RM.LS_ID
    AND RM.DV_ID=DV.DV_ID
    and TO_NUMBER(ls.USE1) > 6
    GROUP BY ls.LS_ID, DV.DV_ID, LS.NAME, DV.BU_ID, LS.AREA_NEGOTIATED, DV.CODE_LE)

    but when I run this query:
    select *
    from TEST;

    I get this error:
    ORA-01722: invalid number

    I believe that error is in "TO_NUMBER(ls.USE1) > 6"

    USE1 VARCHAR2(32)

    How can I resolve??

    Thanks
    Raf

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ORA-01722: invalid number...on view

    Very descriptive column names!!!!!!

    I think the problem (or one of them) is this line

    substr(LS.LS_ID, 3) COD_IMM,


    you need both starting and ending position.

    so you probably intended it to be

    substr(LS.LS_ID,1, 3) COD_IMM,

  3. #3
    Join Date
    Jul 2002
    Posts
    227

    Re: ORA-01722: invalid number...on view

    Originally posted by carloa
    Very descriptive column names!!!!!!

    I think the problem (or one of them) is this line

    substr(LS.LS_ID, 3) COD_IMM,


    you need both starting and ending position.

    so you probably intended it to be

    substr(LS.LS_ID,1, 3) COD_IMM,
    no....my problem is in TO_NUMBER(ls.USE1) > 6

    one value of col USE1 is TR not a number

    How can I resolve??

    Thanks
    Raf

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-01722: invalid number...on view

    You need to strip out any LS rows that do not contain numbers before you apply the TO_NUMBER. Assuming the numbers are simple positive integers, this would do it:

    CREATE OR REPLACE VIEW TEST( UE,
    COD_IMM, DENOM_AZ, CODE_LE, LE,
    SUP_LE, SUP_TOT_IMM, TIP_CLI )
    AS
    ( select substr(LS.LS_ID, 1, 2) UE,
    substr(LS.LS_ID, 3) COD_IMM,
    LS.NAME DENOM_AZ,
    DV.CODE_LE CODE_LE,
    DV.DV_ID LEGAL_ENTITY,
    ROUND(nvl(SUM(nvl(RM.AREA_CHARGABLE,0)),0),2),
    ROUND(LS.AREA_NEGOTIATED,2) SUP_TOT_IMM,
    DECODE (DV.BU_ID, 'TELECOM ITALIA S.P.A.', 'I', 'E') TIP_CLI
    FROM (SELECT LS_ID, NAME, AREA_NEGOTIATED FROM LS WHERE TRANSLATE(USE1,'x0123456789','x') IS NULL ) LS, RM, DV
    WHERE LS.LS_ID=RM.LS_ID
    AND RM.DV_ID=DV.DV_ID
    and TO_NUMBER(ls.USE1) > 6
    GROUP BY ls.LS_ID, DV.DV_ID, LS.NAME, DV.BU_ID, LS.AREA_NEGOTIATED, DV.CODE_LE
    )

    If you need to cater for minus sign and decimal point also, it gets more complicated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •