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

    Unanswered: problem with view

    Hi,
    I've a view that get data from 2 tables:
    CREATE OR REPLACE VIEW ANAGR(
    COD, NAME) AS (
    SELECT substr(COD_ID,1,2)||'-'||TO_CHAR(TO_NUMBER(SUBSTR(COD_ID,3))) COD,
    TABLE2.NAME
    FROM TABLE1, TABLE2
    WHERE TABLE1.COL = TABLE2.COL(+)
    )

    if I write
    select *
    from anagr
    I get data correctly, but if I write:

    select *
    from anagr
    where cod='12-345678'
    I get error 'ora-01722 invalid number'

    table1 and table2 seem correct
    what is the problem????

    Thanks
    Raf

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

    Re: problem with view

    Originally posted by raf
    Hi,
    I've a view that get data from 2 tables:
    CREATE OR REPLACE VIEW ANAGR(
    COD, NAME) AS (
    SELECT substr(COD_ID,1,2)||'-'||TO_CHAR(TO_NUMBER(SUBSTR(COD_ID,3))) COD,
    TABLE2.NAME
    FROM TABLE1, TABLE2
    WHERE TABLE1.COL = TABLE2.COL(+)
    )

    if I write
    select *
    from anagr
    I get data correctly, but if I write:

    select *
    from anagr
    where cod='12-345678'
    I get error 'ora-01722 invalid number'

    table1 and table2 seem correct
    what is the problem????

    Thanks
    Raf
    I can't replicate your problem, but then I am not sure of the datatype of COD_ID. I assumed it was a NUMBER.

    However, this bit is most odd:

    TO_CHAR(TO_NUMBER(SUBSTR(COD_ID,3)))

    - It gets COD_ID e.g. 12345678
    - Assuming COD_ID is a NUMBER, Oracle does an implicit TO_CHAR because SUBSTR works on character strings not numbers => '12345678'
    - It takes SUBSTR from 3 on => '345678'
    - It converts it back to a NUMBER => 345678
    - It converts it back to a string => '345678'

    What is the point of the last 2 steps?

    If COD_ID is a VARCHAR2 then a COD_ID value like '123X5678' would cause the TO_NUMBER above to fail. But it would fail even for the SELECT without the WHERE clause!

Posting Permissions

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