need help in update statement

i have created a table with the system date as one of the column:

create table nav_date as (SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual)

------------------------------------------------------

how to get the current system date in the table whenever i open the table nav_date?

I mean the table should automatically update the date.

create a view
select sysdate as nav_date
from dual ?

Quote:

---

Originally Posted by ali560045

i have created a table with the system date as one of the column:

create table nav_date as (SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual)

------------------------------------------------------

how to get the current system date in the table whenever i open the table nav_date?

I mean the table should automatically update the date.

---

why dont you just query from dual?

when i m creating the table for the first time i m doing sysdate from dual only.

But when i open(select * from nav_date) the table say after 5 mins , it should show me the updated time and not the pevious stored time.

so not getting how to achive this?

again, instead of selecting from your table - why not just select from dual. What you are doing makes no sense

Quote:

---

Originally Posted by citldba

create a view
select sysdate as nav_date
from dual ?

---

like citldba said

you create a view

create view time_view as ( SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual);

it will get update.
In the case of table, it is created with the time while you firing the create query.so when you select from the table you will get the time already stored at the time of creation,again it wont go to select from the dual.

AGAIN - WHY?

whay create a view on dual - whats the point!

And why do you think it wont select from dual - wtf do you think the view is doing?

thanks a lot. i tink this view seems to help me for this time now.

thanks all of u for ur precious time

Quote:

---

Originally Posted by davey23uk

AGAIN - WHY?

whay create a view on dual - whats the point!

And why do you think it wont select from dual

---

dave,

when i tried it from the table it is not doing.But it is working fine in the view.
can you post the correct query for the table ??

[/QUOTE]
- wtf do you think the view is doing?[/QUOTE]

yes the view is doing

SQL> select * from time_view;

Current date
--------------------
24-JUN-2008 06:08:03

SQL> /

Current date
--------------------
24-JUN-2008 06:08:07

SQL> /

Current date
--------------------
24-JUN-2008 06:08:09

select sysdate from dual - will return the date

its up to the CLIENT to say how it wants it displayed

Quote:

---

Originally Posted by davey23uk

select sysdate from dual - will return the date

its up to the CLIENT to say how it wants it displayed

---

yes davey you are rite :), we will get the sysdate from the dual.

but the poster ali560045 needs the time to display it from the table he created, and whenever he select from the tabe he needs the updated time(current time).but it is happening only from the view.

SQL> select * from time_table;

Current date
--------------------
24-JUN-2008 03:04:29

SQL> select * from TIME_VIEW;

Current date
--------------------
25-JUN-2008 09:44:42

SQL> select * from TIME_VIEW;

Current date
--------------------
25-JUN-2008 09:44:51

If we are creating table with "select sysdate from dual" command. Table contains static data. Then if you query from your created table then you will get old date (when table was created and selected sysdate from dual).
If you are creating view then it is dynamic and will give you current date.

my god!

there is no need to create the table in the first place - do not do it - it is dumb, it serves no purpose, it solves no problem, just select straight from dual - its what it is ther for!

A view is of no use, do not do it, it is dumb, it serves no purpose, it solves no problem

you are rite davey:),but the poster requirement is something like that :).