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

I'm not sure if the following is what you're looking for (suppose you're working on oracle 9i):

SQL> select * from the_table;

GR END_DATE

-- --------------------
AA 2003-jul-14 00:00:00
AA 2003-jul-28 00:00:00
BB 2003-jul-14 00:00:00
BB 2003-jul-28 00:00:00
AA 2003-aug-01 00:00:00
AA 2003-aug-15 00:00:00
AA 2003-aug-29 00:00:00
BB 2003-aug-01 00:00:00

---------- --------------------
           2003-aug-01 00:00:00
           2003-aug-30 00:00:00
           2003-aug-15 00:00:00
           2003-aug-29 00:00:00

SQL> update table_two
  2 set field01 = nvl((
  3 with s as
  4 (
  5 select end_date,
  6 dense_rank() over ( partition by trunc(end_date, 'mon') order by end_date ) r
  7 from the_table
  8 )
  9 select distinct r
 10 from s
 11 where s.end_date = table_two.datefield02  12 ), 0) + 2
 13 /

4 rows updated.

SQL> select * from table_two;

   FIELD01 DATEFIELD02

---------- --------------------
         3 2003-aug-01 00:00:00
         2 2003-aug-30 00:00:00
         4 2003-aug-15 00:00:00
         5 2003-aug-29 00:00:00

surendramuthye_at_yahoo.com (Surendra) wrote in message news:<742a5eb5.0312091557.1f695c1_at_posting.google.com>...
> 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 Thu Dec 11 2003 - 18:33:17 CET