Re: Disappearing 'where' clause - On behalf of wayne ivory

[Mark Boyd <mark@CG4.COM>]

I don't know if this has anything to do with this, but here is a thought.  In
DB2 (MVS) you have to use either the forward (/) or backward (\) slashes (I
can't remember exactly), instead of a bracket [].  Maybe Informix requires a
different dynamic variable character?  I don't know Informix, so this may be a
long shot.

-Mark


Pietersz Mark x8938 wrote:

> > -----Original Message-----
> > From: Ivory, Wayne
> > Sent: Tuesday, 25 May 1999 14:24
> > To:   'SQR-USERS@list.iex.net'
> > Subject:      Disappearing 'where' clause
> >
> > Informix 7
> > SQR 3
> > Win95
> >
> >
> > I have written an sql-select that includes a dynamic part
> > [$DeptIdCriteria] in the 'where' clause.  Somehow the presence of the
> > square brackets appears to be making the *entire* where criteria disappear
> > as far as the compiler is concerned.
> >
> > begin-SELECT on-error=SQL-Error-Procedure
> > P.Position_Nbr
> > I.Emplid
> > E.Business_Title
> > P.Descr
> > P.Company
> > E.Reports_To
> > P.Reports_To
> > P.Deptid
> > D.Name
> > E.Phone
> >
> >     (various logic)
> >
> > From PS_POSITION_DATA P,
> >      OUTER (PS_POSN_INCUMBENT I,
> >      PS_EMPLOYMENT E,
> >      PS_PERSONAL_DATA D)
> > Where P.Posn_Status='A'
> > And P.Effdt=(
> >         Select Max(Effdt)
> >         From PS_POSITION_DATA P2
> >         Where P2.Position_Nbr=P.Position_Nbr
> >         And P2.Posn_Status='A'
> >         And P2.Effdt<=$Report_Date)
> > And I.Position_Nbr=P.Position_Nbr
> > And E.Emplid=I.Emplid
> > And D.Emplid=I.Emplid
> > [$DeptIdCriteria]
> > end-select
> >
> >
> > If I run it the sqr.log file contains the following:
> >
> > (SQR 5528) INFORMIX SQL PREPARE/DECLARE error -395 in cursor 4:
> >    The where clause contains an outer Cartesian Product.
> >
> > SQL: select P.Position_Nbr, I.Emplid, E.Business_Title, P.Descr,
> > P.Company,
> >      E.Reports_To, P.Reports_To, P.Deptid, D.Name, E.Phone  From
> >      PS_POSITION_DATA P, OUTER (PS_POSN_INCUMBENT I, PS_EMPLOYMENT E,
> >      PS_PERSONAL_DATA D)
> >
> > Notice how *none* of the 'where' criteria appears in the SQL statement.
> > Informix tools also show the query being executed without 'where'
> > criteria.  This naturally produces the outer Cartesian Product error
> > because no joins are happening.
> >
> > For testing purposes I coded the following before my begin-select:
> >
> > let $DeptIdCriteria = 'and 1=1'
> >
> > but still got the same result.  Yet if I hard code the DeptId criteria the
> > program runs.
> >
> > Ideas?
> >
> > Wayne Ivory
> > Information Services
> >
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This email message has been swept for the presence of computer viruses.
>
> CitiPower Pty ACN 064 651 056
> **********************************************************************