Board index » delphi » SQL Catch 22 - GROUP BY error

SQL Catch 22 - GROUP BY error

Hi,

This is the SQL i use for a search:

    'SELECT DISTINCT D.Field1ID, D.Field2ID, D.Field3, D.blobfield,
AVG(D.Field4) '
    + 'FROM "First.DB" D, "Second.db" d1 '
    + 'WHERE '
    + '(d1.Field1ID = D.Field1ID) and (Field3ID IN (%s)) '
    + 'Group BY D.Field1ID, D.Field2ID, D.Field3 ';  

This worked fine until i added the blobfield

now i get the error:

'When GROUP BY exists, every simple filed in projectors must be in GROUP BY

I tried adding D.blobfield to GROUP BY and it came up with the error

'Blob fields cannot by used in GROUP BY'

Is there any solution to this other than moving the blob out to a secondary
table and displaying it with a lookup field?

Thanks

William
NO_SPAM.the_white_ho...@usa.net
(Remove NO_SPAM from my address when e-mailing)

 

Re:SQL Catch 22 - GROUP BY error


Hi William,
because of GROUP BY clause, there will be
no more than ONE row for each
D.Field1ID, D.Field2ID, D.Field3  combination in the result
although some of these combinations could occur more than once
in the original table. To include a field that is not used in
GROUP BY clause, SQL requires to apply some
aggregate function on this field that convert multiple
values into just one: MAX, AVG, SUM etc.
What aggregate function would you suggest
on a blob field? I cannot figure out any
feasible function. Also, GROUP BY on a blob field
is clear nonsense, I agree. Therefore I cannot guess what
you are expecting to get in the D.blobfield of the query result.
Try to answer this question and you will probably find out that
D.blobfield should be left out.
--
Roman
(please remove STOPSPAM. in header)
URL:  www.rksolution.cz (Delphi corner)
MAIL: I...@rksolution.cz
Quote
William wrote in message <01bdc180$48b79f50$51716bc0@ssspci>...
>Hi,

>This is the SQL i use for a search:

>    'SELECT DISTINCT D.Field1ID, D.Field2ID, D.Field3, D.blobfield,
>AVG(D.Field4) '
>    + 'FROM "First.DB" D, "Second.db" d1 '
>    + 'WHERE '
>    + '(d1.Field1ID = D.Field1ID) and (Field3ID IN (%s)) '
>    + 'Group BY D.Field1ID, D.Field2ID, D.Field3 ';

>This worked fine until i added the blobfield

>now i get the error:

>'When GROUP BY exists, every simple filed in projectors must be in GROUP BY

>I tried adding D.blobfield to GROUP BY and it came up with the error

>'Blob fields cannot by used in GROUP BY'

>Is there any solution to this other than moving the blob out to a secondary
>table and displaying it with a lookup field?

>Thanks

>William
>NO_SPAM.the_white_ho...@usa.net
>(Remove NO_SPAM from my address when e-mailing)

Re:SQL Catch 22 - GROUP BY error


Actually, Roman, I have used SQL in another product and it allowed GROUP BY
without grouping on every single non-aggregate field.  Also, there is no
conceptual reason I can see why this should be so, and it is a minor PITA in
Delphi local SQL.

For example:

SELECT
invoice.customer_id,
sum(invoice.amount) as total_sales
trim(customer.firstname) + ' ' + trim(customer.lastname) as cust_name
FROM
invoice, customer
WHERE
customer.id = invoice.customer_id
GROUP BY
invoice.customer.id

There's absolutely no reason why I should have to also include the "baggage"
fields firstname and lastname in the group-by, since the customer.id uniquely
identifies them.

Another thing:  Delphi local SQL won't let me use an AS field name, I have to
repeate the whole darn expression, so in the example above, I have to have:

GROUP BY
invoice.customer.id,
trim(customer.firstname) + ' ' + trim(customer.lastname)

instead of

GROUP BY
invoice.customer.id,
cust_name

or even better

GROUP BY
invoice.customer.id

Not a huge problem or anything, just a minor annoyance....

