QBE knowledge

[srdaniel]

I'm trying to optimize this (paradox) nested select query.

SELECT * FROM table.db C
WHERE C.datetime =
( SELECT MAX( datetime ) from table.db D
WHERE datetime <= ‘07/21/2008’
and d.index1 = c.index1
and d.index2 = c.index2
and d.index3 = c.index3 )

I thought I could simply do something like this (if it is a correct
interpretation of the first query).

SELECT MAX(datetime), field1, ... FROM table.db
Where datetime <= ‘07/21/2008’
GROUP BY field1, ...

However I found that it doesn't work on tables with blob fields as you
can't group by a blob field. So I thought maybe I could just find out
how to re-write it use paradox's QBE language, but we don't have anyone
here with knowledge on that anymore.


Query

table.DB | DateTime | field1 | ...
| Check _max <= "07/21/2008" | Check | ...

EndQuery



However I can't seem to get a conditional MAX to work on a date field.
Is it possible? Does anyone see a better way to write the query?

Using d6, paradox, and yes I know paradox is old and not supported. We
are slowly migrating tables to firebird, these have not yet been.

daniel

[Rick Carter]

Post by srdaniel
However I found that it doesn't work on tables with blob fields as you
can't group by a blob field. So I thought maybe I could just find out
how to re-write it use paradox's QBE language, but we don't have anyone
here with knowledge on that anymore.

You say you're doing this in Delphi 6? Then how are you going to use a QBE
query anyway? Are you going to use the third-party component from rxLib,
or something else?

Well, it's been years since I've tried to do anything really clever with a
QBE query. If anyone can help you with the QBE query, it's probably a
developer using Paradox the application (yes, there are some diehards who
still use that as their primary development tool). Some of them hang out
at borland.public.bde, but the best place to search through articles and
forum posts would be at http://thedbcommunity.com

In general, when you can't accomplish something with one QBE query, split
it into two or more queries and run them in sequence.

But I don't know that even that will help you if it's essential for you to
"order by" that BLOB field. As I understand it, when the 32-bit BDE takes
a QBE query as input, it first tries to convert it to the equivalent SQL
query before passing it along.

Actually, I'd suggest a different approach. Run that first query (the
subquery in your original example), then send the results to a
ClientDataSet, and do the rest of your data manipulation within the CDS.
Though I have no experience with trying to "order by" a BLOB field, and
I'm not sure how well a CDS will support that either.

Rick Carter
***@despammed.com
Chair, Delphi/Paradox SIG, Cincinnati PC Users Group

--- posted by geoForum on http://delphi.newswhat.com

[srdaniel]

Post by Rick Carter

Post by srdaniel
However I found that it doesn't work on tables with blob fields as you
can't group by a blob field. So I thought maybe I could just find out
how to re-write it use paradox's QBE language, but we don't have anyone
here with knowledge on that anymore.

You say you're doing this in Delphi 6? Then how are you going to use a QBE
query anyway? Are you going to use the third-party component from rxLib,
or something else?
Well, it's been years since I've tried to do anything really clever with a
QBE query. If anyone can help you with the QBE query, it's probably a
developer using Paradox the application (yes, there are some diehards who
still use that as their primary development tool). Some of them hang out
at borland.public.bde, but the best place to search through articles and
forum posts would be at http://thedbcommunity.com
In general, when you can't accomplish something with one QBE query, split
it into two or more queries and run them in sequence.
But I don't know that even that will help you if it's essential for you to
"order by" that BLOB field. As I understand it, when the 32-bit BDE takes
a QBE query as input, it first tries to convert it to the equivalent SQL
query before passing it along.
Actually, I'd suggest a different approach. Run that first query (the
subquery in your original example), then send the results to a
ClientDataSet, and do the rest of your data manipulation within the CDS.
Though I have no experience with trying to "order by" a BLOB field, and
I'm not sure how well a CDS will support that either.
Rick Carter
Chair, Delphi/Paradox SIG, Cincinnati PC Users Group
--- posted by geoForum on http://delphi.newswhat.com

I think I'm going to try the CDS approach but we use Infopower with D6
which supports QBE's.

[Steven Green]

Post by srdaniel
However I found that it doesn't work on tables with blob fields as you
can't group by a blob field.

same response as Rick's.. you can't do that in QBE either.. basic reason
being that a BLOB field can hold anything, not just text.. and all the
"sort" logic is based on text..

--
Steven Green - Myrtle Beach, South Carolina USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

Post by srdaniel
I'm trying to optimize this (paradox) nested select query.
SELECT * FROM table.db C
WHERE C.datetime =
( SELECT MAX( datetime ) from table.db D
WHERE datetime <= ‘07/21/2008’
and d.index1 = c.index1
and d.index2 = c.index2
and d.index3 = c.index3 )
I thought I could simply do something like this (if it is a correct
interpretation of the first query).
SELECT MAX(datetime), field1, ... FROM table.db
Where datetime <= ‘07/21/2008’
GROUP BY field1, ...
However I found that it doesn't work on tables with blob fields as you
can't group by a blob field. So I thought maybe I could just find out how
to re-write it use paradox's QBE language, but we don't have anyone here
with knowledge on that anymore.
Query
table.DB | DateTime | field1 | ...
| Check _max <= "07/21/2008" | Check | ...
EndQuery
However I can't seem to get a conditional MAX to work on a date field. Is
it possible? Does anyone see a better way to write the query?
Using d6, paradox, and yes I know paradox is old and not supported. We are
slowly migrating tables to firebird, these have not yet been.
daniel