Complex Views

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 ?

Quote:

---

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

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

Is the

Fixed an issue with SQL SELECT INNER JOIN

in RC7 a fix for this ?

Quote:

---

Originally Posted by barrymavin

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.

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.

Quote:

---

Originally Posted by barrymavin

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

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 :rolleyes:) by hand to Lianja and have submitted a ticket for one odd thing that I can't figure out.

David

Quote:

---

Originally Posted by HankFay

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

---

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.

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.