Re: Help - "Business Day" Problem

On Apr 27, 3:23 pm, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> On Apr 27, 6:04 pm, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Apr 27, 2:58 pm, "pankaj_wolfhun..._at_yahoo.co.in"
>
> > <pankaj_wolfhun..._at_yahoo.co.in> 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