Complex Views

[avianmanagement]

Hi Hank,

No it will not be hard to do. I already have some code that does something similar in the Xcase2VPM modifications I did recently.

I'll add it to the PSP stuff once it's done and tested.

I'm trying by hand at the moment to converts some complex views ( well complex for me anyway ) by hand to Lianja and have submitted a ticket for one odd thing that I can't figure out.

David

[barrymavin]

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:

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:

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

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.

[avianmanagement]

Brilliant, thanks Barry for sorting this so quickly, as always.

That will save a lot of time converting my view.

Really appreciate your doing this so late on in development, I know how busy you guys are getting ready for release of version 1 of Lianja.