Board index » delphi » Query within a Query - How?

Query within a Query - How?

Delphi 5 Professional.

I have for a long time in vain tried to create
a Query within a Query.

The following Query works fine, but will not
include rows where, for a given "Table",
 no records exist in BIDDERS.

SELECT DISTINCT T."Table",T."Size",T.Name, COUNT (*) as Population
FROM TABLSIZE as T, BIDDERS as B, BIDRNAME as N
WHERE (B."Table"=T."Table")  AND (B.BidID=N.BidID)
GROUP BY T."Table",T."Size",T.Name
ORDER BY T."Table",T."Size",T.Name

The following query seems to me to be a logical
implementaiton of the desired task.
But, Delphi complains about the second SELECT.
Why?

SELECT DISTINCT T."Table",T."Size",T.Name, COUNT (*) as Population
FROM TABLSIZE as T LEFT OUTER JOIN
(SELECT ALL B.BidID, B."Table" as BT
FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID))
ON T."Table"=BT
GROUP BY T."Table",T."Size",T.Name
ORDER BY T."Table",T."Size",T.Name

Any help would be appreciated.
Bj?rn

 

Re:Query within a Query - How?


You have to "alias" the subquery outside of its expression.
<snip>
 (SELECT ALL B.BidID, B."Table"
 FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
 ON T."Table"=BT."Table
<snip>

rb

Re:Query within a Query - How?


What you say seems to make sense.
But...

SELECT DISTINCT T."Table",T."Size",T.Name
, COUNT (*) as Population
FROM TABLSIZE as T LEFT OUTER JOIN
(SELECT ALL B.BidID, B."Table"
FROM BIDDERS as B, BIDRNAME as N
WHERE (B.BidID=N.BidID)) as BB
ON T."Table"=BB."Table"
GROUP BY T."Table",T."Size",T.Name
ORDER BY T."Table",T."Size",T.Name
yields the same complaint: Invalid use of keyword. Token SELECT Line Number:
4
what gives?

Bj?rn

Quote
"rb" <r...@killspam-videotron.ca> wrote in message news:3a037dfc$1_1@dnews...

> You have to "alias" the subquery outside of its expression.
> <snip>
>  (SELECT ALL B.BidID, B."Table"
>  FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
>  ON T."Table"=BT."Table
> <snip>

> rb

Re:Query within a Query - How?


What database are you using?

--

Alain Quesnel
cinqsanss...@compuserve.com

------------

Quote
"Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message

news:8u02ul$dfk3@bornews.borland.com...
Quote
> What you say seems to make sense.
> But...

> SELECT DISTINCT T."Table",T."Size",T.Name
> , COUNT (*) as Population
> FROM TABLSIZE as T LEFT OUTER JOIN
> (SELECT ALL B.BidID, B."Table"
> FROM BIDDERS as B, BIDRNAME as N
> WHERE (B.BidID=N.BidID)) as BB
> ON T."Table"=BB."Table"
> GROUP BY T."Table",T."Size",T.Name
> ORDER BY T."Table",T."Size",T.Name

> yields the same complaint: Invalid use of keyword. Token SELECT Line
Number:
> 4
> what gives?

> Bj?rn

> "rb" <r...@killspam-videotron.ca> wrote in message

news:3a037dfc$1_1@dnews...

- Show quoted text -

Quote

> > You have to "alias" the subquery outside of its expression.
> > <snip>
> >  (SELECT ALL B.BidID, B."Table"
> >  FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
> >  ON T."Table"=BT."Table
> > <snip>

> > rb

Re:Query within a Query - How?


I'm using Paradox tables, Level 4 (TABLSIZE) or 5 (BIDDERS & BIDRNAME).
However, I'm sure it's not the database structure that's the problem.
I suspect that perhaps Delphi 5 Professional (as supposed to the
Server or Enterprise edition) doesn't support "complicated" SQL.

I can run the desired query (with desired results)
directly from Paradox 7 using QBE:

Query
ANSWER: :PRIV:ANSWER.DB

TABLSIZE.DB | Table         | Size   | Name                        |
            | Check _join1! | Check  | Check calc count all as Pop |

BIDDERS.DB | BidID  | Table  |
           | _join2 | _join1 |

BIDRNAME.DB | BidID  |
            | _join2 |

EndQuery

Unfortunately, when requested to convert to SQL, Paradox responds
"SQL not available for this query" (this always happens when I use
the exclusion operator (!), which essentially is equivalent to
LEFT OUTER JOIN.

FYI: (* means primary key, S=Short Integer)
    TABLSIZE:    Table(S*);Size(S);Name(A16)
    BIDDERS:    BidID(S*);Table(S);... (other non-key fields)
    BIDRNAME:    BidID(S*);PersonID(S*);...(other non-key fields)

Quote
"Alain Quesnel" <cinqsanss...@compuserve.com> wrote in message

news:8u10fn$mfv3@bornews.borland.com...
Quote
> What database are you using?

> --

> Alain Quesnel
> cinqsanss...@compuserve.com

> ------------
> "Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message
> news:8u02ul$dfk3@bornews.borland.com...
> > What you say seems to make sense.
> > But...

> > SELECT DISTINCT T."Table",T."Size",T.Name
> > , COUNT (*) as Population
> > FROM TABLSIZE as T LEFT OUTER JOIN
> > (SELECT ALL B.BidID, B."Table"
> > FROM BIDDERS as B, BIDRNAME as N
> > WHERE (B.BidID=N.BidID)) as BB
> > ON T."Table"=BB."Table"
> > GROUP BY T."Table",T."Size",T.Name
> > ORDER BY T."Table",T."Size",T.Name

> > yields the same complaint: Invalid use of keyword. Token SELECT Line
> Number:
> > 4
> > what gives?

> > Bj?rn

> > "rb" <r...@killspam-videotron.ca> wrote in message
> news:3a037dfc$1_1@dnews...

> > > You have to "alias" the subquery outside of its expression.
> > > <snip>
> > >  (SELECT ALL B.BidID, B."Table"
> > >  FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
> > >  ON T."Table"=BT."Table
> > > <snip>

> > > rb

Re:Query within a Query - How?


It's not your version of Delphi that doesn't support that particular syntax,
it's Paradox. Paradox uses Local SQL, which is a subset of standard ANSI-92
SQL. For more complicated SQL, you would need to use a database like
Interbase, Oracle or MSSQL Server, to name a few. Even though Paradox is
quite flexible and very fast for small to medium amounts of data, there's
only so much you can do with it.

--

Alain Quesnel
cinqsanss...@compuserve.com

------------

Quote
"Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message

news:8u11os$mfn4@bornews.borland.com...
Quote
> I'm using Paradox tables, Level 4 (TABLSIZE) or 5 (BIDDERS & BIDRNAME).
> However, I'm sure it's not the database structure that's the problem.
> I suspect that perhaps Delphi 5 Professional (as supposed to the
> Server or Enterprise edition) doesn't support "complicated" SQL.

> I can run the desired query (with desired results)
> directly from Paradox 7 using QBE:

> Query
> ANSWER: :PRIV:ANSWER.DB

> TABLSIZE.DB | Table         | Size   | Name                        |
>             | Check _join1! | Check  | Check calc count all as Pop |

> BIDDERS.DB | BidID  | Table  |
>            | _join2 | _join1 |

> BIDRNAME.DB | BidID  |
>             | _join2 |

> EndQuery

> Unfortunately, when requested to convert to SQL, Paradox responds
> "SQL not available for this query" (this always happens when I use
> the exclusion operator (!), which essentially is equivalent to
> LEFT OUTER JOIN.

> FYI: (* means primary key, S=Short Integer)
>     TABLSIZE:    Table(S*);Size(S);Name(A16)
>     BIDDERS:    BidID(S*);Table(S);... (other non-key fields)
>     BIDRNAME:    BidID(S*);PersonID(S*);...(other non-key fields)

> "Alain Quesnel" <cinqsanss...@compuserve.com> wrote in message
> news:8u10fn$mfv3@bornews.borland.com...
> > What database are you using?

> > --

> > Alain Quesnel
> > cinqsanss...@compuserve.com

> > ------------
> > "Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message
> > news:8u02ul$dfk3@bornews.borland.com...
> > > What you say seems to make sense.
> > > But...

> > > SELECT DISTINCT T."Table",T."Size",T.Name
> > > , COUNT (*) as Population
> > > FROM TABLSIZE as T LEFT OUTER JOIN
> > > (SELECT ALL B.BidID, B."Table"
> > > FROM BIDDERS as B, BIDRNAME as N
> > > WHERE (B.BidID=N.BidID)) as BB
> > > ON T."Table"=BB."Table"
> > > GROUP BY T."Table",T."Size",T.Name
> > > ORDER BY T."Table",T."Size",T.Name

> > > yields the same complaint: Invalid use of keyword. Token SELECT Line
> > Number:
> > > 4
> > > what gives?

> > > Bj?rn

> > > "rb" <r...@killspam-videotron.ca> wrote in message
> > news:3a037dfc$1_1@dnews...

> > > > You have to "alias" the subquery outside of its expression.
> > > > <snip>
> > > >  (SELECT ALL B.BidID, B."Table"
> > > >  FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
> > > >  ON T."Table"=BT."Table
> > > > <snip>

> > > > rb

Re:Query within a Query - How?


Than you very much for clarifying this to me.
It's of course very disappointing, but I guess
I'll have to live with it.
At least, I will not have to spend more time
trying to tinker with the SQL.

Bj?rn

Quote
"Alain Quesnel" <cinqsanss...@compuserve.com> wrote in message

news:8u1311$md04@bornews.borland.com...
Quote
> It's not your version of Delphi that doesn't support that particular
syntax,
> it's Paradox. Paradox uses Local SQL, which is a subset of standard
ANSI-92
> SQL. For more complicated SQL, you would need to use a database like
> Interbase, Oracle or MSSQL Server, to name a few. Even though Paradox is
> quite flexible and very fast for small to medium amounts of data, there's
> only so much you can do with it.

> --

> Alain Quesnel
> cinqsanss...@compuserve.com

> ------------
> "Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message
> news:8u11os$mfn4@bornews.borland.com...
> > I'm using Paradox tables, Level 4 (TABLSIZE) or 5 (BIDDERS & BIDRNAME).
> > However, I'm sure it's not the database structure that's the problem.
> > I suspect that perhaps Delphi 5 Professional (as supposed to the
> > Server or Enterprise edition) doesn't support "complicated" SQL.

> > I can run the desired query (with desired results)
> > directly from Paradox 7 using QBE:

> > Query
> > ANSWER: :PRIV:ANSWER.DB

> > TABLSIZE.DB | Table         | Size   | Name                        |
> >             | Check _join1! | Check  | Check calc count all as Pop |

> > BIDDERS.DB | BidID  | Table  |
> >            | _join2 | _join1 |

> > BIDRNAME.DB | BidID  |
> >             | _join2 |

> > EndQuery

> > Unfortunately, when requested to convert to SQL, Paradox responds
> > "SQL not available for this query" (this always happens when I use
> > the exclusion operator (!), which essentially is equivalent to
> > LEFT OUTER JOIN.

> > FYI: (* means primary key, S=Short Integer)
> >     TABLSIZE:    Table(S*);Size(S);Name(A16)
> >     BIDDERS:    BidID(S*);Table(S);... (other non-key fields)
> >     BIDRNAME:    BidID(S*);PersonID(S*);...(other non-key fields)

> > "Alain Quesnel" <cinqsanss...@compuserve.com> wrote in message
> > news:8u10fn$mfv3@bornews.borland.com...
> > > What database are you using?

> > > --

> > > Alain Quesnel
> > > cinqsanss...@compuserve.com

> > > ------------
> > > "Bj?rn Mossberg" <BMossb...@Houston.RR.com> wrote in message
> > > news:8u02ul$dfk3@bornews.borland.com...
> > > > What you say seems to make sense.
> > > > But...

> > > > SELECT DISTINCT T."Table",T."Size",T.Name
> > > > , COUNT (*) as Population
> > > > FROM TABLSIZE as T LEFT OUTER JOIN
> > > > (SELECT ALL B.BidID, B."Table"
> > > > FROM BIDDERS as B, BIDRNAME as N
> > > > WHERE (B.BidID=N.BidID)) as BB
> > > > ON T."Table"=BB."Table"
> > > > GROUP BY T."Table",T."Size",T.Name
> > > > ORDER BY T."Table",T."Size",T.Name

> > > > yields the same complaint: Invalid use of keyword. Token SELECT Line
> > > Number:
> > > > 4
> > > > what gives?

> > > > Bj?rn

> > > > "rb" <r...@killspam-videotron.ca> wrote in message
> > > news:3a037dfc$1_1@dnews...

> > > > > You have to "alias" the subquery outside of its expression.
> > > > > <snip>
> > > > >  (SELECT ALL B.BidID, B."Table"
> > > > >  FROM BIDDERS as B, BIDRNAME as N WHERE (B.BidID=N.BidID)) BT
> > > > >  ON T."Table"=BT."Table
> > > > > <snip>

> > > > > rb

Other Threads