Quote
Roman Krejci wrote:
> Hi William,
> because of GROUP BY clause, there will be
> no more than ONE row for each
> D.Field1ID, D.Field2ID, D.Field3  combination in the result
> although some of these combinations could occur more than once
> in the original table. To include a field that is not used in
> GROUP BY clause, SQL requires to apply some
> aggregate function on this field that convert multiple
> values into just one: MAX, AVG, SUM etc.
> What aggregate function would you suggest
> on a blob field? I cannot figure out any
> feasible function. Also, GROUP BY on a blob field
> is clear nonsense, I agree. Therefore I cannot guess what
> you are expecting to get in the D.blobfield of the query result.
> Try to answer this question and you will probably find out that
> D.blobfield should be left out.
> --
> Roman
> (please remove STOPSPAM. in header)
> URL:  www.rksolution.cz (Delphi corner)
> MAIL: I...@rksolution.cz

> William wrote in message <01bdc180$48b79f50$51716bc0@ssspci>...
> >Hi,

> >This is the SQL i use for a search:

> >    'SELECT DISTINCT D.Field1ID, D.Field2ID, D.Field3, D.blobfield,
> >AVG(D.Field4) '
> >    + 'FROM "First.DB" D, "Second.db" d1 '
> >    + 'WHERE '
> >    + '(d1.Field1ID = D.Field1ID) and (Field3ID IN (%s)) '
> >    + 'Group BY D.Field1ID, D.Field2ID, D.Field3 ';

> >This worked fine until i added the blobfield

> >now i get the error:

> >'When GROUP BY exists, every simple filed in projectors must be in GROUP BY

> >I tried adding D.blobfield to GROUP BY and it came up with the error

> >'Blob fields cannot by used in GROUP BY'

> >Is there any solution to this other than moving the blob out to a secondary
> >table and displaying it with a lookup field?

> >Thanks

> >William
> >NO_SPAM.the_white_ho...@usa.net
> >(Remove NO_SPAM from my address when e-mailing)

Re:SQL Catch 22 - GROUP BY error


Quote
>Is there any solution to this other than moving the blob out to a secondary
>table and displaying it with a lookup field?

No
--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:SQL Catch 22 - GROUP BY error


Dear Roman

Thanks for your well explained answers.

The reason i need to include the blob field is because i have set up my
dbgrid to display the contents of the blob field as a BMP within the grid
so if the D.blobfield is not used then the dbgrid complains it has no data
for field.

It works fine when i use 'select * from first.db', but that is no good for
searching.

Is there any way i can get the sql search to also return the blob
informaiton?

(e.g. I tried using a separate table and having the sql search return the
BlobID, only to find out too late that calculated and lookup fields cannot
be used for blobfields -'Field Blob cannot be a calculated or lookup field'
is the exact error)

Unendingly grateful for any solutions

Thanks

William

Roman Krejci <STOPSPAM.KRE...@MBOX.CESNET.CZ> wrote in article
<6qf2ao$i...@forums.borland.com>...

Quote
> Hi William,
> because of GROUP BY clause, there will be
> no more than ONE row for each
> D.Field1ID, D.Field2ID, D.Field3  combination in the result
> although some of these combinations could occur more than once
> in the original table. To include a field that is not used in
> GROUP BY clause, SQL requires to apply some
> aggregate function on this field that convert multiple
> values into just one: MAX, AVG, SUM etc.
> What aggregate function would you suggest
> on a blob field? I cannot figure out any
> feasible function. Also, GROUP BY on a blob field
> is clear nonsense, I agree. Therefore I cannot guess what
> you are expecting to get in the D.blobfield of the query result.
> Try to answer this question and you will probably find out that
> D.blobfield should be left out.
> --
> Roman
> (please remove STOPSPAM. in header)
> URL:  www.rksolution.cz (Delphi corner)
> MAIL: I...@rksolution.cz

> William wrote in message <01bdc180$48b79f50$51716bc0@ssspci>...
> >Hi,

> >This is the SQL i use for a search:

