DISTINCT clause in drag and drop builder view
8 October, 2009
I've created a view using the drag and drop builder, but now want to add a DISTINCT clause to the records that are retrieved. I've read the admin guide and gone through the builder and am unable to find anything that will allow me to add the DISTINCT clause.
Is this possible through the drag and drop builder or do I need to recreate my view using freehand SQL?
Is this possible through the drag and drop builder or do I need to recreate my view using freehand SQL?
Yellowfin will add a DISTINCT clause by default on all reports built with a drag-n-drop view.
There are a few exceptions to this rule though. If you have a CLOB, BLOB or other binary column in your report then the DISTINCT will be removed.. as most databases cannot compare binary objects to determine if they are unique or not.
(I think there may be one or two databases that don't support DISTINCT also.. maybe FoxPro)
There is also a option to toggle the DISTINCT on the Report Data page.
On the right side-navigation, in the Report Data section, there is a "Show Duplicate Records" check box.
This defaults to Unchecked..
If you are still not getting distinct records, and you have checked that the "Show Duplicate Record" check box is off, and you don't have any binary columns in your query, could you please send through the SQL being generated for your report.
Are you using Ingres? I can have a look in the query generation code to see if there is any logic that is Ingres specific when it comes to adding the DISTINCT, but from memory I don't think there is.
There are a few exceptions to this rule though. If you have a CLOB, BLOB or other binary column in your report then the DISTINCT will be removed.. as most databases cannot compare binary objects to determine if they are unique or not.
(I think there may be one or two databases that don't support DISTINCT also.. maybe FoxPro)
There is also a option to toggle the DISTINCT on the Report Data page.
On the right side-navigation, in the Report Data section, there is a "Show Duplicate Records" check box.
This defaults to Unchecked..
If you are still not getting distinct records, and you have checked that the "Show Duplicate Record" check box is off, and you don't have any binary columns in your query, could you please send through the SQL being generated for your report.
Are you using Ingres? I can have a look in the query generation code to see if there is any logic that is Ingres specific when it comes to adding the DISTINCT, but from memory I don't think there is.