Complex Views

[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 ?

[HankFay]

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:

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

[barrymavin]

As discussed there has been an ER to support the other syntax you mentioned.

[avianmanagement]

Is the

Fixed an issue with SQL SELECT INNER JOIN

in RC7 a fix for this ?

As discussed there has been an ER to support the other syntax you mentioned.

[barrymavin]

It's not related to different syntax no.

[barrymavin]

I have already discussed ths with hank. The bracketed syntax is in MSSQL for MS Access compatability.

[avianmanagement]

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 ?

[barrymavin]

I am not planning to do this for v1.0 no.

[avianmanagement]

Ok Thanks.

I'll see if I can write some code to automate the conversion as all my views created in Xcase are in this format.

I am not planning to do this for v1.0 no.

[HankFay]

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