> >    'SELECT DISTINCT D.Field1ID, D.Field2ID, D.Field3, D.blobfield,
> >AVG(D.Field4) '
> >    + 'FROM "First.DB" D, "Second.db" d1 '
> >    + 'WHERE '
> >    + '(d1.Field1ID = D.Field1ID) and (Field3ID IN (%s)) '
> >    + 'Group BY D.Field1ID, D.Field2ID, D.Field3 ';

> >This worked fine until i added the blobfield

> >now i get the error:

> >'When GROUP BY exists, every simple filed in projectors must be in GROUP
BY

> >I tried adding D.blobfield to GROUP BY and it came up with the error

> >'Blob fields cannot by used in GROUP BY'

> >Is there any solution to this other than moving the blob out to a
secondary
> >table and displaying it with a lookup field?

> >Thanks

> >William
> >NO_SPAM.the_white_ho...@usa.net
> >(Remove NO_SPAM from my address when e-mailing)

Re:SQL Catch 22 - GROUP BY error


Is it just me or can look up fields not be BLOB fields?  Whenever i insert
the look up field and set active to true i get:

'Field Blob cannot be a calculated or lookup field'

William

Brian Bushay TeamB <BBus...@DataGuidance.com> wrote in article
<35db76bd.21232...@forums.borland.com>...

Quote
> >Is there any solution to this other than moving the blob out to a
secondary
> >table and displaying it with a lookup field?

> No
> --
> Brian Bushay (TeamB)
> Bbus...@DataGuidance.com

Re:SQL Catch 22 - GROUP BY error


Quote
Richard Grossman wrote in message <35CB830A.23F80...@techIII.com>...
>.......
>SELECT
>invoice.customer_id,
>sum(invoice.amount) as total_sales
>trim(customer.firstname) + ' ' + trim(customer.lastname) as cust_name
>FROM
>invoice, customer
>WHERE
>customer.id = invoice.customer_id
>GROUP BY
>invoice.customer.id
>......

Yes, Richard. In the above example I can clearly see that
there is a reasonable solution for SQL to convert
multiple Customer name occurence into just one value -
all occurences do have the same value besause the
customer table is "joined" through id - I agree that
such conclusion is behind local SQL capabilities. I must allways
use MAX(customer.name) aggregation in my programs.
In the William's example, however, it is not at all obvious
that all blobs values should be the same for each
particular D.Field1ID, D.Field2ID, D.Field3  combination-
blob field comes from D table as the grouped fields do.
--
Roman
(please remove STOPSPAM. in header)
URL:  www.rksolution.cz (Delphi corner)
MAIL: I...@rksolution.cz

Re:SQL Catch 22 - GROUP BY error


Quote
>Is it just me or can look up fields not be BLOB fields?  Whenever i insert
>the look up field and set active to true i get:

correct.  Blobs can not be lookup fields

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:SQL Catch 22 - GROUP BY error


On Fri, 07 Aug 1998 15:43:23 -0700, Richard Grossman

Quote
<rgross...@techIII.com> wrote:
>Actually, Roman, I have used SQL in another product and it allowed GROUP BY
>without grouping on every single non-aggregate field.  Also, there is no
>conceptual reason I can see why this should be so, and it is a minor PITA in
>Delphi local SQL.

As far as I have seen, this is standard SQL. For a column to be allowed in
the SELECT clause, it must either be the subject of an aggregate function
or also be listed in the GROUP BY clause. And -- to me -- this makes a lot
of sense. Take the dataset below, for example:

  State  Name              Amount
  -----  ----              ------
  CA     Stacey Green       99.01
  CA     Mike Brown        100.23
  CA     Lila Prose         45.99
  CA     Richard Grossman   50.95

Assume an SQL statement like this:

  SELECT N.State, SUM(N.Amount)
  FROM Names N
  GROUP BY N.State
  ORDER BY N.State

