RB 7.02 Not Correctly Restructuring SQL When Converting From RB 5.0

rbuser

?

There is an error in the way RB performs the conversion from 5.0 to
7.02. The INNER JOIN statements are not always put in the correct order
to produce working SQL. Has this been fixed in later versions? I'm open
to ideas. We are still currently trying to get the version 9.03 demo
working to test if the fault remains.

for example, our original FROM/WHERE blocks follow:
-----------------------------------------
FROM CostCentre CostCentre,
Department Department, PayPeriod PayPeriod,
Location Location, Award Award,
TransHistoricalMaster TransHistoricalMaster,
TransPerPaySequence TransPerPaySequence,
HistoricalAllowance HistoricalAllowance,
Allowance Allowance,
Department Department_2, Employee Employee
WHERE
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
AND
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
AND
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
AND
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
AND
(Award.AWARDCODE = Employee.AWARDCODE)
AND
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
AND
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
AND
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND
(HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
AND
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
AND
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)
AND ( Allowance.PAYING = 1 )
--------------------------------------------------------

When loaded by RB Designer 7.02 it generates a load error and when we
continue anyway we get:
----------------------------------
FROM Employee Employee
INNER JOIN CostCentre CostCentre ON
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
INNER JOIN Department Department ON
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
INNER JOIN PayPeriod PayPeriod ON
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
INNER JOIN Location Location ON
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
INNER JOIN Award Award ON
(Award.AWARDCODE = Employee.AWARDCODE)
INNER JOIN TransHistoricalMaster TransHistoricalMaster ON
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
INNER JOIN TransPerPaySequence TransPerPaySequence ON
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN HistoricalAllowance HistoricalAllowance ON
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND (HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN Allowance Allowance ON
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
INNER JOIN Department Department_2 ON
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)
WHERE ( Allowance.PAYING = 1 )
--------------------------------

As we are using an Advantage DB here, I posted a query on the generated
SQL to Advantage.Delphi and Alex Wong of ADS had this to say:

**********************************

The third line of the FROM clause is not constructed correctly. The
condition should be on the Employee table and the CostCentre table.
Instead, it is referencing the HistoricalAllowance table which is not
part of that join. It seems that the new viersion of the RB is not
generating a valid SQL statement.
To get the correct syntax, the INNER JOIN CostCenter should be moved to a
location after the HistoricalAllowance table, shown below.

The original statement uses the WHERE clause so the SQL engine will
determine the correct application of the join condition.

Alex


FROM Employee Employee
INNER JOIN Department Department ON
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
INNER JOIN PayPeriod PayPeriod ON
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
INNER JOIN Location Location ON
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
INNER JOIN Award Award ON
(Award.AWARDCODE = Employee.AWARDCODE)
INNER JOIN TransHistoricalMaster TransHistoricalMaster ON
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
INNER JOIN TransPerPaySequence TransPerPaySequence ON
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN HistoricalAllowance HistoricalAllowance ON
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND (HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN CostCentre CostCentre ON
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
INNER JOIN Allowance Allowance ON
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
INNER JOIN Department Department_2 ON
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)

*****************************

I can provide a few other queries which are also not converted
correctly, if required. Can you advise if later versions of RB have
addressed this or if RB will be fixed in a time frame likely to help us?

Bill



--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

Do you know why the first SQL statement is in SQL1 syntax and the second is
in SQL2. (SQL1 syntax lists the table names in the From clause and the
conditions in the Where. SQL2 syntax uses the Inner Join statement.)

The syntax should specified by the Designer.DataSettings at the time the
report is created and shoulds always be SQL2 for Advantage.

Can you use RB 5 to save a report to an .rtm file in ASCII format. Then zip
up the .rtm file and the Ads tables and email to
support@digital-metaphors.com.

nardmoseley

Please modify your news reader to post using your real name.

rbuser

The site sign-up procedures allow any handle to be used, you might want
to look at that policy if it is something you don't like. In the spirit
of cooperation, however, I will change it ASAP.

Note that my "posting" email address contains my real name and email
except for the "nospam." addition. Feel free to email me direct.

Bill

in
....
[snip]



--- posted by geoForum on http://delphi.newswhat.com

rbuser

The sample report snippets are from an RB 5.0 report generated by the
wizard.
So the RB 5.0 designer produced the SQL1.

I dont know why the SQL originally got formatted that way. How is the
SQL format changed in RB 5.0? Can we reload the reports into RB 5.0 and
save them as SQL2?
Can we get RB 7.02 to use SQL1 as a way of avoiding impending disaster?

I'll get those file to you as quickly as possible. An hour or two.

Thanks,
Bill Miller

is
zip



--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

This issue has been handled via email.

For future reference, please modify your newsreader to post using Bill
Miller in the From, rather than Cool Change. That is a requirement of these
newsgroups. TIA