By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
/* ***************************************** */
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I am still trying to resolve the query so if anyone can help that will be great
..

su************@yahoo.com (Surendra) wrote in message news:<74**************************@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
/* ***************************************** */

Jul 19 '05 #2

P: n/a
dx
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

su************@yahoo.com (Surendra) wrote in message news:<74*************************@posting.google.c om>...
I am still trying to resolve the query so if anyone can help that will be great
.

su************@yahoo.com (Surendra) wrote in message news:<74**************************@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
/* ***************************************** */

Jul 19 '05 #3

P: n/a
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
)
)
WHERE table_two.end_date IS NOT NULL
/

Thank you in advance.

se*********@hotmail.com (dx) wrote in message news:<7f**************************@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

su************@yahoo.com (Surendra) wrote in message news:<74*************************@posting.google.c om>...
I am still trying to resolve the query so if anyone can help that will be great
.

su************@yahoo.com (Surendra) wrote in message news:<74**************************@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
/* ***************************************** */

Jul 19 '05 #4

P: n/a
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
)
)
WHERE table_two.end_date IS NOT NULL
/

Thank you in advance.

se*********@hotmail.com (dx) wrote in message news:<7f**************************@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

su************@yahoo.com (Surendra) wrote in message news:<74*************************@posting.google.c om>...
I am still trying to resolve the query so if anyone can help that will be great
.

su************@yahoo.com (Surendra) wrote in message news:<74**************************@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
/* ***************************************** */

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.