If you execute an aggregating query, summing the Amount column and grouping
on State, the above four records would be combined into one aggregated
record for the State column value "CA", because all four records share this
group value. The values in the Amount column would be added together to
derive a single value in this one record. But what of the values in the
Name column? Which name (Stacey, Mike, Lila, or Richard) gets included in
the result set? Unless you use one of the aggregate functions like MIN or
MAX, there is no realistic means by which to determine this.

Quote
>For example:

>SELECT
>invoice.customer_id,
>sum(invoice.amount) as total_sales
>trim(customer.firstname) + ' ' + trim(customer.lastname) as cust_name
>FROM
>invoice, customer
>WHERE
>customer.id = invoice.customer_id
>GROUP BY
>invoice.customer.id

>There's absolutely no reason why I should have to also include the "baggage"
>fields firstname and lastname in the group-by, since the customer.id uniquely
>identifies them.

This is a slightly different case, where there is a direct one-for-one
correlation between a given Customer_ID and the associated name. For any
single Customer_ID, there will always only be one name (one distinct
combination of FirstName and LastName) -- in the same record.

But the SQL parser would still require the FirstName and LastName columns
be in the GROUP BY clause if they are in the SELECT clause and not the
subject of an aggregate function. This is because there is the *potential*
that there might not be a direct correlation. Without making a preliminary
first-pass on the data to determine whether such a lack of correlation
exists, a one-pass SQL system must enforce such rules in the SQL statement.

- Show quoted text -

Quote
>Another thing:  Delphi local SQL won't let me use an AS field name, I have to
>repeate the whole darn expression, so in the example above, I have to have:

>GROUP BY
>invoice.customer.id,
>trim(customer.firstname) + ' ' + trim(customer.lastname)

>instead of

>GROUP BY
>invoice.customer.id,
>cust_name

>or even better

>GROUP BY
>invoice.customer.id

>Not a huge problem or anything, just a minor annoyance....

This is true. Column correlation names cannot be used directly in a GROUP
BY clause. You must use the actual column name. Use of a column correlation
name (or number prepresenting the ordinal position of the column) does work
in the ORDER BY clause. Hence the two acceptable statements below (which
use the sample Paradox table Orders.db).

  SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
  FROM "Orders.db" O
  GROUP BY O.SaleDate
  ORDER BY O.TheDate

  SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
  FROM "Orders.db" O
  GROUP BY O.SaleDate
  ORDER BY 1

The inability to use column correlation names or derived values in a GROUP
BY clause are nothing more than simple shortcomings in the local SQL
engine. I have written these up as suggested improvement changes for the
BDE.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "Knowledge comes, but wisdom lingers."
Technical Publications                -- Alfred, Lord Tennyson (1809-1914)
INPRISE Corporation
http://www.inprise.com/delphi

Re:SQL Catch 22 - GROUP BY error


Quote
Roman Krejci wrote:
>  I must allways use MAX(customer.name) aggregation in my programs.

Clever workaround - thanks for mentioning it.

Quote
> In the William's example, however, it is not at all obvious
> that all blobs values should be the same for each
> particular D.Field1ID, D.Field2ID, D.Field3  combination-
> blob field comes from D table as the grouped fields do.

You're right again...

Re:SQL Catch 22 - GROUP BY error


FoxPro will let you group on whatever you want, and if a field is not using an
aggregate-function and not in the group-by, then you just get whatever data you
happen to get.

I understand the conceptual point, however.

Quote
Steve Koterski wrote:
> On Fri, 07 Aug 1998 15:43:23 -0700, Richard Grossman
> <rgross...@techIII.com> wrote:

> >Actually, Roman, I have used SQL in another product and it allowed GROUP BY
> >without grouping on every single non-aggregate field.  Also, there is no
> >conceptual reason I can see why this should be so, and it is a minor PITA in
> >Delphi local SQL.

> As far as I have seen, this is standard SQL. For a column to be allowed in
> the SELECT clause, it must either be the subject of an aggregate function
> or also be listed in the GROUP BY clause. And -- to me -- this makes a lot
> of sense. Take the dataset below, for example:

>   State  Name              Amount
>   -----  ----              ------
>   CA     Stacey Green       99.01
>   CA     Mike Brown        100.23
>   CA     Lila Prose         45.99
>   CA     Richard Grossman   50.95

