Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help - "Business Day" Problem
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? Received on Fri Apr 27 2007 - 08:23:27 CDT