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 ?
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
Last edited by HankFay; 2013-07-08 at 21:07. Reason: formatting
As discussed there has been an ER to support the other syntax you mentioned.
Principal developer of Lianja, Recital and other products
Follow me on:
Twitter: http://twitter.com/lianjaInc
Facebook: http://www.facebook.com/LianjaInc
LinkedIn: http://www.linkedin.com/in/barrymavin
It's not related to different syntax no.
Principal developer of Lianja, Recital and other products
Follow me on:
Twitter: http://twitter.com/lianjaInc
Facebook: http://www.facebook.com/LianjaInc
LinkedIn: http://www.linkedin.com/in/barrymavin
I have already discussed ths with hank. The bracketed syntax is in MSSQL for MS Access compatability.
Principal developer of Lianja, Recital and other products
Follow me on:
Twitter: http://twitter.com/lianjaInc
Facebook: http://www.facebook.com/LianjaInc
LinkedIn: http://www.linkedin.com/in/barrymavin
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.
Principal developer of Lianja, Recital and other products
Follow me on:
Twitter: http://twitter.com/lianjaInc
Facebook: http://www.facebook.com/LianjaInc
LinkedIn: http://www.linkedin.com/in/barrymavin
I have now changed the SQL parser to handle this bracketed join syntax (as well as the original syntax). In fact in SQL-92 the brackets are optional. They are there to specify the order of the joins. Under normal circumstances the joins would be from left to right in the SQL statement. If you are using some SQL statement generation tools they may be using the bracketed notation in order to enforce a right to left join syntax -- rather than just generating the statement in the correct left to right order. Anyway that aside, the Lianja SQL parser now handles both syntaxes.
Last edited by barrymavin; 2013-07-21 at 21:05.
Principal developer of Lianja, Recital and other products
Follow me on:
Twitter: http://twitter.com/lianjaInc
Facebook: http://www.facebook.com/LianjaInc
LinkedIn: http://www.linkedin.com/in/barrymavin
Bookmarks