"Complex" Query Issues 1

[appealdenied]

I have a table with a list of companies. Each company has multiple task names associated with it and dates at which time the task was completed. I am trying to write a query that will provide the name of the company and the task name for the most recent date posted. The data below should provide an example of what I am seeing (starred fields should appear in the result)

COMPANY TASK DATE
CPY A Validate Data 07/06/2007
CPY A Setup Data 07/09/2007 ***
CPY A Publish Data 07/03/2007
CPY B Validate Data 07/08/2007
CPY B Setup Data 07/09/2007
CPY B Publish Data 07/09/2007
CPY B Validate Data 07/10/2007 ***

Any assistance would be greatly appreciated.

"Life is like a purple antelope walking through a sea of dead tuna."

 

[SantaMufasa]

First, Date is an illegal column name in Oracle since is a reserved keyword.

Here is a query that gives you the results for which you are looking:

select company, task, dt
from company_task
where (company,dt) in (select company, max(dt)
                        from company_task
                       group by company)
/

COMPANY    TASK                 DT
---------- -------------------- ---------
CPY A      Setup Data           09-JUL-07
CPY B      Validate Data        10-JUL-07

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[appealdenied]

(Of course DATE is reserved, this is in pseudocode )

Excellent answer!
I don't think I ever recall being able to pass multiple parameters to IN subqueries. That piece of code made the difference.

Thank you.

"Life is like a purple antelope walking through a sea of dead tuna."

 

[appealdenied]

While that was a very good answer, I need to change the scenario slightly.

So how would I build this query if I needed to grab the company name, earliest date and the task assigned with the most recent date?

When I try adding an AND clause to the main query it greatly restricts the number of records it returns.

Any ideas?

"Life is like a purple antelope walking through a sea of dead tuna."

 

[SantaMufasa]

Appeal,

Here is adjusted code that does, I believe, what you want:

select a.company, a.task, a.dt task_dt, b.mindt Min_Dt
from company_task a
    ,(select company, min(dt) mindt from company_task
       group by company) b
where (a.company,a.dt) in (select company, max(dt)
                             from company_task
                            group by company)
  and a.company = b.company
/

COMPANY    TASK                 TASK_DT   MIN_DT
---------- -------------------- --------- ---------
CPY A      Setup Data           09-JUL-07 03-JUL-07
CPY B      Validate Data        10-JUL-07 08-JUL-07

If this is not what you want, please advise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[appealdenied]

Well...at initial inspection it might do exactly what I want.

However, I am (restricted to) using MS Query and Excel 2000 for reporting. I get the same result when using either of the Microsoft Oracle ODBC and Oracle ODBC (8.1.7) Drivers


Just to make sure that the query is correct, here it is...

SELECT a.counterparty_name, a.request_status, a.external_system_name||'--'||a.task_name, a.submitted_by, b.date_acquired
FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY a,
	(SELECT counterparty_name, min(date_acquired)
	FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
	GROUP BY counterparty_name) b
WHERE (a.counterparty_name, a.date_completed) in
	(SELECT counterparty_name, max(date_completed) 
	FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
	GROUP BY counterparty_name)
AND a.company = b.company

Unfortunately, I get a message saying...
"Could not add the table '(SELECT'


I might be able to get someone else to run it tomorrow.
Let me know what other information you need.

"Life is like a purple antelope walking through a sea of dead tuna."

 

[appealdenied]

the AND clause should read

a.counterparty_name = b.counterparty_name

Not that it changes anything.

"Life is like a purple antelope walking through a sea of dead tuna."

 

[SantaMufasa]

Appeal,

I haven't the software in which you are trying to run your code, but the error message appears to say that it doesn't like "in-line views" (e.g., "FROM...(SELECT..."), where Oracle has no problem with such code. If you want to confirm that that is the case, then first run this code:

create or replace view b as
SELECT counterparty_name, min(date_acquired)
  FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
 GROUP BY counterparty_name;

Then, modify your query, above, to read:

