The Query Designer does not support either of those. Having is a SQL standard, but is not supported. Limit, is not a SQL standard, but I think some database products have added support for it.
You link query dataviews, by dragging the linking field from detail query dataview toolwindow to the field in the master query dataview toolwindow. A visual link will be drawn. There is no distinction between a one to many or one to one, they are handled the same way internally. When the master record position changes the linked dataset will find the first related record - thus the lookup will occur instantly.
If you are performing a join, it could be related to joining in a detail record too many times.
The other possibility is that it is related to the linking SQL.
Linking DataViews in the ReportBuilder Data workspace (DADE), requires that the detail data be sorted by the linking fields. When DataViews are linked, DADE generates special linking SQL for the detail DataView. The linking SQL (Magic SQL) can be viewed by positioning the mouse over the DataView window and pressing Ctrl + Left Mouse button. The results of the Magic SQL query can be viewed by pressing Shift + Left Mouse button.
ReportBuilder's DataPipeline architecture includes support for linked data traversal. Whenver the record position of the master data changes, the traversal logic compares the master and detail field link values and then determines where to locate the record position for the related detail data.
It would be a nice feature, though I think quite difficult to implement.
You can manually edit the SQL and add the Having. This will disable the linking ability. You can programmatically create the links, as shown in the following example. You just need to make sure that detail data is sorted on the linking fields.
Comments
The Query Designer does not support either of those. Having is a SQL
standard, but is not supported. Limit, is not a SQL standard, but I think
some database products have added support for it.
You link query dataviews, by dragging the linking field from detail query
dataview toolwindow to the field in the master query dataview toolwindow. A
visual link will be drawn. There is no distinction between a one to many or
one to one, they are handled the same way internally. When the master record
position changes the linked dataset will find the first related record -
thus the lookup will occur instantly.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
create two queries which were linked.
But for some reason it counted for each occurance the total again example
NoOfInvoices*Sum(InvoiceTotal) any idea ??
Paul
If you are performing a join, it could be related to joining in a detail
record too many times.
The other possibility is that it is related to the linking SQL.
Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.
ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenver the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
It would be a nice feature, though I think quite difficult to implement.
You can manually edit the SQL and add the Having. This will disable the
linking ability. You can programmatically create the links, as shown in the
following example. You just need to make sure that detail data is sorted on
the linking fields.
http://www.digital-metaphors.com/tips/EditSQLAndLink.zip
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com