Generated Magic SQL is not correct

rbuser

Hi,



I have two queries in DADE visual linked together on quotation_id.



Simplified: The master query retrieves the company orders



order_id quotation_id

1 1

2 2

3 2



Standalone the detail query retrieves the quotation parts



id quotation_id description amount

1 1 geveldragers
3526,00

2 2 geveldragers
1000,00

3 2 algemeen
250,00



Linked together the detail query retrieves erroneous the following:



id quotation_id description amount

1 1 geveldragers
3526,00

2 2 geveldragers
1000,00

2 2 geveldragers
1000,00

3 2 algemeen
250,00

3 2 algemeen
250,00



The report prints:



order_id description amount

1 geveldragers
3526,00



2 geveldragers
1000,00

geveldragers
1000,00

algemeen 250,00

algemeen 250,00



3 geveldragers
1000,00

geveldragers
1000,00

algemeen 250,00

algemeen 250,00





I know you say this way of querying the database (e.g. constructing the
magic sql to retrieve all records for the linked query in one execute) often
gives a performance benefit, but in more advanced reports this is often the
opposite if linking is even possible. What about using the delphi way of
linking queries in DADE using parameters and prepared queries. At least give
the user the choice. Drill down solutions are in my case often not possible
because the data retrieval is often much to slow.



Using D7, RB7.04 ent.



Regards,

Jeroen R?ttink

rbuser

Resend because formatting made a mess of the origininal.
---------------------------------------------------------

Hi,

I have two queries in DADE visual linked together on quotation_id.

Simplified: The master query retrieves the company orders

order_id quotation_id
1 1
2 2
3 2

The detail query retrieves the quotation parts

id quotation_id description amount
1 1 geveldragers 3526,00
2 2 geveldragers 1000,00
3 2 algemeen 250,00

Linked together the detail query retrieves erroneous the following:

id quotation_id description amount
1 1 geveldragers 3526,00
2 2 geveldragers 1000,00
2 2 geveldragers 1000,00
3 2 algemeen 250,00
3 2 algemeen 250,00

The report prints:

order_id description amount
1 geveldragers 3526,00

2 geveldragers 1000,00
geveldragers 1000,00
algemeen 250,00
algemeen 250,00

3 geveldragers 1000,00
geveldragers 1000,00
algemeen 250,00
algemeen 250,00

I know you say this way of querying the database (e.g. constructing the
magic sql to retrieve all records for the linked query in one execute) often
gives a performance benefit, but in more advanced reports this is often the
opposite if linking is even possible. What about using the delphi way of
linking queries in DADE using parameters and prepared queries. At least give
the user the choice. Drill down solutions are in my case often not possible
because the data retrieval is often much to slow.

Using D7, RB7.04 ent.

Regards,
Jeroen R?ttink

rbuser

Maybe you missed this thread?

nardmoseley

Sorry, yes I missed this one.

There are some complex cases in which the the Magic SQL breaks down.

One option is to manually edit the SQL and then write code to
programmatically link the datapipelines. (Linking the datapipelines requires
that the queries fire only a single time and that the detail query is
ordered on the linking field(s).

www.digital-metaphors.com/tips/LinkDADEPipelines.zip

Another option is to create custom dataviews. A single custom dataview can
contain multiple datasets that are linking using the Delphi linking method.
In this case, you do not link the datapipelines at all. For an example check
out RBuilder\Demos\EndUser\Custom DataViews.

In the future, we would like to support both types of linking with the Query
tools.