OraFAQ Forum: SQL & PL/SQL » Data Function help

Home » SQL & PL/SQL » SQL & PL/SQL » Data Function help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data Function help [message #642152]

Fri, 04 September 2015 01:26

samyms
Messages: 18
Registered: May 2015
Location: Chennai

Junior Member

Requirment
• All the check approved until Friday should have subsequent Tuesday as Closed date.

• Any payments approved after Friday should have next Tuesday as Closed date.

Based on check_date(sysdate), for example

If check_date(sysdate) between 07-Sep-2015 to 11-Sep-2015 then Closed date =15-Sep-2015

if check_date(sysdate) 12-Sep-2015 to 18-Sep-2015 then closed date =22-Sep-2015

Query:
SELECT
CASE
WHEN TO_CHAR (SYSDATE+2, 'D') > 6
THEN
NEXT_DAY (SYSDATE + 7, 'TUE')
ELSE
NEXT_DAY (SYSDATE, 'TUE')
END
FROM DUAL

Please verify is this correct.

[Updated on: Fri, 04 September 2015 02:30]

Report message to a moderator

Re: Data Function help [message #642154 is a reply to message #642152]

Fri, 04 September 2015 02:03

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Well, your code runs:

orclz>
orclz> SELECT SYSDATE,
  2  NEXT_DAY (SYSDATE, 'TUE'), TO_CHAR (SYSDATE+1, 'DAY'),
  3  CASE
  4  WHEN trim(TO_CHAR (SYSDATE+1, 'DAY')) = 'SATURDAY'
  5  THEN
  6  NEXT_DAY (SYSDATE + 7, 'TUE')
  7  END
  8  FROM DUAL
  9  /

SYSDATE             NEXT_DAY(SYSDATE,'T TO_CHAR(SYSDATE+1,'DAY')             CASEWHENTRIM(TO_CHA
------------------- ------------------- ------------------------------------ -------------------
2015-09-04:08:00:45 2015-09-08:08:00:45 SATURDAY                             2015-09-15:08:00:45

orclz>

presumably you knew that already. I don't know if it does what you want. You need a proper test case: a table with a few rows.

Report message to a moderator

Re: Data Function help [message #642157 is a reply to message #642152]

Fri, 04 September 2015 02:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Also remind:

John Watson wrote on Sun, 10 May 2015 11:04

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
...

John Watson wrote on Thu, 30 July 2015 15:54

Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
You have been aske to do this before.
...

And

BlackSwan wrote on Fri, 22 May 2015 15:19

samyms wrote on Fri, 22 May 2015 04:33
got the ans

Please post it here so others may benefit from it.

If you refuse to help others posting the solution you get why should we still help someone with a so bad behaviour?

Report message to a moderator

Re: Data Function help [message #642324 is a reply to message #642152]

Tue, 08 September 2015 02:39

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

You can verify that yourself, Why don't you run it on your real data-set and check the results by yourself?

Report message to a moderator

Previous Topic:	PLSQL - providing values to Bind variables appearing multiple times in USING clause
Next Topic:	Different way of writing OR condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:51:43 CDT 2024