Query max(status_date) hangs

[kburrows]

Hi all,

I am working on a query to find the max_status date with several requirements. I have tried it every which way I can. Need some help!

Here is my current version: It just hangs, never completes. You can see I'm using some In-line views and I tried to separate them for easy reading. Thanks, Kathy

Select
Pa0.id,
Pa0.status_date,
Pa0.n_number,
Pa0.fk_sourcenumber,
Pa0.fk_appli_subtypeno,
Ilv_atr.description atr_description,
Ilv_ast.n_number ast_number,
Ilv_ast.description ast_description

From permit_application pa0,

(Select pa.fk_sourcenumber, pav.fk_permit_applinum, pav.fk_permit_summanum
from permit_application pa, permit_attr_value pav
where pa.n_number = pav.fk_permit_applinum
and pav.fk_permit_summanum = ‘PREV’) ilv_pav,

(Select pa.fk_sourcenumber, pa.fk_application_num, atr.n_number, atr.description, pa. fk_appli_subtypeno
from application_type_r atr, permit_application pa
where atr.n_number = pa.fk_application_num
and atr.description in ('Title V', 'FESOP', 'MSOP', 'SSOA', 'Conversion')) ilv_atr,

(Select pa.fk_sourcenumber, ast.n_number, ast.description
from permit_application pa, application_subtyp ast
where pa.fk_appli_subtypeno = ast.n_number(+)
and (pa.fk_appli_subtypeno is null or pa.fk_appli_subtypeno in
(6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))) ilv_ast

where
pa0.status0 = 'Issued'
and
pa0.fk_sourcenumber = ilv_pav.fk_sourcenumber
and
pa0.fk_sourcenumber = ilv_atr.fk_sourcenumber
And
Pa0.fk_sourcenumber = ilv_ast.fk_sourcenumber
and
(pa0.status_date = (select max(pa1.status_date)
from permit_application pa1
where (pa0.fk_sourcenumber = pa1.fk_sourcenumber)))

[slimdave]

How about changing ...

...
From permit_application pa0,
...

... to ...

From
   (
   Select
      id,
      status_date,
      n_number,
      fk_sourcenumber,
      fk_appli_subtypeno
   From
      (
      Select
         id,
         status_date,
         n_number,
         fk_sourcenumber,
         fk_appli_subtypeno,
         Max(status_date) Over 
           (Partition By fk_sourcenumber)
            max_status_date
      From
         permit_application
      )
   Where
      status_date = max_status_date
   ) pa0
...

...and dropping the correlated subquery?

[kburrows]

Thanks for the reply.

I am getting way too many rows returned. Returning multiple rows for the status_date, also including fk_appli_subtypeno with incoreect values (11, etc) Here is what I run last. Any more ideas?

Select
Pa0.id,
Pa0.status_date,
Pa0.n_number,
Pa0.fk_sourcenumber,
Pa0.fk_appli_subtypeno,
Ilv_atr.description atr_description,
Ilv_ast.n_number ast_number,
Ilv_ast.description ast_description
From

( Select id, status_date, n_number, fk_sourcenumber, fk_appli_subtypeno, status0 from
( Select id, status_date, n_number, fk_sourcenumber, fk_appli_subtypeno, status0, Max(status_date) Over (Partition By fk_sourcenumber) max_status_date From permit_application ) Where status_date = max_status_date ) pa0 ,

(Select pa.fk_sourcenumber, pav.fk_permit_applinum, pav.fk_permit_summanum
from permit_application pa, permit_attr_value pav
where pa.n_number = pav.fk_permit_applinum
and pav.fk_permit_summanum = ‘PREV’) ilv_pav,

(Select pa.fk_sourcenumber, pa.fk_application_num, atr.n_number, atr.description, pa. fk_appli_subtypeno
from application_type_r atr, permit_application pa
where atr.n_number = pa.fk_application_num
and atr.description in ('Title V', 'FESOP', 'MSOP', 'SSOA', 'Conversion')) ilv_atr,

(Select pa.fk_sourcenumber, ast.n_number, ast.description
from permit_application pa, application_subtyp ast
where pa.fk_appli_subtypeno = ast.n_number(+)
and (pa.fk_appli_subtypeno is null or pa.fk_appli_subtypeno in
(6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))) ilv_ast

where
pa0.status0 = 'Issued'
and
pa0.fk_sourcenumber = ilv_pav.fk_sourcenumber
and
pa0.fk_sourcenumber = ilv_atr.fk_sourcenumber
And
Pa0.fk_sourcenumber = ilv_ast.fk_sourcenumber

[slimdave]

This code ...

