Home » Other » Training & Certification » Need Help with an Expression ( Oracle SQL, Oracle 10g Express Edition)
Need Help with an Expression [message #364286] Sun, 07 December 2008 19:47 Go to next message
janders
Messages: 1
Registered: December 2008
Junior Member
Hello,
can you help me with the following problem?

Update the due date in table book_trans:
Write one UPDATE statement using an expression with the Decode function and the to_Char function?

ERD diagramm:
Table books:
ISBN (Primary KEY)
Title
No_of_copies
Author

Table Book_Trans:
Book_Trans_ID (Primary KEY)
ISBN (Foreign KEY)
employee_id (Foreign_key)
checkout_dte
due_dte
return_date
pastdue_fees

Table employees:
employee_id (PK)
last_name
first_name...

Business Rule for due date: The due date is 30 days from the checkout_dte. If due_dte falls on a saturday or sunday then the loan period is 32 days. To test for a weekday, use the to_char function with the format of 'D'. For example to_char ('12-NOV-2007', 'D'). This function returns a number between 1 and 7 (1=Sunday, 2=Monday,...)
Thanks
Re: Need Help with an Expression [message #364290 is a reply to message #364286] Sun, 07 December 2008 20:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
We don't just do your homework for you here. You need to post what you have tried yourself and what error or results you got. Please read our forum guidelines for how to post a proper question:

http://www.orafaq.com/forum/t/88153/0/
Re: Need Help with an Expression [message #364313 is a reply to message #364286] Sun, 07 December 2008 23:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell your teacher the examples he gave are not correct:
Quote:
To test for a weekday, use the to_char function with the format of 'D'. For example to_char ('12-NOV-2007', 'D').

SQL> select to_char ('12-NOV-2007', 'D') from dual;
select to_char ('12-NOV-2007', 'D') from dual
                *
ERROR at line 1:
ORA-01722: invalid number

Quote:
This function returns a number between 1 and 7 (1=Sunday, 2=Monday,...)

SQL> select to_char(sysdate-1,'Day') "Day", to_char(sysdate-1,'D') "Num" from dual;
Day       N
--------- -
Sunday    7

1 row selected.

See TO_CHAR function.

Regards
Michel

[Updated on: Sun, 07 December 2008 23:35]

Report message to a moderator

Previous Topic: Triggerzzzz (like a tiger)
Next Topic: number(p,s) doubt
Goto Forum:
  


Current Time: Sun Dec 04 16:30:34 CST 2016

Total time taken to generate the page: 0.04190 seconds