Home » SQL & PL/SQL » SQL & PL/SQL » Select statment required
Select statment required [message #223427] Thu, 08 March 2007 19:38 Go to next message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi All,

I am using Oracle database, i want a select SQL with some conditions.

my table name is wip_doc

my table columns are

doc_num, create_date,accept_date,cnt,print_cnt,descrip

my SQL condition is:

select doc_num, create_date,accept_date,cnt,print_cnt,descrip from wip_doc where (create_date between to_date('09-MAR-06') AND to_date('09-MAR-07')

i want to add one more column that is not in table, the column must be create by condition, the column name is "age"

if create_date is today's date then that age column record value is 0

if create_date is yesterday's dte then the age column record value is 1

like that i want the above SQL with Age column based upon the create_date column value.

so the SQL select will produce the all column values from database with condition column age and it's value for each row.

can any one please help me to generate SQL.

with regards,
anbaz.
Re: Select statment required [message #223444 is a reply to message #223427] Thu, 08 March 2007 23:54 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

just add
select
doc_num, create_date,accept_date,cnt,print_cnt,descrip ,(to_date(sysdate,'dd-mm-yy') -create_date ) as age
from wip_doc
where (create_date between to_date('09-MAR-06') AND to_date('09-MAR-07')
Re: Select statment required [message #223557 is a reply to message #223444] Fri, 09 March 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Both these posts are sloppy ways to do TO_DATE. If you NLS_DATE_FORMAT does not conform to the exact format you lay out here, these statements will fail. Anyone else trying your code will most likely have this statement fail.
Re: Select statment required [message #223559 is a reply to message #223557] Fri, 09 March 2007 08:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
in addition to joy's remarks:
why would you do a to_date on sysdate? Sysdate already is a date.
Previous Topic: Roles and Objects
Next Topic: ORA-06530: Reference to uninitialized collection
Goto Forum:
  


Current Time: Thu Dec 08 20:00:14 CST 2016

Total time taken to generate the page: 0.09297 seconds