SQL query

[Quoted] I am using a function (GET_CURRENT_DATE) to get the sysdate back from the database. I do this because the other application is in SYBASE which doesn't recognize sysdate.

The function is basically this statement.

Select sysdate INTO tmpVar from dual;

   RETURN tmpVar;

Anyway I get a MERGE_CARTESIAN JOIN when I try this query.

Select firstname, surname, p.fin FROM PARTICIPANTS P, LICENCE_PARTICIPANTS LP,
GEAR_TAG_ISSUANCES GTI, LICENCE_AREAS LA, AREAS A WHERE p.fin = lp.fin and
gti.licence_id = lp.licence_id and
lp.licence_id = la.licence_id and
la.area_id = a.area_id and a.area = '33' and 33010251 between gti.tag_start and
gti.tag_end and GET_CURRENT_YEAR = gti.LICENCE_YEAR and GET_CURRENT_DATE between lp.START_DATE  and lp.end_date ;

Operation

SELECT STATEMENT Optimizer Mode=CHOOSE
  TABLE ACCESS BY INDEX ROWID
    NESTED LOOPS

      NESTED LOOPS
        HASH JOIN
          MERGE JOIN CARTESIAN
            TABLE ACCESS BY INDEX ROWID
              INDEX RANGE SCAN
            BUFFER SORT
              TABLE ACCESS FULL
          INDEX FAST FULL SCAN
        TABLE ACCESS BY INDEX ROWID
          INDEX UNIQUE SCAN
      INDEX RANGE SCAN

But when I use sysdate in the last line, then no more CARTESIAN.

Select firstname, surname, p.fin FROM PARTICIPANTS P, LICENCE_PARTICIPANTS LP,
GEAR_TAG_ISSUANCES GTI, LICENCE_AREAS LA, AREAS A WHERE p.fin = lp.fin and
gti.licence_id = lp.licence_id and
lp.licence_id = la.licence_id and
la.area_id = a.area_id and a.area = '33' and 33010251 between gti.tag_start and
gti.tag_end and GET_CURRENT_YEAR = gti.LICENCE_YEAR and sysdate between lp.START_DATE
 and lp.end_date ;

Operation

SELECT STATEMENT Optimizer Mode=CHOOSE
  NESTED LOOPS
    HASH JOIN

      HASH JOIN
        HASH JOIN
          TABLE ACCESS BY INDEX ROWID
            INDEX RANGE SCAN
          INDEX FAST FULL SCAN
        TABLE ACCESS FULL
      TABLE ACCESS FULL
    TABLE ACCESS BY INDEX ROWID
      INDEX UNIQUE SCAN

Anybody know why this may happen? I've used the function successfully with other SQL statements and didn't have any problems. I'm using Oracle 9.2.0.7.0.

Thanks for any help.

Glenn Received on Fri Mar 17 2006 - 17:51:23 CET