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

Thank you very much for replying to the question that I had posed on the Google D-list. You have hit the nail on its head - That's what I am trying to do and it is Oracle 9

But when I ran the query on my environment, I ran into an error ORA:00904 "table_two"."datefield02" : invalid identifier.

I have slightly modified your query (added a 'distinct') as follows: update table_two
  set field01 = nvl((
  with s as
  (
  select distinct end_date,
  dense_rank() over ( partition by trunc(end_date, 'mon') order by end_date ) r
  from the_table
  )
 select distinct r
 from s
 where s.end_date = table_two.datefield02 ), 0) + 2  /

Earlier I was trying the following query that ran into similar error : ORA 00904 : "table_two":"datefield02" : invalid identifier.

Here is the query that I was trying:
UPDATE table_two
SET field01 =
(
SELECT Sq + 2
FROM (

       SELECT to_char(end_date,'YYYY-MON'), end_date
       , rank() over (partition BY to_char(end_date,'YYYY-MON') ORDER
BY end_date) AS Sq
       FROM the_table 
    WHERE end_date = table_two.end_date
       GROUP BY to_char(end_date,'YYYY-MON'), end_date 
     )

Thank you in advance.

seaelephant_at_hotmail.com (dx) wrote in message news:<7f28ac37.0312110933.6cfe8503_at_posting.google.com>...
> 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
> BB 2003-aug-15 00:00:00
> BB 2003-aug-29 00:00:00
>
> SQL> select * from table_two;
>
> FIELD01 DATEFIELD02
> ---------- --------------------
> 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 Mon Dec 15 2003 - 20:43:06 CET