Re: join conditions for files

Hi Mohan,

I was trying the following command:
sele a.csno,dlr_code,b.* from pnw10409 a,opmain b,dist_mas where dist_mas.
dlr_code$b.dorno and b.dcd='I904' and a.csno=b.csno dist

This works in Fox 2.6, but not in VFP ( SQL: Internal error)
The problem is in the join condition with '$'

The join condition is ----- ANY(dlr_code) from dist_mas should be found in
field dorno from opmain

The specifications for the join condition seem to preclude this sort of a
join, but it works in Fox2.6 ( and correctly!)

Maybe $ is not allowed in a join condition, I don't know exactly. But SQL LIKE is allowed and a UDF is allowed.

The LIKE way would go like this:
SELCT aa.csno,dlr_code, bb.* FROM pnw10409 aa, opmain bb, dist_mas ;
WHERE bb.dorno LIKE "%" + dist_mas.dlr_code + "%" and bb.dcd='I904' and aa.csno=bb.csno
From the help I'm not sure, whether it will work!


The UDF way would go like this:
SELCT aa.csno,dlr_code,bb.* FROM pnw10409 aa, opmain bb, dist_mas ;
WHERE yourudf(dist_mas.dlr_code, bb.dorno) and bb.dcd='I904' and aa.csno=bb.csno

FUNCTION yourudf(tcContained, tcContains)
RETURN tcContained $ tcContains
ENDFUNC


Yet another way:
SELCT aa.csno,dlr_code,bb.* FROM pnw10409 aa, opmain bb, dist_mas ;
WHERE AT(dist_mas.dlr_code, bb.dorno) > 0 and bb.dcd='I904' and aa.csno=bb.csno


Another hint:
Don't use local aliases made of one letter only, they may confuse Foxpro, since they are reserved names for workspace aliases.

Regards
Bernhard Sander
.

Relevant Pages

  • Re: join conditions for files
    ... as there is no JOIN condition for DIST_MAS: the UDF ... Maybe $ is not allowed in a join condition, ... FUNCTION yourudf(tcContained, tcContains) ... Don't use local aliases made of one letter only, they may confuse Foxpro, since ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Joining every row from 1 table to every row from another
    ... I need to join all garments to all materials,! ... This can be done in SQL simply by omitting any join condition. ... This should work in Oracle, MySQL, or any other system that supports the most rudimentary features of SQL. ...
    (comp.lang.java.databases)
  • Re: SQL Select: Very Fast in VFP8, Very Slow in VFP9
    ... JOIN for the two tables contained the following JOIN condition: ... When I removed the ALLTRIM functions, the SQL Select statement in VFP9 ...
    (microsoft.public.fox.programmer.exchange)
Quantcast