I apologize if I'm posting this question in the wrong forum.
I have a query that exports data from an Oracle database to an Excel spreadsheet. The application that executes the query is Computer Associates Eureka Report writer. I do not have direct access to the database or any of its objects. With the data in Excel, I run a VBA macro-driven inventory report. The Excel spreadsheet row limitation is not a problem presently, however, the query exports a lot of extraneous data that I don't need. I actually export about a dozen columns of data however I'm only listing 4 in the example below.
Example:
CaseNum ActionDate ActionType ComplCd
1029901 09/08/2006 F 0
1029901 09/11/2006 C 0
1029901 08/18/2006 C 1
1029901 08/17/2006 F 1
1029901 08/01/2006 F 1
When the ComplCd = 1 I only want the query to export one row of data for each CaseNum. Is
there a way to code my query to accomplish this?
ugabulldog
Depending on the version of Oracle server, you can use one of the queries below:
select CaseNum, ActionDate, ActionType, CompICd
from (
select CaseNum, ActionDate, ActionType, CompICd
, ROW_NUMBER() OVER(PARTITION BY CaseNum, CompICd ORDER BY ActionDate DESC?) as seq
from tbl
) t
where (CompICd = 0)
or (CompICd = 1 and t.seq = 1)
-- or
select CaseNum, ActionDate, ActionType, CompICd
from tbl
where CompICd = 0
union all
select CaseNum, ActionDate, ActionType, CompICd
from tbl t1
where CompICd = 1
/*
Assumes that ActionDate is unique for each CaseNum, CompICd combination.
Else use some other appropriate column(s)
*/
and ActionDate = (
select max(t2.ActionDate)
from tbl as t2
where t2.CaseNum = t1.CaseNum and t2.CompICd = 1
)