SELECT a.counterparty_name, a.request_status, a.external_system_name||'--'||a.task_name, a.submitted_by, b.date_acquired
FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY a, b
WHERE (a.counterparty_name, a.date_completed) in
    (SELECT counterparty_name, max(date_completed) 
    FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
    GROUP BY counterparty_name)
AND a.company = b.company;

Try this and let us know the result.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[appealdenied]

Well, as I found out, ODBC does not support inline views. That is why the query didn't work. I did manage to get a hold of TOAD to continue working on the problem. All the query needed was an alias for the date_acquired column in the inline view.

The query as it stands now looks like this

SELECT 
  a.counterparty_name, a.request_status,   
  a.external_system_name||'--'|| a.task_name,
  a.submitted_by, b.date_acquired, a.date_completed
FROM 
  ECMADM.R_CP_RQ_ONBOARDING_SUMMARY a, 
  (SELECT counterparty_name, min(date_acquired) date_acquired 
   FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
   GROUP BY counterparty_name) b
WHERE (a.counterparty_name, a.date_completed) in
    (SELECT counterparty_name, max(date_completed) 
    FROM ECMADM.R_CP_RQ_ONBOARDING_SUMMARY 
    GROUP BY counterparty_name)
AND a.counterparty_name = b.counterparty_name;

However, things are now a bit more complicated
1) I forgot that the date_completed column does contain null values. Thus, I am not pulling back the most recent record but the most recent record with a date completed.
2) I need to add some additional columns to this table. The columns are request_seq and prodkey

just to give an example of the data structure I am dealing with...

prodkey request_seq counterparty, request_status system_name task_name submitter date_acquired date_completed
123456 100000 company123 COMPLETED RDM Request ABC 23-JUL-2007 25-JUL-2007
123456 100000 company123 COMPLETED RDM Validate ABC 23-JUL-2007 27-JUL-2007
123456 100000 company123 COMPLETED RDM Validate ABC 29-JUL-2007 30-JUL-2007
123456 100000 company123 COMPLETED Sys1 Publish ABC 29-JUL-2007 30-JUL-2007
123456 100000 company123 IN-PROGRESS Sys2 Publish ABC 29-JUL-2007 30-JUL-2007
123456 100001 company123 IN-PROGRESS RDM Request ABC 23-JUL-2007 25-JUL-2007
123456 100001 company123 IN-PROGRESS RDM Validate ABC 23-JUL-2007 25-JUL-2007
123456 100001 company123 IN-PROGRESS RDM Send ABC 23-JUL-2007 NULL
112233 111111 companyABC COMPLETED RDM Request ABC 29-JUL-2007 30-JUL-2007
112233 111111 companyABC COMPLETED RDM Setup ABC 01-AUG-2007 03-AUG-2007
112233 111111 companyABC COMPLETED RDM Validate ABC 03-AUG-2007 04-AUG-2007
112233 111111 companyABC COMPLETED Sys1 Publish ABC 03-AUG-2007 04-AUG-2007
112233 111112 companyABC IN-PROGRESS RDM Request ABC 22-AUG-2007 22-AUG-2007
112233 111112 companyABC IN-PROGRESS RDM Validate ABC 23-AUG-2007 24-AUG-2007
112233 111112 companyABC IN-PROGRESS RDM Reject ABC 24-AUG-2007 24-AUG-2007
112233 111112 companyABC IN-PROGRESS RDM Validate ABC 25-AUG-2007 NULL

The result of the ultimate query should pull back as much data as this (but possibly less depending on my research needs)

prodkey request_seq counterparty, request_status system_name task_name submitter date_acquired date_completed
123456 100000 company123 COMPLETED Sys2 Publish ABC 23-JUL-2007 30-JUL-2007
123456 100001 company123 IN-PROGRESS RDM Send ABC 23-JUL-2007
112233 111111 companyABC COMPLETED Sys1 Publish ABC 29-AUG-2007 04-AUG-2007
112233 111112 companyABC IN-PROGRESS RDM Validate ABC 22-AUG-2007

Any new wildly succsesful ideas?

"Life is like a purple antelope walking through a sea of dead tuna."