Re: Help - "Business Day" Problem

On Apr 28, 3:26 am, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 27, 12:41 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
>
>
> > <sybra..._at_hccnet.nl> a écrit dans le message de news: ndj433pb0t46mim6siqoq1et3cuciai..._at_4ax.com...
> > | On Fri, 27 Apr 2007 18:30:27 +0200, "Michel Cadot"| <micadot{at}altern{dot}org> wrote:
>
> > |
> > | >
> > | >"sybrandb" <sybra..._at_gmail.com> a écrit dans le message de news: 1177681440.943124.58..._at_r30g2000prh.googlegroups.com...
> > | >| On Apr 27, 3:23 pm, "pankaj_wolfhun..._at_yahoo.co.in"
> > | >|
> > | >| 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
> > | >|
> > | >
> > | >Is this faster than:
> > | >
> > | >select 1 from emp where empno = :1 and rownum = 1;
> > | >
> > | >--------------------------------------------------------------------------­­---
> > | >| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> > | >--------------------------------------------------------------------------­­---
> > | >| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
> > | >|* 1 | COUNT STOPKEY | | | | | |
> > | >|* 2 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
> > | >--------------------------------------------------------------------------­­---
> > | >
> > | >Predicate Information (identified by operation id):
> > | >---------------------------------------------------
> > | >
> > | > 1 - filter(ROWNUM=1)
> > | > 2 - access("EMPNO"=TO_NUMBER(:1))
> > | >
> > | >Your query:
> > | >
> > | >--------------------------------------------------------------------------­­---
> > | >| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> > | >--------------------------------------------------------------------------­­---
> > | >| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
> > | >|* 1 | FILTER | | | | | |
> > | >| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
> > | >|* 3 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
> > | >--------------------------------------------------------------------------­­---
> > | >
> > | >Predicate Information (identified by operation id):
> > | >---------------------------------------------------
> > | >
> > | > 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE
> > | > "EMPNO"=TO_NUMBER(:1)))
> > | > 3 - access("EMPNO"=TO_NUMBER(:1))
> > | >
> > | >
> > | >Regards
> > | >Michel Cadot
> > | >
> > |
> > | I don't see much difference, I must say.
> > | But I was implicitly referring to the approach I see many times:
> > | select count(*)
> > | into dummy
> > | from emp
> > | where empno = :emp;
> > | if dummy > 0 then
> > | select ename into
> > | ...
> > | from emp
> > | where empno = : emp;
> > |
> > | I even saw this when the predicate wasn't a PK but a FK.
> > |
> > | --
> > | Sybrand Bakker
> > | Senior Oracle DBA
>
> > I didn't have any opinion, that was just an open remark.
> > I pretty understand your point. :)
>
> > Regards
> > Michel Cadot
>
> I think that "fast dual" indicates the answer to this may vary by
> version. Not to mention, vary by 1000 users doing it 10000 times in
> an update :-O
>
> jg
> --
> @home.com is bogus.
> "Now you're just screwing with my mind! While I was commenting on your
> last blog, you published a new blog about my blog, about ... I need to
> lie down! ;-)" - Doug Burns- Hide quoted text -
>
> - Show quoted text -

Thanks for all the replies.
Sybrand, I think I am not able to get your point

Query with dual will only give me the "existence" of the record.

SELECT 1
FROM dual
WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN') Enter value for 2: 28-APR-2007
old 3: WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN') new 3: WHERE TO_CHAR(TO_DATE ('28-APR-2007') - 1,'DY') NOT IN ('SAT','SUN')          1

         1

SELECT 1
FROM dual
WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN') Enter value for 2: 30-APR-2007
old 3: WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN') new 3: WHERE TO_CHAR(TO_DATE ('30-APR-2007') - 1,'DY') NOT IN ('SAT','SUN') no rows selected

What I want is the business date prev to the date provided. So if I enter 30-APR-2007, I should get 27-Apr-2007

Sorry if I am not able to get your point or If I was not clear with my requirement. Received on Sat Apr 28 2007 - 02:24:48 CDT