> Assume an SQL statement like this:

>   SELECT N.State, SUM(N.Amount)
>   FROM Names N
>   GROUP BY N.State
>   ORDER BY N.State

> If you execute an aggregating query, summing the Amount column and grouping
> on State, the above four records would be combined into one aggregated
> record for the State column value "CA", because all four records share this
> group value. The values in the Amount column would be added together to
> derive a single value in this one record. But what of the values in the
> Name column? Which name (Stacey, Mike, Lila, or Richard) gets included in
> the result set? Unless you use one of the aggregate functions like MIN or
> MAX, there is no realistic means by which to determine this.

> >For example:

> >SELECT
> >invoice.customer_id,
> >sum(invoice.amount) as total_sales
> >trim(customer.firstname) + ' ' + trim(customer.lastname) as cust_name
> >FROM
> >invoice, customer
> >WHERE
> >customer.id = invoice.customer_id
> >GROUP BY
> >invoice.customer.id

> >There's absolutely no reason why I should have to also include the "baggage"
> >fields firstname and lastname in the group-by, since the customer.id uniquely
> >identifies them.

> This is a slightly different case, where there is a direct one-for-one
> correlation between a given Customer_ID and the associated name. For any
> single Customer_ID, there will always only be one name (one distinct
> combination of FirstName and LastName) -- in the same record.

> But the SQL parser would still require the FirstName and LastName columns
> be in the GROUP BY clause if they are in the SELECT clause and not the
> subject of an aggregate function. This is because there is the *potential*
> that there might not be a direct correlation. Without making a preliminary
> first-pass on the data to determine whether such a lack of correlation
> exists, a one-pass SQL system must enforce such rules in the SQL statement.

> >Another thing:  Delphi local SQL won't let me use an AS field name, I have to
> >repeate the whole darn expression, so in the example above, I have to have:

> >GROUP BY
> >invoice.customer.id,
> >trim(customer.firstname) + ' ' + trim(customer.lastname)

> >instead of

> >GROUP BY
> >invoice.customer.id,
> >cust_name

> >or even better

> >GROUP BY
> >invoice.customer.id

> >Not a huge problem or anything, just a minor annoyance....

> This is true. Column correlation names cannot be used directly in a GROUP
> BY clause. You must use the actual column name. Use of a column correlation
> name (or number prepresenting the ordinal position of the column) does work
> in the ORDER BY clause. Hence the two acceptable statements below (which
> use the sample Paradox table Orders.db).

>   SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
>   FROM "Orders.db" O
>   GROUP BY O.SaleDate
>   ORDER BY O.TheDate

>   SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
>   FROM "Orders.db" O
>   GROUP BY O.SaleDate
>   ORDER BY 1

> The inability to use column correlation names or derived values in a GROUP
> BY clause are nothing more than simple shortcomings in the local SQL
> engine. I have written these up as suggested improvement changes for the
> BDE.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                      "Knowledge comes, but wisdom lingers."
> Technical Publications                -- Alfred, Lord Tennyson (1809-1914)
> INPRISE Corporation
> http://www.inprise.com/delphi

Re:SQL Catch 22 - GROUP BY error


Quote
> One thing you might try is a second, non-aggregated TQuery to supply the
> BLOB column. Set up a Master-Detail relationship (aggregated query as the
> Master) using the DataSource property. Set the DataSource property of the
> Detail TQuery to the TDataSource used for the aggregated (Master) TQuery.
> The SQL would need to include a WHERE clause to filter to a matching
record
> (or more if they exist). This WHERE clause wouold filter automatically by
> using a parameter named the same as a column in the aggregated query. For
> example, if the combination of Field1ID and Field2ID in the Master query
> can be used to identify the right record to retrieve from the Detail
query:

>   SELECT T2.BlobField
>   FROM "First.DB" T2
>   WHERE (T2.Field1ID = :Field1ID) AND (T2.Field2ID = :Field2ID)

