-
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
-
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 12:22 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|