Multiple selections from a single object

[funkmonsteruk]

I am working with BO on a database which utilises a diary. Basically the diary has several elements including a date and an event. These are linked to a 'case'

The problem i have is that i need to report on two types of event from the diary for each case.

The two events 'a' and 'b' are to be used as follows

i want to display all cases with an event 'a' (which occurs infrequently) and i want to show the date of event 'a'

But i also want to show the date at which event event 'b' occured (all estates with event 'a' will also have an event 'b')

I have tried using event = 'a' or event = 'b' but this shows cases where event 'b' only has occured as well as cases where event 'a' has occured.

Can anybody help

Funkmonster

 

[blom0344]

Hello Funk...

I think you will have to resort to some free-hand SQL in this case.

Part 1:

Selecting only combinations of 'B' where corresponding 'A' exists:

Select T2.CaseT2,T2.EventT2,T2.DateT2 from
(Select Case as CaseT1,Event as EventT1, Date as DateT1
from T where Event = 'A' ) T1,
(Select Case as CaseT20Event as EventT2, Date as DateT2
from T where Event = 'B' ) T2
Where T1.CaseT1 = T2.CaseT2

Part 2:

Selecting all combinations of 'A':

Select T.Case,T.Event,T.Date from T where T.Event = 'A'


Part 3: Combining both with the UNION operator (removes duplicate combinations from the subqueries:


(Select T2.CaseT2,T2.EventT2,T2.DateT2 from
(Select Case as CaseT1,Event as EventT1, Date as DateT1
from T where Event = 'A' ) T1,
(Select Case as CaseT20Event as EventT2, Date as DateT2
from T where Event = 'B' ) T2
Where T1.CaseT1 = T2.CaseT2)
UNION
(Select T.Case,T.Event,T.Date from T where T.Event = 'A')

But maybe there is some input to deal with this at the report level.....
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[promero]

You don't need that complexity, bloom0344!

I think this query does the job:
[tt]
Select Case, Event, Date From T
Where Event in ('A', 'B')
And Case in (Select Case From T
Where Event = 'A')
[/tt]
You can easily construct this query in the query panel without recurring to free-hand SQL, if you are allowed to make subqueries.

Another solution is to Select all from A and B ([tt]Select Case, Event, Date From T Where Event in ('A', 'B')[/tt]) and then add a filter at the top of the report to the variable <Case> with the expression =<Event>=&quot;A&quot;.

 

[blom0344]

You're right about the SQL solution being too complex. That happens when you solve a couple of issues in one way, you loose sight on other strategies (such as a simple subselect , in this case) There always someone to kick your ... , in a open forum. Have a look at your solution at the report level using filters. That will not give the right effect , I think. Setting a filter like you mention won't give you any information any more about the 'B' type events (kick my ...., if I'm off the mark)

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

 

[promero]

I think you are off the mark

Note that the filter is applied over <Case>, not over <Event>, although it's expression uses <Event>. The filter will let pass all values of <Case> for which there is some <Event>=&quot;A&quot;.

 

[blom0344]

Ouch,
Think I'm going to cut down time on these forum-activities and sample some nasty financial report issues for the likes of Promero........ T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com