//////////////////////////////////////////////////////////////////////////

Quote
> Steve Koterski                      "Knowledge comes, but wisdom
lingers."
> Technical Publications                -- Alfred, Lord Tennyson
(1809-1914)
> INPRISE Corporation
> http://www.inprise.com/delphi

Steve

Thank you for your suggestion of using a master-detail relationship, which
i have done as you descirbed (below), jowever i can't seem to make the
dbgrid recognise the field in the detail query

This is what i have:

a 'sqlsearch' query coponent which does the aggregrate query

- SQL is provided at run time (see below)

- has fields: FileName,FileID,PathID, Path (lookupfield)

i have added a 'sqlsearchmore' query component

- sql:
  Select T2.Thumb
  From "Files.db" T2
  Where (T2.FileID=:FileID)

- field:
  Thumb  a ftgraphic field

- datasource: the datasource for sqlsearch above

The code that fills the dbgrid: viewfilesgrid is attached to the bottom of
this message. the datasource for the dbgrid is sqlsearch

My question is this how can i add the field thumb to my dbgrid?

i.e.  if i don't add the field Thumb to sqlsearch then dbgrid won't allow
me add it, it does not see that it is a detail field of the master table

and

if i add the field thumb (fkdata) as a field of sql search then i get the
error

'Edatabase error sqlsearch: field 'thumb' not found!'

Thanks for your help

William
the_white_ho...@usa.net

************************************************************************

procedure TfrmMain.SearchbtnClick(Sender: TObject);
var
  f, iCount: integer;
  strSelAttributes, strSQL: string;
const
  SQL_FIND_ALL =
    'SELECT DISTINCT D.FileID, D.PathID, D.FileName, Count(AttributeID) AS
Score '
    + 'FROM "Files.DB" D, "Links.db" d1 '
    + 'WHERE '
    + '(d1.FileID = D.FileID) and (AttributeID IN (%s)) '
    + 'Group BY D.FileID, D.PathID, D.FileName '
    + 'HAVING Count(AttributeID)>=%d ';
    + 'Order BY D.FileName';

begin
try

  with dgdSearchAttributes.SelectedRows do begin
    iCount:= Count;
    if (icount = 0) then exit;  // nothing to search for
    screen.cursor:=crhourglass;

   // here wwe compile a list of the attribute ids that are the items being
searched on
    // files.db is the list of files and paths, with a fileID that can be
used to link to the items
    // attributes.db is a list of keywords that can be used to describe
files with an attributeid that can be used to link to the attribute
    // links.db is the matching of fileID's and AttributeID's

    for f:= 0 to iCount-1 do begin
      dmWnkData.tblAttributes.Bookmark:=Items[f];
      strSelAttributes:= strSelAttributes
        + dmWnkData.tblAttributesAttributeID.AsString + ',';
    end;
    //  strSelAttributes is '12, 23, 23,' etc
    SetLength(strSelAttributes, Length(strSelAttributes) -1); // remove
trailing ','

    // build sql search string
       strSQL:= Format(SQL_FIND_ALL, [strSelAttributes, iCount]);

    {I tried using this to no avail
     with dmWnkData.sqlSearchmore do begin
        Close;
        Open;
      end;}

    with dmWnkData.sqlSearch do begin
      Close;
      SQL.Text := strSQL;
      Open;
    end;
  end; // with dgdSearchAttributes do begin
  finally
    screen.cursor:=crdefault;
  end;
end;

Re:SQL Catch 22 - GROUP BY error


Don't worry guys, i got it in the end, thanks to the brilliant woll2woll
infopower db components, which permit all sorts of things, including look
up (linked) blobs.

William

Brian Bushay TeamB <BBus...@DataGuidance.com> wrote in article
<35cf0b0e.1281...@forums.borland.com>...

Quote

> >Is it just me or can look up fields not be BLOB fields?  Whenever i
insert
> >the look up field and set active to true i get:

> correct.  Blobs can not be lookup fields

> --
> Brian Bushay (TeamB)
> Bbus...@DataGuidance.com

Other Threads