Sql help with nested statement

Hi all,

Using Firebird 1.5 with dBase 2.6.

The following statement is a simplified version of what I'm really after, 
but it gives me an error of:
Dynamic SQL error - Unexpected end of command

If I include an ORDER BY c.Lastname at the end of the statement, it causes 
the error to change to:
Token unknown - ORDER

      sql = "SELECT c.FirstName, c.LastName, c.City, c.StateID, c.ZipCode
;
   FROM Client c WHERE (SELECT distinct ClientID FROM Petdata) "

I'm trying to produce a query that I can use to make a single mailing label 
for every dog owner whether they have 1 dog or 10.

Being a newbie at sql, using a nested statement is very ambitious for me and 
I may not be doing it right.  As there are multiple ways of accomplishing 
the same thing, feel free to share a different way.  I just don't understand 
why I'm getting the error message I am in this case.

Dain Lochridge 

Update -

After hitting the books, this statement should be closer to functional, but 
causes dBase to become non-responsive.

      sql = "SELECT c.FirstName, c.LastName, c.City, c.StateID, c.ZipCode
;
   FROM Client c WHERE c.clientID IN (SELECT distinct p.ClientID FROM 
Petdata p) ;
         ORDER BY Lastname"
Still experimenting and learning.

Dain

"Dain Lochridge" <deloch@hotmail.com> wrote in message 
news:3RtLhT5jIHA.960@news-server...
> Hi all,
>
> Using Firebird 1.5 with dBase 2.6.
>
> The following statement is a simplified version of what I'm really after, 
> but it gives me an error of:
> Dynamic SQL error - Unexpected end of command
>
> If I include an ORDER BY c.Lastname at the end of the statement, it causes

> the error to change to:
> Token unknown - ORDER
>
>      sql = "SELECT c.FirstName, c.LastName, c.City, c.StateID,
c.ZipCode ;
>   FROM Client c WHERE (SELECT distinct ClientID FROM Petdata) "
>
> I'm trying to produce a query that I can use to make a single mailing 
> label for every dog owner whether they have 1 dog or 10.
>
> Being a newbie at sql, using a nested statement is very ambitious for me 
> and I may not be doing it right.  As there are multiple ways of 
> accomplishing the same thing, feel free to share a different way.  I just 
> don't understand why I'm getting the error message I am in this case.
>
> Dain Lochridge
> 

In article <3RtLhT5jIHA.960@news-server>, deloch@hotmail.com says...
> Hi all,
> 
> Using Firebird 1.5 with dBase 2.6.
> 
> The following statement is a simplified version of what I'm really after, 
> but it gives me an error of:
> Dynamic SQL error - Unexpected end of command
> 
> If I include an ORDER BY c.Lastname at the end of the statement, it causes

> the error to change to:
> Token unknown - ORDER
> 
>       sql = "SELECT c.FirstName, c.LastName, c.City, c.StateID,
c.ZipCode ;
>    FROM Client c WHERE (SELECT distinct ClientID FROM Petdata) "
> 
> I'm trying to produce a query that I can use to make a single mailing label

> for every dog owner whether they have 1 dog or 10.
> 
> Being a newbie at sql, using a nested statement is very ambitious for me
and 
> I may not be doing it right.  As there are multiple ways of accomplishing 
> the same thing, feel free to share a different way.  I just don't
understand 
> why I'm getting the error message I am in this case.
> 
> Dain Lochridge 
> 
> 
> 

Dain,

This is untested:

select distinct p.clientID, c.firstName, c.lastName from petData p ;
join client c on ( c.clientID = p.clientID )

Your second statement:

sql = "SELECT c.FirstName, c.LastName, c.City, c.StateID, c.ZipCode ;
FROM Client c WHERE c.clientID IN (SELECT distinct p.ClientID FROM  
Petdata p) ;
         ORDER BY Lastname"

should work but could take a long time if petData is a large table. I 
know the BDE is not very efficient with this type of SQL statement if it 
were dBASE tables.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Geoff's statement worked like a charm!  Thank you both.

In a similar JOIN statement I was trying earlier, the client table was 
joined to the petdata table and the "distinct" had no effect.  ( a
good 
example of not really understanding how to put the statement together to get 
the right result)

Select distinct c.clientID, c.firstName, c.lastName from Client c  ;
join petData p on ( c.clientID = p.clientID )

The subselect does indeed run but takes _forever_ (> 2 minutes).  The 
petdata table has about 16500 records.  If I click the mouse or do anything 
else while it's processing, dBase flips out.  The join solution is 
instantaneous. Verified that BDE settings are correct with SQLQueryMode = 
server, etc.  ODBC driver is 1.02.0070 .

Dain

"Lysander" <nobody@nowhere.de> wrote in message 
news:K$$AsB$jIHA.468@news-server...
> Geoff Wass [dBVIPS] schrieb:
>
>> select distinct p.clientID, c.firstName, c.lastName from petData p ;
>> join client c on ( c.clientID = p.clientID )
>
> if every client has at least one dog/pet you can even drop the join to 
> petData.
>
> But even with the join (necessary for example if another constraint needs 
> to select only dog-owners, but leave out cat-only-owners) this should 
> indeed run MUCH faster than any construction with a "where ... in
" and 
> subselect.
>
> Dain,
> also make sure that you have the correct ODBC-driver 1.02.0070 and that 
> your alias is indeed using that. Also make sure that the BDE's setting 
> "SQLQueryMode" is set to "server".
>
> BDE is behaving different than the documentation would suggest. If it is 
> empty, the statements according to documentation should be processed by 
> server, but this is not true.
>
> So you need to explicitely set the value to "Server". 

Geoff Wass [dBVIPS] schrieb:

> select distinct p.clientID, c.firstName, c.lastName from petData p ;
> join client c on ( c.clientID = p.clientID )

if every client has at least one dog/pet you can even drop the join to 
petData.

But even with the join (necessary for example if another constraint 
needs to select only dog-owners, but leave out cat-only-owners) this 
should indeed run MUCH faster than any construction with a "where ... in 
  " and subselect.

Dain,
also make sure that you have the correct ODBC-driver 1.02.0070 and that 
your alias is indeed using that. Also make sure that the BDE's setting 
"SQLQueryMode" is set to "server".

BDE is behaving different than the documentation would suggest. If it is 
empty, the statements according to documentation should be processed by 
server, but this is not true.