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 ?
Printable View
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:
And here's what it has to look like in Lianja: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
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: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
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
As discussed there has been an ER to support the other syntax you mentioned.
It's not related to different syntax no.
I have already discussed ths with hank. The bracketed syntax is in MSSQL for MS Access compatability.
Thanks.
Is it intended to make this compatible , or do I need to alter my SQL statements to make them work with Lianja Database engine ?
I am not planning to do this for v1.0 no.
Hi David,
the join expression will give you the parent of the join, so it shouldn't be a difficult job (except for the inevitable exceptions).
Hank