I am getting an 'operation not supported' error when I run a SQL statement
containing two joined tables and a LIKE clause.  I have no problems doing a
similar query where a single table is involved.

The sql statement is as follows:

SELECT a.*,b.claim_no,b.claim_sufx, b.carrier_no,b.date_loss,b.class_loss,
b.type_loss,b.cause_loss,b.c_status,b.clmnt_type,b.mail_name,b.clmnt_cd,
b.instype
FROM claimant a
INNER JOIN claims b
ON a.clmnt_cd = b.clmnt_cd
WHERE (b.instype="A") AND (clmnt_last LIKE "SMITH%")

Anybody know why this fails?

Thanks for any input,
Mark J. Wallin

Try changin clmnt_last LIKE to a.clmnt_last LIKE

--
Bill
(TeamB cannot answer questions received via email)

Actually, that is what it was in my code - I just didn't copy it correctly
to the message.  I
still get the same error.  I believe, however, that what I want to do will
work if I use a subquery instead of a direct join.  I just wonder why what I
have doesn't work in local tables.  If this kind of thing is not supported
by the BDE, it does not seem to be documented.  I tried the same query in
SQL Server with two tables and it works OK without using a subquery.

"Bill Todd (TeamB)" <bill.nos...@dbginc.com> wrote in message
news:3c179acc$1_2@dnews...

Every database implements its own dialect of SQL and Local SQL, the dialect
used for Paradox and dBase tables is quite limited in some ways.

--
Bill
(TeamB cannot answer questions received via email)

news:3c1798ff$1_2@dnews...

You don't say what database.

Is RequestLive set true? If so then try turning it off. Local SQL does
accept joins.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

I am using a .DBF table (dbase driver).  The result set doesn't have to be live
in this case and I am using an ORDER by as the last sql statement.  I will check
to see how requestlive is set.  As mentioned, the Join works fine and the query
works fine if I substitute an '=' for the 'LIKE'.  I have just verified that the
same code using the JOIN and the LIKE works fine using  SQL SERVER tables.  It
just gives me the error using the DBF tables when LIKE is used.  I also found
that I could use a subquery to join the tables and LIKE works OK in that
scenario but I really need to use the JOIN because I need some fields in the
second table, whereas with the subquery method, I can't get any of the other
fields out of the second table (only one field can be used in the subquery
SELECT).

Sorry for not including that information.  I am using a .dbf table (dbase
driver) via the BDE ver. 5.11.  The requestLive property was set to false
and I tried it both ways but it doesn't change the outcome.  I am using an
ORDER BY so the result set would not be live anyway.  As I said, if I change
the LIKE to '=' and eliminate the '%', the query works as advertised.  With
LIKE and '%', it fails with the 'operation not applicable'.  Here is the
exact syntax of the offending code I am using:

qClaimant.sql.add('SELECT a.*, b.claim_no, b.claim_sufx, '+
                            'b.carrier_no,b.date_loss,b.class_loss,'+
                            'b.type_loss,b.cause_loss,b.c_status,'+
                            'b.clmnt_type,b.mail_name,b.clmnt_cd,'+
                             'b.instype') ;
qClaimant.sql.add('FROM claimant a') ;
qClaimant.sql.add('INNER JOIN claims b') ;
qClaimant.sql.add('ON a.clmnt_cd = b.clmnt_cd') ;
qClaimant.sql.add('WHERE (b.instype="A")') ;
qClaimant.sql.add(' AND (a.'+sSearchField +
                            ' LIKE "'+ upperCase(edSearch.text) + '%")');
qClaimant.SQL.add( 'ORDER BY a.' + sSortOrd ) ;

I am not using parameters as the same query table is used for other queries.
I wouldn't think it would make any difference to do the query with
parameters.
I tried the same sql syntax on a SQL Server database and it works without
complaint.  I also  found that I could use a subquery which effectively
joins the two tables and could successfully use a LIKE there, but that
doesn't allow me to get any of the other data in the second table since the
subquery allows you to access the only the common key field.  The following
is an example of a subquery joining two tables with a LIKE clause that works
in local tables:

SELECT * FROM claimant a
WHERE  (a.clmt_last LIKE "B%")
AND
a.clmnt_cd IN
(SELECT b.clmnt_cd FROM claims b WHERE b.instype="A")

Unfortunately, I need a JOIN to get what I need.

news:3c18c3c9$1_2@dnews...

Is the field(s) specified in sSortOrd also included in the Select clause?
This is a requirement in Local SQL - all order by fields must be selected.

