Master Detail Queries - End USer Solution

rbuser

Hello Two Questions / Queries

Q1.
I have a detail dataset that I want to sort on. The sort order does nothing
for any fields apart from a text based field. If I remove the sort order and
actually order the fields in the fields tab to the sort order I need I get
the desired results. Surely this is a bug???

The fields I am trying to sort on are ID fields from two tables (Keyed
Fields). These same two fields are being used in a sort order for the Master
Data Set.

Any ideas.
I have fixed the report by arranging the fields in the correct order but it
seems crazy to me to have to do this.

Q2.
On another point with Master Detail datasets.
I have a report with two data sets..

Left side returns 10 records.
Right side returns too many records (detail).

Example

Data Set 1 - Table A with a where clause to return 10 records
Data Set 2 - Table A no where clause, linked to Data Set 1 (master)

The above returns 10 records in both data sets no problems yet.

If in Data set 2 i then intoduce a second table TABLE B joined to TABLE
A that will result in many more records in a standard SQL statement why is
it
within the DataSet in the ReportBuilder solution I now get duplicates. I
seem to get 6 * the amount of data I should expect.

I have checked this out in IBExpert and get the correct results there...

Any pointers???

Cheers

Mark

nicocizik

Hi Mark,

1. Take a look at the SQL generated by your QueryDataView by selecting the
SQL tab. Are you perhaps ordering by two different fields? We have never
had any issues with the sort order of queries in DADE. DADE simply executes
the SQL that is generated.

2. (From my previous reply)

Depending on which field you are joining Table A and Table B on, you are
probably getting duplicate enteries for the linking field in the detail
dataset. Take a look at the detail data (unlinked) and see if there are
multiple copies of the linking field.


--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico

The SQL is fine. It simply does not order by the field I select (certain
fields).

And why does the order of the output get sorted when I order the Fields on
the Fields tab and ignores the sort order on the Sort Tab?

I dont mind sending the report but of course you will not have my database
if that helps.

For question 2, no there are no duplicates. What behaviour should I expect
to see when I preview the data in a child data set if the master has been
narrowed down so I get one master record.


Mark

nicocizik

Hi Mark,

I'm sorry, I believe I'm not understanding the problem.


Are you saying that the data is not being ordered by a field that is placed
in the ORDER BY clause in the SQL? I use the functionality daily and it
works correctly.

Perhaps I'm missunderstanding what you are refering to as "output". Are you
talking about the report output or the display of the QueryDesigner or
perhaps something else?

For question 2, if there is a one to one relationship between the linking
field of the master and detail datasets, you should not see any duplicates.
Just remember that the problem is most likely dealing with the linking field
and the joining field(s) of the detail table.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com