| Subject | RE: [IBO] SQLWhere Items ??? |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-04-27T03:06:46Z |
At 12:44 PM 27/04/2003 +1000, you wrote:
In response to this and the previous message, the parser obviously isn't
going to the ORDER BY level of its sequence. That is the final level -
where it checks to see whether SQLOrder has any items and, if so, it
constructs and ORDER BY clause from that and adds it *after* the WHERE
clause. By this level, it already has the WHERE clause. For some reason
(not yet apparent....) it seems to be *reading* the SQLOrder structure
before it has finished the WHERE level (and not interpreting it as it should).
On the server side, you should *certainly* get an UNKNOWN TOKEN exception
from SUBMITDATE in the statement above.
SQLOrder should be tested for content and, if Count > 0, should be cleared
before applying the new values. If you don't, you will get an
exception; but you shouldn't be seeing what you report here under any
conditions. And DESC is a perfectly valid element of an SQLOrder string.
SQLOrder is a bit different to SQLWhereItems, where it's possible to ADD
more WHERE criteria.
What versions of
a) database
b) IBO
are you using?
Helen
>If I remove DESCAlan,
>it uses this statement with no error?? Why not object to that SUBMITDATE
>token in that position?
>SELECT ID, F_PADLEFT(LINKTOMEMBER,'0',6) LINKTOMEMBER, JOBTITLE,
>SHORTDESCRIPTION, SUBMITDATE, EMPTIME, EMPSTATUS, ADREF,
>(SELECT TITLE FROM LOCATION WHERE ADVERTISEMENTS.LINKTOLOCATION=LOCATION.ID)
>AS LOCATION,
>(SELECT TITLE FROM ROLECATEGORIES WHERE
>ADVERTISEMENTS.LINKTOROLECATEGORY=ROLECATEGORIES.ID) AS CATROLE,
>(SELECT ADTEMPLATE FROM MEMBERS WHERE
>ADVERTISEMENTS.LINKTOMEMBER=MEMBERS.MEMNO) AS ADTEMPLATE,
>(SELECT USECUSTOM FROM MEMBERS WHERE
>ADVERTISEMENTS.LINKTOMEMBER=MEMBERS.MEMNO) AS USECUSTOM,
>f_dayofmonth(SUBMITDATE)||'.'||f_month(SUBMITDATE)||'.'||f_year(SUBMITDATE)
>MYSUBMITDATE
>FROM ADVERTISEMENTS
>SUBMITDATE
>WHERE (
> (
> (
> (ADVERTISEMENTS.ADACTIVE = -1)
> AND (ADVERTISEMENTS.EXPIRY > 'NOW')
> AND LINKTOLOCATION IN (3)
> )
> AND LINKTOROLECATEGORY IN (1)
> )
> AND EMPTIME = 'part-time'
> )
> AND EMPSTATUS = 'contract'
In response to this and the previous message, the parser obviously isn't
going to the ORDER BY level of its sequence. That is the final level -
where it checks to see whether SQLOrder has any items and, if so, it
constructs and ORDER BY clause from that and adds it *after* the WHERE
clause. By this level, it already has the WHERE clause. For some reason
(not yet apparent....) it seems to be *reading* the SQLOrder structure
before it has finished the WHERE level (and not interpreting it as it should).
On the server side, you should *certainly* get an UNKNOWN TOKEN exception
from SUBMITDATE in the statement above.
SQLOrder should be tested for content and, if Count > 0, should be cleared
before applying the new values. If you don't, you will get an
exception; but you shouldn't be seeing what you report here under any
conditions. And DESC is a perfectly valid element of an SQLOrder string.
SQLOrder is a bit different to SQLWhereItems, where it's possible to ADD
more WHERE criteria.
What versions of
a) database
b) IBO
are you using?
Helen