Processing the right records in BO

[SimoneC]

Hello all
I have trying to figure this out. I'm required to retrieve records that are dependent on the outcome of the
other records in the same table. Here is an example.

Key1 Key2 data
100 01.01.1999 A
100 12.06.2001 B
100 31.12.2999 Z
200 02.07.2000 D
200 03.06.2001 D
200 12.11.2002 E
200 31.12.2999 Z

I would like to retrieve 100 12.06.2001 B and 200 12.11.2002 E. They are just 1 record before 31.12.2999 Z which is the terminating records for key1.
Any help would be appreciated.
Thank you.


Kind regards
Simone

 

[blom0344]

Hello Simone,

I came up with a solution, which uses both freehand SQl and ranking at report level.
The following freehand SQL (tested on DB2 database) gets rid of the terminating records for KEY1 (the table is simone:


Select distinct SUB1.K,SUB1.D,SUB2.K2 from
(select KEY1 as K,DATA as D from simone
EXCEPT ALL
select KEY1 as K,Max(DATA) as D from simone group by KEY1) SUB1,
(select KEY1 as KK, KEY2 as K2, DATA as DD from simone) SUB2
WHERE SUB1.K = SUB2.KK and SUB1.D = SUB2.DD;

gives the following recordset:

K D K2

100 A 01.01.1999
200 D 02.07.2000
200 D 03.06.2001
100 B 12.06.2001
200 E 12.11.2002


In the report set K as a section.

Create a variable Max_K2 (=Max(<K2>)) in the reporttable
Apply ranking on K2 with top 1 based in the measure Max_K2.

If all works as it should you only see the combinations you want to see.

Can't figure out a way to do this without using freehand SQL, if you expand the SQL even further you might even get the result you want in the recordset. (Get's pretty complicated as it is)
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[promero]

If the ending record of Key1 is always (31.12.2999, Z), then the query of T.Blom can be replaced by:
[tt]
Select Key1, Key2, data From Simone
Where Key2<>'31.12.2999' Or data<>'Z'
[/tt]
You can make Key1 a break instead of a section. Then apply the ranking as T.Blom said.

Alternate query that returns exactly what you want, but a bit weird ;-P
[tt]
Select
Key1,
SubStr(Max(Char(Key2)||data), 1, 10),
SubStr(Max(Char(Key2)||data), 11, 1)
From Simone
Where Key2<>'31.12.2999' Or data<>'Z'
Group by Key1
[/tt]
Do you catch the idea?