Help needed in Update SQL that uses an Inline Query View containing 'Partition By'

From: Surendra <surendramuthye_at_yahoo.com>
Date: 5 Dec 2003 09:31:11 -0800
Message-ID: <742a5eb5.0312050931.5217b1ac_at_posting.google.com>

I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq

---

Inline View Query:
Select Sq
from
(
Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
From The_Table
Group by substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date where substr(to_date(End_Date,"DD-MON-YYYY"),4)= "Jun-2003"
)

Note : To see what the query returns, sample data with results are provided below

---

Update statement that I am trying:

UPDATE Table_Two
SET A.field01 =
Select Sq + 2
From
(
Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
From The_Table
where End_Date= A.datefield02
Group by substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date
)

WHERE A.datefield02 is not null

ie. I would like to set field01 of table A by the value (Sq + 2) where Sq is returned by the Inline View by picking up only that record from the The_table that has End_Date = A.datefield02.

This update has to run for all records of Table_Two

All help will be appreciated.

---

Sample Data and Inline View Query results:

/* *********************** */

I have a table that has fields GRP_CODE & END_DATE. For a given GRP_CODE and a given month of a year, there may be 1, 2 or 3 END_DATE values.
For Example,

GRP_CODE          END_DATE
AA                07/14/2003
AA                07/28/2003
BB                07/14/2003
BB                07/28/2003
AA                08/01/2003
AA                08/15/2003
AA                08/29/2003
BB                08/01/2003
BB                08/15/2003
BB                08/29/2003

...

I have to develop a query that has one input parameter, a date. The query should return either 0, 1, 2, or 3 based on the match with the END_DATE field.

In the above example, here is what the query should return the following results for various input dates

Input Date        Query Output
08/01/2003             1
08/15/2003             2
08/29/2003             3
08/30/2003             0
07/14/2003             1
07/28/2003             2
/* ***************************************** */

Received on Fri Dec 05 2003 - 18:31:11 CET