table of unique values

[maverick00]

Say there is a 1 to many relationship of table A (1) to table B (Many). A person wants a unique list of document ids from A based on multiple choices in table B.

In SQL this would be a simple Select distinct doc_id from A,B where B.item in ('this','that','whatever').

Now, how do I create this in my universe? How does one do a select distinct?

 

[blom0344]

Hello Maverick,

Very simple,

In query options, set rows to "no duplicate rows". This will give you the distinct version of the SQL BO generates! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[skrandel]

Just to be clear. You can't do this in the universe. You do this at query time in the Reporter module.

You might be able to build an object that has in the Select portion:

distinct table.column.

It would only be useful if used alone. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[promero]

BO displays dimension and detail variables with no duplication, independently of duplicate values in the query, providing that you do not check the "avoid row aggregation" in the BO table properties.

Measure variables are aggregated. For example if a column is <Sales> (measure aggregated by Sum), and there are three duplicate rows in the query result with the value 2, the BO table will display only one row with the value 6.

 

[maverick00]

Thanks to everyone responding, but as I read the responses, maybe I am not clear enough with what is needed (or don't understand what the responses are saying).

A distinct list of doc id's is needed in the universe to choose values from other parts of the universe.

Ordinarily, the DBA would create a view in our Oracle database, if the distinct list was going to be used many times, but we have found it very convenient to substitute a query in the from clause in place of the view. Sort of a dynamic view.

SELECT
k.VOUCHERS
FROM
CHECK_TABLE k,
(SELECT DISTINCT
a.DOC_ID
FROM
CONTRACT_TABLE a,
CONTRACT_DETAIL b
WHERE
b.ITEM_CODE in ('115','040','859')) g
WHERE
k.DOC_ID= g.DOC_ID

 

[blom0344]

Hello Maverick,

Could it be that you have forgotten to specify the relationship within the subquery of tables a and b.
The SQL you are showing us is bound to give a carthesian product , me thinks.

So sonmething like:

SELECT
k.VOUCHERS
FROM
CHECK_TABLE k,
(SELECT DISTINCT
a.DOC_ID
FROM
CONTRACT_TABLE a,
CONTRACT_DETAIL b
WHERE
a.*** = b.*** AND
b.ITEM_CODE in ('115','040','859')) g
WHERE
k.DOC_ID= g.DOC_ID

But I still not get the idea behind this. Every LOV , list of values is per definition a distinct one !!
Why not attach a LOV to the DOC_ID and use that?

Rereading your original post, you will get what you want by setting the query options to 'no duplicate rows'. The actual DISTINCT clause is added to the SQL BO generates.
However, distinct performs on records, not on specific fields. If you retrieve other fields than DOC_ID the DISTINCT will look at the UNIQUE records it sees. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[maverick00]

Blom, you amaze me. Yes, my sql was incomplete, my fault, was trying to simplify as much as possible.

If the CONTRACT_TABLE has CONTRACT = '11111'. (one row of header) with item_codes from the CONTRACT_DETAIL table of '115' and '040' (two rows of detail), then the inner script will pull two rows for contract 1, unless the DISTINCT is in the SELECT statement.

If it pulls two rows both with the value of contract '11111' and then it is joined to CHECK_TABLE and there is a single VOUCHER written for CONTRACT '11111' it will also produce 2 rows and give me twice the money.

Help me out here, Blom. I am new to Business Objects. We currently use a sql writer tool. In otherwords, I write a lot of intense sql, hence my solution. Is there a better way? I want the inner script to be a part of the Universe. This situation comes up a lot in different forms.

 

[skrandel]

The real problem is that you want to have a virtual table (or query) in the FROM clause. BO has no way of providing this functionality.

The only solution available to you is to use a subqueries. You could build condition objects that invoke the subqueries.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

Hello Maverick,

Using a definition in a universe limits you to either tables or views. From what I gather from the 3 tables you mention this is a typical fan example (see designer manual for examples) Using universe and one SQL this may prove not possible to do without creating a freehand SQL dataprovider. I can understand you want to stay away from this option for as long as possible, with a lot of reports in mind. How about the option of turning your SQL (but then a generic one, without the filters) into a database view and using this in the universe?

The partial solution of the Fan trap is available when you only select measures in the query from contract_detail and check_table (Multiple SQL statementes for measures,SQL page in Universe parameters) This may not the case with your problem.

Do you have the means to create a custom made view in your database and incorporate this into the universe you are using?

Steve Krandel' s remark about subqueries may be another workable strategy, but I can not come up with a direct example to solve it that way. Perhaps Steve ......

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com