on 06-12-2013 7:16 PM
Hi,
We have an issue with large amount of rows being returned and would like to have the sql generated with a distinct operator or a group by to limit the result set.
e.g select distinct prod_num, parent_prod_num
from products
or select pord_num, prod_name
from porducts
group by pord_num, prod_name
As I understand it the parameter DISTINCT VALUES should control this.
queris with measures are correctly getting group by clause inferred (ie.. objects haev agg function on them).
However dimension only querries have neither a group by or distinct.
We are using idt tool and have not set/changed this parameters so far.
Our example have a large dimesnion table product numbers and parent product numbers. dimesnion table concerned has over 7 million such rows.
but only a few thousand distinct products.
Have an issue realted to a bug ADAPT01695593 saying problems over 1 million rows.
Would like to be able to haev sql generated with distinct operator.
unx universe using idt tool .
bo4 sp06.
Is this possible?
Is it a generic setting such that all queries have this or can we customise it such that obly queries not involving a mesaure have distinct operator.
Thanks
Philip
Hi Philip,
below are the options that you can use to get Distinct in your query, that should solve your problem
ADD DISTINCT keyword in the SQL expression of the object in IDT ex:DISTINCT Table.CATEGORY
Use Custom Query script option on Query script viewer of Query panel and add DISTINCT explicitly in the query to get the required result
Create Derived Table or DB view with Select DISNTICT of required columns and use the objects from Derive Table/View in the report query
Thanks
Mallik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thanks for advice.
Appreciate this would work but would mean multiple distincts would appear in sql - just want one distinct in the select statement as that enough if say
select distinct fld1, fld2 etc.
Would have hoped DISTINCT_VALUES with setting of DISTINCT would have caused it to be inferred.
Would ideally like to have this set at univers elevel.
Thanks
Hi Philip,
Try to create required objects with distinct in object definition as
for e.g. Product_num as select distinct (product_num) from ****************
Product_name as select distinct (product_name) from **************** and use them in reports.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.