Home » SQL & PL/SQL » SQL & PL/SQL » auto increment column on view or materialized view.
auto increment column on view or materialized view. [message #191425] Wed, 06 September 2006 05:47 Go to next message
zanna__82
Messages: 1
Registered: September 2006
Junior Member
Hi, I have created a view on a spatial join between two tables and I want to add to this view a column, that I will use for id, that enumerates each tuple of my view. I don't know if it is possible, I have tried also with materialized view but oracle says me that sequence is not supported in materialized view. Any idea?
Therefore I would have something like that:

View schema:

VIEW_ID | RIVERS_ID | RIVERS_LENGTH |...| STREETS_ID | ....|..


View instance:

1 | ... | ..... |...|........
2 | .....
3 | ...


Thanks a lot for your help.

Best regards
Re: auto increment column on view or materialized view. [message #191704 is a reply to message #191425] Thu, 07 September 2006 17:29 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

What do You need the id for?
Could You perhaps use the rowid column from one of the tables instead?
Have You primary keys on the existing tables?

If not, it sounds like You have to create a third table, or maybe just a column on one of Your existing tables, where You maintain the id number. This (table/column) You then add to Your view.
-Not the best solution, but perhaps You do not need this after all?


Br
Kim
Re: auto increment column on view or materialized view. [message #192046 is a reply to message #191425] Sun, 10 September 2006 08:43 Go to previous message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
What is your tuple? It is unclear without SQL statement of the view.

Most likely you simply need to use ROWNUM instead of sequence.


Previous Topic: Changing backend of Crystal from SQL Server to Oracle
Next Topic: Regarding Stored procedure
Goto Forum:
  


Current Time: Tue Dec 03 05:42:06 CST 2024