problem with view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: problem with view

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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 e table2 seem correct
    what is the problem????

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Are you doing anything wrong?? Post view creation output and sample table/view data..

    Code:
    SQL> CREATE OR REPLACE VIEW anagr (
      2     cod, name)
      3  AS
      4     (SELECT    SUBSTR (a.object_id, 1, 2)
      5             || '-'
      6             || TO_CHAR (TO_NUMBER (SUBSTR (a.object_id, 3))) cod,
      7             a.object_name
      8        FROM user_objects a, all_objects b
      9       WHERE a.object_id(+) = b.object_id);
    
    View created.
    
    SQL> SELECT *
      2    FROM anagr ;
    
    25592 rows selected.
    
    SQL> SELECT *
      2    FROM anagr
      3   WHERE cod = '12-345678';
    
    no rows selected..
    SQL> DROP VIEW anagr;
    
    View dropped.
    
    SQL>
    Last edited by Sameer; 01-16-2003 at 11:22 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width