Condition problem when using max dates 1

[DrSmyth]

While i'm hooked up to the internet, thought i'd ask another one which is causing me some probs...

I have a BO hooked upto a db2 database and one of the objects in my universe is a timestamp in a diary, i have prefixed the object in the universe with the date function DATE(Timestamp) and i have set the object as a measure to show the Max item...

Now i'm using this object in a report criteria and it's causing me a few problems, basically if the max date doesn't fit the criteria that i have imposed the bo reports on one of the previous entries which isn't a max date.

EG...
b - 1 - 02/01/2003
a - 1 - 01/01/2003
b - 2 - 05/01/2003
a - 2 - 07/01/2003

If i impose a condition that maxdate is less than 06/01/2003 then i would only expect b -2 to be returned (as a's max date is after criteria), however both b - 2 and a -1 are being returned...

Any ideas??

 

[Subhashab]

Have you mentioned max in the sql definition of the measure object.

And where are u applying the filter in the DP or BO (BO u you cannot apply filter of summary varaibles created in BO.

 

[DrSmyth]

Hi,

I have simply dragged the field from the table to the object panel in Designer and then I've set it up as a measure with MAX as it's aggregate behaviour.

Are you saying that i should add some sql to the object in designer?

I'm applying the filter in BO eg: Max Date(Timestamp) within user prompt

 

[Subhashab]

your filter clause is generated using the having clause so make sure that the dimension selected is ok.

 

[blom0344]

This problem is purely SQL related. If you use max (or any other aggregate) it will performed OVER the set of dimensions you are retrieving in your SQL. So if you retrieve the object that is set as max(...) along with another dimension more than one row will be produced. (If that dimension holds more than one value)
Working with DB2 as well, I suggest you may want to look into new functions like RANK() which offer very elegant solutions.

A very good source for examples can be found in Graeme Birchall SQL Cookbooks for DB2:

http://ourworld.compuserve.com/homepages/graeme_birchall/HOMEPAGE.HTM

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Since it is a SQL Related one how 'bout an Having Clause to accomplish the task..

Sri

 

[DrSmyth]

Been looking for a reference like this for ages T.Blom... Cheers......

Been having trouble getting an SQL statement that somebody gave me for a DB2 database into a designer object, don't spose you could shed some light on the matter???? It goes like this:

SELECT WILL_COD FROM PSC_PSC_W_WILL_V

WHERE MIRR_WILL_COD IN

(SELECT MIRR_WILL_COD FROM PSC_PSC_W_WILL_V GROUP BY MIRR_WILL_COD HAVING COUNT(*) > 1)

Any ideas.....????

 

[blom0344]

Yes,

The SQL will automatically generate a group by clause if you retrieve a dimension and and aggregate.

In your example you want a list of MIRR_WILL_COD 's that occur more than once.

So create an object Count(MIRR_WILL_COD). Use this in conditions panel:

Count(MIRR_WILL_COD) > 0
The SQL that BO creates will be:

Select MIRR_WILL_COD from PSC_PSC_W_WILL_V
group by MIRR_WILL_COD having count(MIRR_WILL_COD) > 1

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Cheers T.Blom...

 

[DrSmyth]

Blom, I'm trying to create a COUNT(MIRR_WILL_COD) measure in designer, I've put COUNT(MIRR_WILL_COD) into the select box and I've set it's aggregate function to sum, but when i parse it i get the following error message..
[ibm][cli driver][db2]sql0409n the operand of a count function is not valid sqlstate=42607{sqlexecute}

Can i just create an object with mirr_will_cod and set it's aggregate to count, will this do the same job?

 

[blom0344]

Setting the aggregation level to count will have effect at the report level, it will not have effect on the SQL generated.
What are you using for DB2 database, type , version ?

Look at the following explanation on another forum:

http://www.experts-exchange.com/Databases/IBM_UDB/Q_20427697.html#7592172

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

The programers aren't sure which version we're using but they are sure that it's less than version 7. The link you've pointed me to does offer a good explanation as to why i've been experiencing these problems..

But, after reading the article I have now got this to work. I created a COUNT(DISTINCT WILLCOD) object and made a condition with a subquery of <mirrWillCod> in list COUNT(DISTINCT WILLCOD) >1

Thanks for your help here, think you deserve another star as this thread has been very useful for me and has covered a couple of issues

 

[blom0344]

Pleased to have been able to help you this way,

Be aware that current version is 8, version 7.1 and 7.2 are widely used. version 7 offers quite a bit more than earlier version and 8 offers new stuff and totally new set of tools.

Versions 6 and lower are no longer supported anyway.



T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Back to the original max date problem, I have finally managed to find a solution and it was actually quite simple in the end.

Instead of using a simple condition for the max date (date >= current date - 1 month)i created a notinlist sub query. The subquery just gave me all the data that met the exclusion criteria.

Seems to work fine.... Thanks for all the help though folks.....