Originally Posted by
avianmanagement
If we have views that have multiple joins, unions, sub-selects etc, can we use the same sql statement to create virtual tables ?
Is a virtual table the best way to move a view from vfp to Lianja ?
Hi David,
using some help from Yvonne, which I had forgotten about when we discussed this, it is in fact possible to create multiple-join selects. Unsurprisingly, it works just like the notation defined on the wiki -- which I hadn't bothered looking at in detail, because after all, Barry said Lianja is SQL92-compliant, and that's what I've been using for the last 18 or 19 years or so.
Wrong.
Here's SQL that works in VFP or MS SQL:
Code:
select dprod.cmfgprodno, dsku.nsku, dfacl.cname
from ((dprod inner join dsku on dprod.cdprod_pk = dsku.cdprod_fk)
inner join dinve on dsku.nsku = dinve.ndsku_fk)
inner join dfacl on dinve.cdFacl_FK = dfacl.cdfacl_pk
where dfacl.nNumber = 3
And here's what it has to look like in Lianja:
Code:
select dprod.cmfgprodno, dsku.nsku, dfacl.nnumber ;
from dprod inner join dsku on dprod.cdprod_pk = dsku.cdprod_fk, ;
dsku inner join dinve on dsku.nsku = dinve.ndsku_fk, ;
dinve inner join dfacl on dinve.cdFacl_FK = dfacl.cdfacl_pk ;
where dfacl.nNumber = 3 ;
into cursor myprods
Notice that instead of the parens (which keeps the joins apart) used in vfp and t-sql (if you don't use them, you will be bitten at some point, or at least we have, in SQL Server -- in theory they are not needed), two changes are needed:
1) joins are separated by a comma
2) each join has <table1> <join type> <table2> (instead of inferring the table to join by).
I have to say the select in Lianja is very fast, and I didn't create any indexes (in fact, Lianja created temporary indexes on the fly).
Both queries return the same number of records (I imported the SQL DB into Lianja for the test -- and I will say that SQL import is hugely faster than it was when I first tried it).
As to why it's not SQL92 style, I have no idea. I'm sure Barry will chime in with more info.
hth,
Hank
Bookmarks