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

I am still trying to resolve the query so if anyone can help that will be great .

surendramuthye_at_yahoo.com (Surendra) wrote in message news:<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 Wed Dec 10 2003 - 00:57:55 CET