I have a query that does a left outer join and always gives me an
error, something like "invalid use of ! operator...".
After playing with variations of the same query I found that the WHERE
statement was the source of the problem.  The WHERE was of the type:
    WHERE (a=b) or (c=d)
I found that by adding something at the end it would work.  I changed
it to:
    WHERE (a=b) or (c=d) or (a=b)
and it now runs.  Which tells me the BDE is buggy!  Hope Borland will
fix it soon.
Luis

In article <41mg0o$...@news.isp.net> lur...@slip.net (Luis C. Uribe) writes:

I had alot of trouble with Jions and SQL. Try the shareware TQBE component.
You can build the .QBE file in Paradox, save it and read it into the SQL
property. It respects the ! operator for joins.

I found the latest version on Compu$erve.

BTW it takes parameters too.

Matthew McDermott
McDermott Enterprises

  [snip]

Yes - I've just been converting a pile of PdoxDOS code to Delphi (yawn)
and it works no problem.  Because of the non-SQL standard names allowed
in Pdox, you have to follow the local SQL naming rules carefully - plus
the error messages are useless.  Please find below examples of some of
the common Pdox features and Delphi/local SQL equivalents, and a
procedure 'doQuery' which I use for PAL conversions:

  Pdox feature: _collno! on "Collection No"
  Delphi LocalSQL equivalent LEFT OUTER JOIN:

  doQuery('SELECT a."Source", a."Collection No", a."Coin No",'+
          '  a."Amount", a."Source Name",'+
          '  b."Name" AS Collection_Name'+
          '  FROM ":work:answer.db" a '+
          '  LEFT OUTER JOIN ":covyear:specials" b' +
          '  ON a."Collection No" = b."No"');

  Pdox feature: Check on "Coin No", CALC SUM ALL on "Collected":
  Delphi LocalSQL equivalent:

  doQuery('SELECT w."Coin No", SUM(w."Collected") AS Number' +
          '  FROM ":covwork:cash" w' +
          '  GROUP BY w."Coin No" ');

  Pdox feature: Creating new blank columns with CALC 0 AS ...:
  Delphi LocalSQL equivalent:

  doQuery('SELECT p."No", p."Name", p."Qty in Bag",' +
          '  a."Number" * p."Value" AS Amount, ' +
          '  0 AS Full_Bags, 0 AS Remainder,' +
          '  a."Number"' +
          '  FROM ":covperm:coinage" p, ":work:answer" a' +
          '  WHERE p."No" = a."Coin No"');

  Note that LocalSQL will NOT allow:  0 AS "Non Standard SQL Name" ...

  procedure TWeeklyReportsForm.doQuery(QueryString: String);
  begin
    with MyQuery do begin
      Close;
      SQL.Clear;
      SQL.Add(QueryString);
      Open;
    end;
    with MyBatchMove do begin
      Source := MyQuery;            {Move to Answer}
      Destination := AnswerTable;
      Mode := batCopy;
      Mappings.Clear;
      Execute;
    end;
  end;

I'm sure there are many more examples - come on, Dr.Bob, there's an
article in it for you!

John

+---------------------+------------------------------------------------------+
 John P.McCreesh      |  Internet: jpmccre...@pines.demon.co.uk
 Forbo-Lancaster Ltd. |  X.400:    c=GB,a=CWMAIL,p=FORBO,g=John,s=McCreesh
 Lancaster, England   |  Voice:    +44 (0) 1524 65222  Fax: +44 (0) 1524 61638

ebin...@aol.com (EBinary)  wrote

Hi,
I recently had success with the following:

SELECT Section.etno, Spares.pg, Spares.pu, Spares.german
FROM Section s  LEFT OUTER JOIN Spares p ON s.etno=p.etno
WHERE section = :section;

Section is a table with a field 'etno' (parts ref. No). Spares is a table  
with 'etno' as the primary key and other descriptive fields for this part.
The 'section' field in the WHERE clause refers to a master table. The  
result of this query is a table of spare part refs and the descriptions  
with empty descriptions for the spare parts refs which do not occur in the  
'spares' table.

Michael
--

Michael Suedkamp                                      m...@suedkm.franken.de

The Interbase users guide gives a full explanation of Outer Joins
in SQL on pages 121 and 122. The default is an inner join.

--
Donald Oddy
Grove Systems Ltd.                                    0161-224 4465