Board index » delphi » SQL and the GROUP BY clause with local Paradox tables

SQL and the GROUP BY clause with local Paradox tables

Sami J El-Mahgary <selma...@cc.hut.fi> wrote in article
<5f3gjn$...@nntp.hut.fi>...

Quote
> Cc:

> Hello all,

> I'm using Delphi 1.0 and SQL to access local Paradox tables. When

> trying to execute an SQL statement that features both both a GROUP BY

> and an OR clause, I get an error message saying  'capability not
supported'.

> For instance the following query doesn't work.

> SELECT item_nr, order_date, amount_paid FROM Orders
> WHERE (item_nr=1 OR item_nr=2)
> GROUP BY order_date;

> However if the OR clause (OR item_nr=2) is removed the query works just
fine.

> That is, the GROUP BY clause does not seem to  work with OR clauses.

> Any ideas or work-arounds? Is this really a BDE limitation?

> Thanks a million,

> Sami El-Mahgary
> Systems Analysis Lab/U212
> Helsinki University of Technology
> 02150 Espoo, Finland

> email: mahg...@niksula.cs.hut.fi

Hi,

I am using Delphi 2, but use the GROUP BY clause with multiple conditions a
lot against Paradox tables.  I don't think there is a limitation in Delphi
1.

You do have to include all of your non-aggregate fields in the GROUP BY
clause.  Also, if you were intending to get the sum of amount_paid, you
will have to use the SUM function.  Try this:

SELECT item_nr, order_date, sum(amount_paid) TOTAL
FROM Orders
WHERE (item_nr=1 OR item_nr=2)
GROUP BY item_nr, order_date;

Good luck.

Regards,
Bruce

 

Re:SQL and the GROUP BY clause with local Paradox tables


Hi Bruce,
you seams to know about SQL-hints!
May I ask you for assitance??

Im hunting for following solution.

Delphi 1.02  / local SQL
State: DBF IV  Query for UDPATE one table with another
       (MDX is aval. on AiField and BiField)

  ex:    update Atable set Afield =
                   (select Bfield from Btable where AiField=BiField);
  ErrorMessage: feature not availabe
or
  ex:    update Atable set Afield = "X" where AiField =
                   (select Bfield from Btable where BiField = "X");
  ErrorMessage: feature not availabe: Nested select

The result is frustrating either excessed with
   as tString in with query1 do begin;close;EXECsql;end;  or
   with F8 in DBD.exe

I also tried in Delphi 2, here I didnt an error message
but if the answertable is type DB I get an Error after 5 mintues for a
1000 record sized table, that the ANSWER.DBF cant be found.
If I change ste setting to DBF the error means not to find the
ANSWER.DB.
The working-/private alias is either set to different paths or to the
same.

I also tryed this game with Paradox.db.

Further after copying dbfIII+ to Paradix.db I got accordingly
DBE-setting
a Version 5 DB, abfer adding 3 to 4 single field indexes addition to an
serial-field I was forced to convert to Version Paradox 7. No way back
an now Ive mixed tybeletype in one database? Causing this any probs?

Is Delphi simly ignorant or its me?
thanks for help from Klaus Gabriel Germany

pls. use CC: to Klaus.Gabr...@t-online.de or Klaus.Gabriel.metronet.de
or
                CEMA...@t-online.de

Re:SQL and the GROUP BY clause with local Paradox tables


Hi Bruce,
you seams to know about SQL-hints!
May I ask you for assitance??

Im hunting for following solution.

Delphi 1.02  / local SQL
State: DBF IV  Query for UDPATE one table with another
       (MDX is aval. on AiField and BiField)

  ex:    update Atable set Afield =
                   (select Bfield from Btable where AiField=BiField);
  ErrorMessage: feature not availabe
or
  ex:    update Atable set Afield = "X" where AiField =
                   (select Bfield from Btable where BiField = "X");
  ErrorMessage: feature not availabe: Nested select

The result is frustrating either excessed with
   as tString in with query1 do begin;close;EXECsql;end;  or
   with F8 in DBD.exe

I also tried in Delphi 2, here I didnt an error message
but if the answertable is type DB I get an Error after 5 mintues for a
1000 record sized table, that the ANSWER.DBF cant be found.
If I change ste setting to DBF the error means not to find the
ANSWER.DB.
The working-/private alias is either set to different paths or to the
same.

I also tryed this game with Paradox.db.

Further after copying dbfIII+ to Paradix.db I got accordingly
DBE-setting
a Version 5 DB, abfer adding 3 to 4 single field indexes addition to an
serial-field I was forced to convert to Version Paradox 7. No way back
an now Ive mixed tybeletype in one database? Causing this any probs?

Is Delphi simly ignorant or its me?
thanks for help from Klaus Gabriel Germany

pls. use CC: to Klaus.Gabr...@t-online.de or Klaus.Gabriel.metronet.de
or
                CEMA...@t-online.de

Re:SQL and the GROUP BY clause with local Paradox tables


Quote
> Sami J El-Mahgary <selma...@cc.hut.fi> wrote in article
> <5f3gjn$...@nntp.hut.fi>...

> > I'm using Delphi 1.0 and SQL to access local Paradox tables. When
> > trying to execute an SQL statement that features both both a GROUP BY

> > and an OR clause, I get an error message saying  'capability not
> > supported'.

> > For instance the following query doesn't work.

> > SELECT item_nr, order_date, amount_paid FROM Orders
> > WHERE (item_nr=1 OR item_nr=2)
> > GROUP BY order_date;

> > However if the OR clause (OR item_nr=2) is removed the query works just
> fine.

> > That is, the GROUP BY clause does not seem to  work with OR clauses.

        I don't know much about SQL, but I was wondering what are you trying to
        do with that GROUP BY clause in that kind of query... I thought
        it is only used when some calculations like SUM are made. If you
        were trying to sort the result table use ORDER BY clause.

-Timo

Other Threads