Board index » delphi » (Help me Team B) illegal outer-join request

(Help me Team B) illegal outer-join request

I have some complex code that I am trying to pass to SQL Server.  When
I pass it through Delphi Query Object I get the error 'illegal
outer-join request' and it fails.

If I copy the same SQL and put it into ISQL/w it works like a charm.

Anybody have any clues?  I could use the help.  I am running with BDE
5.10 and Sql Links.

Thanks

SELECT
F.EMH_CODE,PSDETAIL.PSD_DATE,D.CHH_CODE,D.CHH_FLD1,D.CHH_DESC,SUM(PSDETAIL.PSD_MIN)
AS PSD_MIN,E.PRJ_FILE,E.PRJ_DESC,G.RES_CODE,F.EMH_FLD1,C.PSH_WEDATE
 FROM PSDETAIL INNER JOIN PSLINES B ON PSDETAIL.PSD_PSL = B.PSL_KEY
 INNER JOIN PSHEADER C ON B.PSL_PSH = C.PSH_KEY
 INNER JOIN EMPHIS F ON C.PSH_EMH = F.EMH_KEY
 INNER JOIN CHRHIS D ON B.PSL_CHH = D.CHH_KEY  
 LEFT JOIN TCPROJ E ON B.PSL_PRJ = E.PRJ_KEY
 LEFT JOIN TCRES G ON F.EMH_RES = G.RES_KEY
 WHERE PSDETAIL.PSD_KEY NOT IN (SELECT BCD_PSD FROM BCDETAIL,BCHEADER
WHERE BCD_BCH=BCH_KEY AND (BCH_IFH=45))
 GROUP BY
F.EMH_CODE,PSDETAIL.PSD_DATE,D.CHH_CODE,D.CHH_FLD1,D.CHH_DESC,E.PRJ_FILE,E.PRJ_DESC,G.RES_CODE,F.EMH_FLD1,C.PSH_WEDATE

 ORDER BY
F.EMH_CODE,PSDETAIL.PSD_DATE,D.CHH_CODE,D.CHH_FLD1,D.CHH_DESC,E.PRJ_FILE,E.PRJ_DESC,G.RES_CODE,F.EMH_FLD1,C.PSH_WEDATE

 

Re:(Help me Team B) illegal outer-join request


Quote
Stephen Eyton-Jones wrote in message

<38307d7b.26856...@forums.inprise.com>...

Quote

>I have some complex code that I am trying to pass to SQL Server.  When
>I pass it through Delphi Query Object I get the error 'illegal
>outer-join request' and it fails.

>If I copy the same SQL and put it into ISQL/w it works like a charm.

If it works fine in ISQL then the likely culprit is the SQLQRYMODE setting
of the BDE alias - make sure it is set to Server otherwise the BDE will
attempt to parse the query and may fail even though the server accepts it.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Toronto Delphi Users Group - http://www.tdug.com/

Re:(Help me Team B) illegal outer-join request


Quote

>If it works fine in ISQL then the likely culprit is the SQLQRYMODE setting
>of the BDE alias - make sure it is set to Server otherwise the BDE will
>attempt to parse the query and may fail even though the server accepts it.

Thanks for the response.  Can you tell me what the impact is of
setting this.  From what I read, if I set this the Query is processed
only by the Server, and if it cannot be then I get no result.  Can you
offer a little more help on when this setting is NOT appropriate.

Thanks

Steve

Re:(Help me Team B) illegal outer-join request


Quote
Stephen Eyton-Jones wrote in message

<38355552.142158...@forums.inprise.com>...

Quote

>>If it works fine in ISQL then the likely culprit is the SQLQRYMODE setting
>>of the BDE alias - make sure it is set to Server otherwise the BDE will
>>attempt to parse the query and may fail even though the server accepts it.

>Thanks for the response.  Can you tell me what the impact is of
>setting this.  From what I read, if I set this the Query is processed
>only by the Server, and if it cannot be then I get no result.  Can you
>offer a little more help on when this setting is NOT appropriate.

It is always appropriate if you are using a real SQL backend like Interbase,
Oracle, etc - you want that server to get your SQL without a middle man
messing with it. If you are trying to execute SQL against something like
Paradox, then the BDE *is* the server and so leaving it blank or setting it
to Local is appropriate.

Note that in the case of RequestLive queries, the BDE will *still* attempt
to parse your SQL, but that is typically ok because any query that would be
allowed to be live will probably be acceptable to the BDE as well.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Toronto Delphi Users Group - http://www.tdug.com/

Re:(Help me Team B) illegal outer-join request


Quote

>It is always appropriate if you are using a real SQL backend like Interbase,
>Oracle, etc - you want that server to get your SQL without a middle man
>messing with it. If you are trying to execute SQL against something like
>Paradox, then the BDE *is* the server and so leaving it blank or setting it
>to Local is appropriate.

>Note that in the case of RequestLive queries, the BDE will *still* attempt
>to parse your SQL, but that is typically ok because any query that would be
>allowed to be live will probably be acceptable to the BDE as well.

>--
>Wayne Niddery - WinWright Consulting
>RADBooks - http://members.home.net/wniddery/
>Toronto Delphi Users Group - http://www.tdug.com/

Thanks for the response.  I think I get it!

Steve

Other Threads