ONE-TO-MANY : Only want MAX record from second table in link

[MJRBIM]

ORACLE 9i, CR-10

I'm trying to link two tables with a one-to-many relationship, but I only want a one-to-one return on the dataset using the MAX of the time field. This is to assist with performance of the reports.

Our DBAs preference is to do this without creating and views or stored procedures on ORACLE.

Sample tables are below -

TABLE A
========
RECORD,DATE
1,2006/01/01
2,2006/04/05

TABLE B
=======
RECORD,DATE,TIME
1,2006/01/01,07:00
1,2006/01/01,08:00
1,2006/01/01,09:00
2,2006/04/05,07:00
2,2006/04/05,08:00
2,2006/04/05,09:00
2,2006/04/05,10:00

------------------------

DESIRED RESULTS - Only the MAX time for each RECORD and DATE.
===============
RECORD,DATE,TIME
1,2006/01/01,09:00
2,2006/04/05,10:00


------------------------

Thanks in advance for the advice...!

 

[synapsevampire]

Suggest to your IT Manager that they find a more competent dba.

What do you want if there aren't any rows in the child table?

The ugly Crystal solution, and no doubt prefered by second rate Oracle dba's everywhere is to group by whatever entity these dates exist for, and in the Report->Selection Formulas->Group place:

{table2.date} = maximum({table2.date},{table.group})

This is a bad solution though, as all rows will be returned to Crystal, it simply suppresses after the fact.

You can also use a Add Command under the database connection to paste in SQL to do the same, but I would suggest creating reusable objects on the database, such as Views and SPs, more readily accomplished by squirting a quart of lightining fast acting superglue on your dba whilst he/she is seated and then lighting their desk on fire.

-k

 

[MJRBIM]

There are always rows in the child table.

I will try the "ugly Crystal solution" with the expectation that it will not improve performance....then go back to the DBAs.

Thanks!

 

[lbass]

I would try the add command approach first. Something like:

Select max(table.`time`),table.`date`, table.`record`
From `table`table
Group by table.`record`, table.`date`

Then link the command to TableA on both record and date, and enforce the joins in link options (if that is an option in CR10).

-LB