If the sort fields are indeed present in the select clause then I can't tell
you why this is not working, it is legal according to the Local SQL
documentation. But I can give you an alternative syntax to try, an
"equ-join" which gives same results as an inner join:

 qClaimant.sql.add('FROM claimant a, claims b') ;
 qClaimant.sql.add('WHERE a.clmnt_cd = b.clmnt_cd') ;
 qClaimant.sql.add('AND (b.instype="A")') ;
 qClaimant.sql.add(' AND (a.'+sSearchField +
                             ' LIKE "'+ upperCase(edSearch.text) + '%")');
 qClaimant.SQL.add( 'ORDER BY a.' + sSortOrd ) ;

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

Oops, there were mistakes in my proposed test code.  The corrected test
syntax is

***  Equi-join code that fails with an 'operation not supported' error:
SELECT p.*, c.*
FROM parentTable p, childTable c
WHERE (c.keyfield = p.keyfield)
 AND (c.somefield = "somevalue")
 AND (p.searchfield LIKE "uservalue%")
ORDER BY p.searchfield

*** Syntax that works via sub-query
SELECT p*
FROM parentTable p
WHERE p.keyfield IN
(SELECT c.keyfield FROM childTable WHERE c.somefield="somevalue")
AND (p.searchfield LIKE "uservalue%")
ORDER BY p.searchfield

"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message

I tried your suggestion above and the same error results.  Apparently, the
BDE/local SQL doesn't like "LIKE" in joined tables, no way, no how.  I would
appreciate it if someone could verify this by doing a simple join by either
the JOIN clause or the equi-join, just as a sanity check.  It is always
possible that there is something peculiar about my dbf tables and indexes,
but as everything works with an '=' and not with a 'LIKE' indicates to me
that it is something systemic, and thus should be documented for dbf tables
if not fixed.  The sql that would cause the program to crash would be
something like this:

SELECT p.* FROM parentTable p
INNER JOIN childTable c
ON p.keyfield = c.keyfield
WHERE (c.somefield = "somevalue")
AND p.searchfield LIKE "uservalue%"
ORDER BY p.searchfield

or in the equi-join case which also crashes:

SELECT p.*, c.*
FROM parentTable p, childTable c
WHERE (c.keyfield = p.keyfield)
  AND (c.somefield = "somevalue")
  AND (a.searchfield LIKE "uservalue%")
ORDER BY a.somefield

The syntax that does work with local dbf tables (no crash, good result) for
joined tables with a LIKE involves a subquery:
SELECT p*
FROM parentTable p
WHERE p.keyfield IN
(SELECT c.keyfield WHERE c.somefield="somevalue)
AND (p.searchfield LIKE "uservalue%")

However, in the last case, you don't get access to the fields of the child
table. The first two that fail with local tables have been verified to work
with SQL Server tables using the same syntax.

Thanks,
Mark J. Wallin

news:3c1a1283$1_2@dnews...

There is something unique about your sytem/data/something. I tried the
following on the sample dbase files in dbdemos:

select * from clients c
join holdings h on c.acct_nbr = h.acct_nbr
where
c.state='CA'
and h.symbol like "L%"
order by h.symbol

Symbol is an indexed field so I also tried this with an unindexed field,
c.city. I also tried a left join instead of inner join. All cases are
working perfectly.

Sorry I can't offer any more, all I can tell you is it *should* work. Try
rebuilding your indexes just in case that has anything to do with it.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

Thanks for your reply.  I guess I should have tried this myself on the demos
or some other set of tables.  Its always possible that there is something
peculiar about the tables but I don't know what it is.  Reindexing didn't
help.  It could also be some 'junk' in some of the records, which has caused
occasional crashes and might be showing up here.  The tables use .MDX
indexes, so I don't know if that is a factor.  There are quite a few indexes
involved, some more or less redundant (not very well designed), and I had to
add another one recently.  I guess I have to take your word that 'it should
work'.  However, it doesn't in my case and I did a rather elaborate work
around to get the necessary effect which will have to do until we switch to
a SQL Server database.  I will be glad to do so for another reason - I spend
a great deal of time dealing with Crystal Reports which likes to stop
working if you do anything stupid like try to change table structures or
pack your tables or pretty much anything else including the what the outside
temperature is that day.  I'm told that it works better with SQL tables: how
could it be worse?

Thanks for your input,
Mark J. Wallin

Sorry, I don't understand what you mean here.  Please clarify.

Thanks,
Mark Wallin

To answer your question, yes, the field is included that corresponds to
sSortOrd.
I will give your equi-join suggestion a try and see if that works.  It looks
like it should work.

Thanks,
Mark Wallin