Re: Help - "Business Day" Problem

On Apr 27, 3:23 pm, "pankaj_wolfhun...@xxxxxxxxxxx"
<pankaj_wolfhun...@xxxxxxxxxxx> wrote:
On Apr 27, 6:04 pm, sybrandb <sybra...@xxxxxxxxx> wrote:





On Apr 27, 2:58 pm, "pankaj_wolfhun...@xxxxxxxxxxx"

<pankaj_wolfhun...@xxxxxxxxxxx> wrote:
Greetings,
I have a requirement where an input date will be provided and I
have to
update a table with the date previous to input_date (input_date - 1)

All I have to make sure is input_date - 1 should not be 'SAT' or 'SUN'
and should not be
in our holiday table.

Something like

UPDATE TABLE1
SET final_date =(SELECT input_date - 1
FROM ...
WHERE TO_CHAR(TO_DATE ('input_date') - 1,'DY') NOT IN
('SAT','SUN')
AND NOT EXISTS (SELECT h_holiday from holiday where h_holiday =
input_date)

My question do we need the FROM clause in the subquery? If yes, what
to specify as this is just an
input date and not from any table.

Any help would be appreciated.

TIA

a) Yes
b) this is what DUAL was invented for (OK: Originally it was used to
send printer specific codes to output as the PROMPT command in
sql*plus didn't yet exist)

--
Sybrand Bakker
Senior Oracle DBA- Hide quoted text -

- Show quoted text -

Thanks for the reply but I didnt get it.
Do you mean to say

SELECT input_date - 1
FROM dual
WHERE TO_CHAR(TO_DATE ('input_date') - 1,'DY') NOT IN ('SAT','SUN')
AND NOT EXISTS (SELECT h_holiday from holiday where h_holiday =
input_date)

will solve the purpose?- Hide quoted text -

- Show quoted text -

Exactly.
In fact the most efficient way to check for existence of a record is
select 1
from dual
where exists
(select 1
from emp where empno = :1)

Subqueries over DUAL: I love them!

--
Sybrand Bakker
Senior Oracle DBA

.

Relevant Pages

  • Re: Help - "Business Day" Problem
    ... in our holiday table. ... send printer specific codes to output as the PROMPT command in ... Sybrand Bakker ...
    (comp.databases.oracle.misc)
  • Re: Help - "Business Day" Problem
    ... in our holiday table. ... Sybrand Bakker ... Senior Oracle DBA- Hide quoted text - ... will solve the purpose? ...
    (comp.databases.oracle.misc)
  • Re: PLSQL: execute immediate call procedure
    ... Sybrand Bakker ... Senior Oracle DBA- Hide quoted text - ... I think Keith is on the very beginning of his pl/sql programming path. ...
    (comp.databases.oracle.misc)
  • Re: Forms 9.0.4.2
    ... Sybrand Bakker, Senior Oracle DBA ... Sounds like too much hard work (mind you, like you say, I am lazy). ... Let's focus on the original problem;-) ...
    (comp.databases.oracle.misc)
  • Re: Duplicate Database with RMAN
    ... Storage manager? ... Sybrand Bakker ... Senior Oracle DBA ... I did read the docs and I perform rman duplicates ...
    (comp.databases.oracle.server)
Quantcast