Select
      id,
      status_date,
      n_number,
      fk_sourcenumber,
      fk_appli_subtypeno
   From
      (
      Select
         id,
         status_date,
         n_number,
         fk_sourcenumber,
         fk_appli_subtypeno,
         Max(status_date) Over 
           (Partition By fk_sourcenumber)
            max_status_date
      From
         permit_application
      )
   Where
      status_date = max_status_date

... will return just the records from the permit_application table for which the status date matches the highest status date for each fk_source_number ... is that the set that you're interested in?

Also, why all the in-lineviews? why not merge them into the main query?

[kburrows]

I need max_data from the permit app table that also meets the other conditions. I took out the ilv's and now I get 1512 rows. I am going to check with the program area, because I had this figure 1512 rows earlier with a different query, but they seem to think the figure is too low. Do you see anything else I'm missing, let me know? Otherwise, thanks so much for your help. Kathy

I now have:

Select id, status_date, pa.n_number, pa.fk_sourcenumber, pa.fk_appli_subtypeno, pa.status0, pa.fk_application_num
From ( Select id, status_date, n_number, fk_sourcenumber, fk_appli_subtypeno, status0, fk_application_num, Max(status_date) Over (Partition By fk_sourcenumber) max_status_date From permit_application) pa,
permit_attr_value pav,
application_type_r atr,
application_subtyp ast
Where status_date = max_status_date
And pa.n_number = pav.fk_permit_applinum
And pa.fk_application_num = atr.n_number
And pa.fk_appli_subtypeno = ast.n_number(+)
And pa.status0 = ‘Issued’
And pav.fk_permit_summanum = ‘PREV’
And (pa.fk_appli_subtypeno is null or pa.fk_appli_subtypeno in
(6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))

[DaPi]

Obviously we don't know what it is supposed to do . . . . You might want to check the logic on:

And pa.status0 = ‘Issued’
And (pa.fk_appli_subtypeno is null or pa.fk_appli_subtypeno in
(6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))

Two possibilities:

a) you want to take a row with max(status_date) and ignore it if the above conditions are not met => your query as written.

b) you want to eliminate rows not meeting these conditions before selecting one with max(status_date) => these conditions should go inside the inline view.

[kburrows]

We do know what we want, just don't know how to get it. I want to select all the rows that meet the conditions(first) and then from those rows select the one with the maximum status_date per fk_sourcenumber. (I'll take B)

b) you want to eliminate rows not meeting these conditions before selecting one with max(status_date) => these conditions should go inside the inline view.

Here is the query that I believe returns everything I want, but doesn't select the max status date based on fk_sourcenumber. Also, some sample data. I have tried (max in the select, max subquery in the where, group by and having). Doesn't seem like it should be this difficult.
After having all the rows that meet the conditions, how would you get the max?

select status_date, fk_permit_applinum, pa.n_number, fk_permit_summanum, fk_sourcenumber, id, atr.description, ast.description, ast.n_number
from permit_application pa, permit_attr_value pav, application_type_r atr, application_subtyp ast
where pav.fk_permit_applinum = pa.n_number
and pav.fk_permit_summanum = ‘PREV’
and pa.status0 = ‘Issued’
and pa.fk_application_num = atr.n_number
and atr.description in ('Title V', 'FESOP', 'MSOP', 'SSOA', 'Conversion')
and pa.fk_appli_subtypeno = ast.n_number(+)
and (ast.n_number is null or ast.n_number in (6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))

STATUS_DATE FK_SOURCENUMBER DESCRIPTION Status 0 ID
------------- -------------- --------------- -------- -----
26-JAN-98 71803089 SSOA Issued 2856
02-MAR-01 71803089 SSOA Issued 6556
06-DEC-00 71803089 MSOP Issued 2349

Thanks,
Kathy

[DaPi]

You need to wrap slimdave's analytic function round your select, like:

select * from (
select status_date, fk_permit_applinum, pa.n_number, fk_permit_summanum, fk_sourcenumber, id, atr.description, ast.description, ast.n_number,
Max(status_date) Over (Partition By fk_sourcenumber) max_status_date
from permit_application pa, permit_attr_value pav, application_type_r atr, application_subtyp ast
where pav.fk_permit_applinum = pa.n_number
and pav.fk_permit_summanum = ‘PREV’
and pa.status0 = ‘Issued’
and pa.fk_application_num = atr.n_number
and atr.description in ('Title V', 'FESOP', 'MSOP', 'SSOA', 'Conversion')
and pa.fk_appli_subtypeno = ast.n_number(+)
and (ast.n_number is null or ast.n_number in (6,7,90,91,22,8,9,92,93,23,100,98,76,77,83,51,50))
)
Where status_date = max_status_date;

[kburrows]

It doesn't seem to like the select *

ERROR at line 1:
ORA-00918: column ambiguously defined

any thoughts?

[slimdave]

You have two columns named "description" in the in-line view.