Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
14
Erster Beitrag:
vor 2 Jahren, 1 Monat
Letzter Beitrag:
vor 2 Jahren, 1 Monat
Beteiligte Autoren:
Al, Arie, CCC2, DerekT, Piet van Zanten

[WD20] Is running a query on a query good policy ?

Startbeitrag von Al am 28.12.2015 23:02

Hello All

The query editor runs out of puff after about 8 joins and refuses to add any more, so I have used the first query as the base data for a second query so I can add more joins.

It all seems to work ok, but I am wondering if there are any pitfalls in using queries in this manner that I am not aware of ?

Regards
Al

Antworten:

Hi Al,

I used that method before in one of my program , just make sure to check if there are any records in the first query before running second query.

the bad part for this method is the join is done in local , if you processing huge data , you may experience slow .

other methods you can try is
- using hexecutesqlQuery(0
- if you are using external database (sql server, mysql, postgresql, etc... ) you can create view as the based. this way the joins will be done in server side .


hope will help
CCC2

von CCC2 - am 29.12.2015 08:19
Hello CCC2

For the moment I am happy using the SQL wizard but as I get bettter at queries I will try writing my own and run them with HexecuteSQL Query as you suggest.

Regards
Al

von Al - am 30.12.2015 10:35
Hi Al,

You can use the editor to create your basic query and then copy the code to use it by hExecuteSQLquery.
Then you can add some more joins and other stuff.

Regards,
Piet

von Piet van Zanten - am 30.12.2015 10:53
Hello Piet

Thanks for the info, much better than starting from scratch.

Regards
Al

von Al - am 30.12.2015 11:19
Hi Al,

you can also modify the code of the SQL in the query editor (press F2).
Then you lose the visual presentation, but maybe the 8+ joins are allowed?
I do this quite often, because the visual query editor has it's limitations on other points as well.
HExecuteQuery() can still be used as before, so no need for code changes whereever you use the query.

von Arie - am 30.12.2015 11:25
Hello Arie

After the 8 + joins the query wizard returns with a message that it can't work out the connections and then it gets really annoyed and deletes all the joined files leaving only the original file !

Regards
Al

von Al - am 30.12.2015 12:17
Al
I have not got a query that requires 8+ joins so I may, not for the first time I might add, be barking up the wrong tree.

Try editing the query text directly using file suffixes instead of names.
If you roll your own or edit the text WD does accept this.

Maybe, just maybe, it will not get so confused.

SELECT 
a.idProperty AS idProperty,
a.PK_Property AS PK_Property,
a.FK_Landlord AS FK_Landlord,
a.ReferenceCode AS ReferenceCode,
b.Name AS Name,
c.FormalName
FROM
Property AS a,
Landlord AS b,
Landlord AS c
WHERE
a.idProperty = {pnIDProperty}
AND
a.FK_Landlord = b.PK_Landlord
AND
a.FK_Landlord = c.PK_Landlord
ORDER BY
a.PK_Property ASC


Not the greatest example I grant you but does give an idea of what I am talking about.
NOTE: You can link to the same file twice using this approach - see Landlord above

If you do need to roll your own and use HExecuteSQLQuery it is not that different
Just declare
MyQry is string [

]

Paste the above, or better still your, query between the [] brackets, change the {Parameter} for %1 and use Stringbuild to substitute a value.

If all else fails then try building it line by line to discover where 'enough is enough' so to speak where multiple joins are required.

von DerekT - am 30.12.2015 16:26
Hi A1,

I highly not recommend to do 2 query unless you have no choice.

i just run a test and found this method is not recommend on big database.

this is how i test.
DB : mysql

I create 3 tables, continent (7 records), region (23 records) , country (245 records)

I create 2 query
----- QRY1 ---------

SELECT
Continent.Id AS Continent_Id,
Continent.Continent_Name AS Continent_Name,
Region.Id AS Region_Id,
Region.Region_Name AS Region_Name,
Region.Continent_Id AS Region_Continent_Id
FROM
Continent,
Region
WHERE
Continent.Id = Region.Continent_Id


----- QRY2 ---------
SELECT
QRY1.Continent_Id AS QRY1_Continent_Id,
QRY1.Continent_Name AS QRY1_Continent_Name,
QRY1.Region_Id AS QRY1_Region_Id,
QRY1.Region_Name AS QRY1_Region_Name,
Country.Id AS Country_Id,
Country.Region_Id AS Country_Region_Id,
Country.Country_Name AS Country_Name
FROM
Country,
QRY1
WHERE
QRY1.Region_Id = Country.Region_Id


-------- code ---------------------------------------------------------
HCancelDeclaration(QRY1)
HCancelDeclaration(QRY2)

HExecuteQuery(QRY1)
Info( HNbRec(QRY1))

HExecuteQuery(QRY2)
Info( HNbRec(QRY2))



the result is correct but when i look into the profiler . it is totally different story

------from profiler when execute QRY1 ---------
SELECT `Continent`.`Id` AS `Continent_Id` , `Continent`.`Continent_Name` AS `Continent_Name` , `Region`.`Id` AS `Region_Id` , `Region`.`Region_Name` AS `Region_Name` , `Region`.`Continent_Id` AS `Region_Continent_Id`
FROM `Continent` , `Region`
WHERE ( `Continent`.`Id` = `Region`.`Continent_Id` ) ORDER BY `Continent`.`Id`

-----from profiler when execute QRY2 -------------------------
SELECT `Continent`.`Id` AS `Continent_Id` , `Continent`.`Continent_Name` AS `Continent_Name` , `Region`.`Id` AS `Region_Id` , `Region`.`Region_Name` AS `Region_Name` , `Region`.`Continent_Id` AS `Region_Continent_Id`
FROM `Continent` , `Region`
WHERE ( `Continent`.`Id` = `Region`.`Continent_Id` ) ORDER BY `Continent`.`Id`

-----------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM `Country`

------------------------------------------------------------------------------------------------------
SELECT * FROM `Country` WHERE `Region_Id`=1 ORDER BY `Region_Id`,`Id` LIMIT 100

-----------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM `Country`

------------------------------------------------------------------------------------------------------
SELECT * FROM `Country` WHERE `Region_Id`=2 ORDER BY `Region_Id`,`Id` LIMIT 100

=========================================================
repeat until Region_Id = 23
========================================================

i highly recommend you use hExecuteSQLquery()

von CCC2 - am 30.12.2015 16:59
Hello All

Thanks for all the replies - it looks like I should use HExecuteSQLQuery. The problem is that I really should go away and learn the basic theory behind queries, but as usual I am constrained by the fact that there are only a measly 24 hours in every day so if I can get some more help here I would appreciate it.

These are the two queries from the query wizard - I have removed the field list to make it a bit shorter.
This is the first query to extract data from file CRTrans and link in data from other files where the value in a CRTrans field is a foreign key to a primary key in the other file.
CRTrans is a file of Creditor Invoices and there are links in each record to the creditor, a job, GL accounts etc so to present this information in a table, I need to link to a large number of other files.
Previously, I was doing it all procedurally by filtering CRTrans to a Job or Creditor, setting up a loop, reading the first record and then doing a series of HSeekFirst() on the other files and then moving to the next record in CRTrans and all of this in HF Classic. The new work is using HFCS so that is why I am trying out the queries.

SELECT
List of fields
FROM
(
(
(
(
(
Creditor
LEFT OUTER JOIN
(
OrdLines
RIGHT OUTER JOIN
Crtrans
ON OrdLines.OTCLEUNIK = Crtrans.OTCLEUNIK
)
ON Creditor.CRCLEUNIK = Crtrans.CRCLEUNIK
)
LEFT OUTER JOIN
Glacc
ON Crtrans.DrGlAccount = Glacc.GLCLEUNIK
)
LEFT OUTER JOIN
Glacc GLCRAccAlias
ON Crtrans.CrGlAccount = GLCRAccAlias.GLCLEUNIK
)
LEFT OUTER JOIN
CostCat
ON Crtrans.JCSourceCleunik = CostCat.CACLEUNIK
)
LEFT OUTER JOIN
Orders
ON OrdLines.ODCLEUNIK = Orders.ODCLEUNIK
)
LEFT OUTER JOIN
Budgets
ON CostCat.BUCLEUNIK = Budgets.BUCLEUNIK
WHERE
(
Crtrans.JOCLEUNIK = {pJOCleunik}
AND Crtrans.FullyPaid = {pFullyPaid}
AND Crtrans.CRCLEUNIK = {pCRCleunik}
AND Crtrans.FinPeriod BETWEEN {pStartFinPeriod} AND {pEndFinPeriod}
AND Crtrans.OTCLEUNIK = {pOTCleunik}
AND Crtrans.TransactionDate BETWEEN {pStartTransDate} AND {pEndTransDate}
AND Crtrans.ClaimFinPeriod BETWEEN {pStartClmFinPeriod} AND {pStartClmFinPeriod}
)
ORDER BY
{psBudget} ASC,
{psCreditor} ASC,
{psInvoiceNo} ASC

This returns the correct number of records from the file
The second query uses the data from the first to continue the process of getting mnore linked data

SELECT
List of fields from query 1
FROM
(
(
Emp
RIGHT OUTER JOIN
(
Stock
RIGHT OUTER JOIN
(
FAssets
RIGHT OUTER JOIN
(
Lookups
RIGHT OUTER JOIN
(
ProjMemo
INNER JOIN
CRTransJOQry
ON ProjMemo.PMCLEUNIK = CRTransJOQry.CTCLEUNIK
)
ON Lookups.LUCLEUNIK = CRTransJOQry.UOM
)
ON FAssets.FACLEUNIK = CRTransJOQry.FACLEUNIK
)
ON Stock.STCLEUNIK = CRTransJOQry.STCLEUNIK
)
ON Emp.EMCLEUNIK = CRTransJOQry.EMCLEUNIK
)
LEFT OUTER JOIN
PayGroup
ON Emp.PGCleunik = PayGroup.PGCleunik
)
LEFT OUTER JOIN
Lookups PGLookupsAlias
ON PayGroup.PayGroupPosition = PGLookupsAlias.LUCLEUNIK
WHERE
(
ProjMemo.SourceFile = 'CT'
)



One thing I don't understand (among the many) is that the first query primarily uses left joins and the second uses mainly right joins. In the first query, why would the join to collect the orderline record, which is then used to collect the order number from the order file, be mixed in with the join to get the Creditor code ?
In principle, if I want to combine the two queries into one, is it just a matter of inserting the "From" clauses from the second query after the "From" clauses of the first query ? In the second query, I only need records from ProjMemo where the Sourcefile = "CT" and the SourceCleunik = CRTransJOQry.CTCleunik and I can't figure out where that goes if everything is combined into one query

I think I will probably end up just using the first query to do the bulk of the work and pick up the rest of the data procedurally.

Regards
Al

von Al - am 30.12.2015 21:38
Al,

I don't know when of why de WD query editor 'creates' left joins at day 1 and right joins on other moment. That's done behind the scenes.

In your example you try to get records from Crtrans.
There has to be a corresponding Projmemo record otherwise the Ctrans record will not get into your result set.
But that does not count for all other tables. If there for example no Emp record (which means most of the time that Ctrans.emp is empty, because otherwise you would have non-existing EMP-id in CTrans which would make no sense....) then you will still get a record in the resultset. But the EMP fields will be empty/null

LEFT or RIGHT joins are in fact doing the same thing, The result depends on the order of the listed tables.
You can turn all RIGHT joins into LEFT joins AND switch the first and second tablename. It gives you the same result.
If you do so you have LEFT joins only, which make things better readable.
From that point it's much easier to mix both queries into one.

btw: the OUTER word can be omitted if you like. That's the default for a LEFT/RIGHT JOIN, instead of a INNER JOIN.
And it also doesn;t matter what the seqeunce in the ON= part is. table1.key=table2.key is the same as table2.key=table1.key
Also without the parentheses is reads better.

Try something like this


SELECT
List of fields
FROM
Crtrans
LEFT INNER JOIN ProjMemo ON ProjMemo.PMCLEUNIK = CRTrans.CTCLEUNIK
LEFT JOIN Creditor ON Creditor.CRCLEUNIK = Crtrans.CRCLEUNIK
LEFT JOIN OrdLines ON OrdLines.OTCLEUNIK = Crtrans.OTCLEUNIK
LEFT JOIN Glacc ON Crtrans.DrGlAccount = Glacc.GLCLEUNIK
LEFT JOIN Glacc GLCRAccAlias ON Crtrans.CrGlAccount = GLCRAccAlias.GLCLEUNIK
LEFT JOIN CostCat ON Crtrans.JCSourceCleunik = CostCat.CACLEUNIK
LEFT JOIN Orders ON OrdLines.ODCLEUNIK = Orders.ODCLEUNIK
LEFT JOIN Budgets ON CostCat.BUCLEUNIK = Budgets.BUCLEUNIK
LEFT JOIN Lookups ON Lookups.LUCLEUNIK = CRTrans.UOM
LEFT JOIN FAssets ON FAssets.FACLEUNIK = CRTrans.FACLEUNIK
LEFT JOIN Stock ON Stock.STCLEUNIK = CRTrans.STCLEUNIK
LEFT JOIN Emp ON Emp.EMCLEUNIK = CRTrans.EMCLEUNIK
LEFT JOIN PayGroup ON Emp.PGCleunik = PayGroup.PGCleunik
LEFT JOIN Lookup AS PGLookupsAlias ON PayGroup.PayGroupPosition = PGLookupsAlias.LUCLEUNIK

WHERE
Crtrans.JOCLEUNIK = {pJOCleunik}
AND Crtrans.FullyPaid = {pFullyPaid}
AND Crtrans.CRCLEUNIK = {pCRCleunik}
AND Crtrans.FinPeriod BETWEEN {pStartFinPeriod} AND {pEndFinPeriod}
AND Crtrans.OTCLEUNIK = {pOTCleunik}
AND Crtrans.TransactionDate BETWEEN {pStartTransDate} AND {pEndTransDate}
AND Crtrans.ClaimFinPeriod BETWEEN {pStartClmFinPeriod} AND {pStartClmFinPeriod}
AND ProjMemo.SourceFile = 'CT'

ORDER BY
{psBudget} ASC,
{psCreditor} ASC,
{psInvoiceNo} ASC


von Arie - am 31.12.2015 10:13
Hello Arie

Thanks for the code simplification, I will give it a go.

Regards
Al

von Al - am 31.12.2015 21:57
Great explanation by Arie
IMHO the query generator went south when it was changed to accommodate the current SQL format (v18 iirc)
Now adds parenthesis all over the place, frequently does not run for complex queries and as someone on here once remarked turns the graphic display into something resembling the London Underground map.

As a result I tend to 'roll my own' which actually takes no longer than with the generator and has the added benefit of a. working and b. looking better.

This was provided by the generator (using an alias on the second link to LU_SysLookUps)...

SELECT
Property.idProperty AS idProperty,
Property.PK_Property AS PK_Property,
Property.FK_Landlord AS FK_Landlord,
LU_SysLookUps.Description AS _Accommodation,
b.Description AS _PropType
FROM
(
LU_SysLookUps
,
LU_SysLookUps b

)
RIGHT OUTER JOIN
Property
ON LU_SysLookUps.idLU_SysLookUps = Property.idLU_Accommodation
AND b.idLU_SysLookUps = Property.idLU_PropertyType
WHERE
(
Property.idProperty = 1
)

When tested in WDSQL it will not run - complains of Unexpected word: ,
If I remove the set of parenthesis in FROM it does run BUT because of the AND on the second join the result is a Cartesian join.

If I changed the code to

SELECT
Property.idProperty AS idProperty,
Property.PK_Property AS PK_Property,
Property.FK_Landlord AS FK_Landlord,
a.Description AS _Accommodation,
b.Description AS _PropType
FROM
LU_SysLookUps a,
LU_SysLookUps b
RIGHT OUTER JOIN Property ON a.idLU_SysLookUps = Property.idLU_Accommodation
RIGHT OUTER JOIN Property ON b.idLU_SysLookUps = Property.idLU_PropertyType
WHERE
Property.idProperty = 1


I get the expected result returned.

It may seem a pain to write your own and use HExecuteSQLQuery() but once you get into it (with the occasional visit to http://www.w3schools.com/sql/) it is not that bad or time consuming.

Just my 2 cents worth

von DerekT - am 01.01.2016 11:25
Hello Derek

It is getting clearer now.
Thanks you for the sql teaching site link, it is a very good reference site for me.


Regards
Al

von Al - am 01.01.2016 23:54
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.