Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 10g, AIX 5.3)
| Query help [message #546886] |
Fri, 09 March 2012 12:10  |
sminnakanti
Messages: 73
Registered: October 2008
|
Member |
|
|
Hi,
I need help on writing query for one of my requirement. I've below table with 5 records. Now for each col*_category i need to populte it with previous record value.
For example if the first record has the COL1_CATEGORY as 'ACCU-CHECK' then i need to populate the value for the other records with rsp_dt greater than 1st record.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Connected as rochprod
SQL>
SQL> create table TEST_RECS
2 (
3 indiv_id NUMBER,
4 promo_rsp_id NUMBER,
5 rsp_dt DATE,
6 col1_category VARCHAR2(50),
7 col2_category VARCHAR2(50),
8 col3_category VARCHAR2(50)
9 );
Table created
SQL> insert into TEST_RECS (INDIV_ID, PROMO_RSP_ID, RSP_DT, COL1_CATEGORY, COL2_CATEGORY, COL3_CATEGORY)
values (1, 1, to_date('01-01-2008', 'dd-mm-yyyy'), 'ACCU-CHEK', '', '');
1 row inserted
SQL> insert into TEST_RECS (INDIV_ID, PROMO_RSP_ID, RSP_DT, COL1_CATEGORY, COL2_CATEGORY, COL3_CATEGORY)
values (1, 2, to_date('01-01-2009', 'dd-mm-yyyy'), '', '', '2+ PER DAY');
1 row inserted
SQL> insert into TEST_RECS (INDIV_ID, PROMO_RSP_ID, RSP_DT, COL1_CATEGORY, COL2_CATEGORY, COL3_CATEGORY)
values (1, 3, to_date('01-01-2010', 'dd-mm-yyyy'), '', 'INSULIN', '');
1 row inserted
SQL> insert into TEST_RECS (INDIV_ID, PROMO_RSP_ID, RSP_DT, COL1_CATEGORY, COL2_CATEGORY, COL3_CATEGORY)
values (1, 4, to_date('01-06-2009', 'dd-mm-yyyy'), '', '', '');
1 row inserted
SQL> insert into TEST_RECS (INDIV_ID, PROMO_RSP_ID, RSP_DT, COL1_CATEGORY, COL2_CATEGORY, COL3_CATEGORY)
values (1, 5, to_date('01-01-2011', 'dd-mm-yyyy'), '', '', '');
1 row inserted
SQL> select * from test_recs order by 1,3;
INDIV_ID PROMO_RSP_ID RSP_DT COL1_CATEGORY COL2_CATEGORY COL3_CATEGORY
---------- ------------ ----------- ---------------- ---------------- --------------------
1 1 1/1/2008 ACCU-CHEK
1 2 1/1/2009 2+ PER DAY
1 4 6/1/2009
1 3 1/1/2010 INSULIN
1 5 1/1/2011
SQL>
My Output has to be like this:
INDIV_ID PROMO_RSP_ID RSP_DT COL1_CATEGORY COL2_CATEGORY COL3_CATEGORY
-------------------------------------------------------------------------------
1 1 1/1/2008 ACCU-CHEK
1 2 1/1/2009 ACCU-CHEK 2+ PER DAY
1 4 6/1/2009 ACCU-CHEK 2+ PER DAY
1 3 1/1/2010 ACCU-CHEK INSULIN 2+ PER DAY
1 5 1/1/2011 ACCU-CHEK INSULIN 2+ PER DAY
Can we do this with sql query or do i need to write pl/sql procedure. Appreciate your help.
Thanks
Sri
[Updated on: Fri, 09 March 2012 14:23] by Moderator Report message to a moderator |
|
|
|
| Re: Query help [message #546891 is a reply to message #546886] |
Fri, 09 March 2012 14:32  |
 |
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please post the test case ONLY, not its execution (but verify it executes correctly).
SQL> select indiv_id, promo_rsp_id, rsp_dt,
2 last_value(col1_category ignore nulls) over (order by rsp_dt) col1_category,
3 last_value(col2_category ignore nulls) over (order by rsp_dt) col2_category,
4 last_value(col3_category ignore nulls) over (order by rsp_dt) col3_category
5 from TEST_RECS
6 /
INDIV_ID PROMO_RSP_ID RSP_DT COL1_CATEGORY COL2_CATEGORY COL3_CATEGORY
---------- ------------ ----------- ------------- ------------- -------------
1 1 01-JAN-2008 ACCU-CHEK
1 2 01-JAN-2009 ACCU-CHEK 2+ PER DAY
1 4 01-JUN-2009 ACCU-CHEK 2+ PER DAY
1 3 01-JAN-2010 ACCU-CHEK INSULIN 2+ PER DAY
1 5 01-JAN-2011 ACCU-CHEK INSULIN 2+ PER DAY
Regards
Michel
[Updated on: Fri, 09 March 2012 14:34] Report message to a moderator |
|
|
|
|
|
| Re: Query help [message #570878 is a reply to message #546891] |
Sat, 17 November 2012 03:45 |
 |
Rasoul_57
Messages: 8
Registered: October 2012
Location: Iran
|
Junior Member |
|
|
the Rseult of (select indiv_id, promo_rsp_id, rsp_dt,
last_value(col1_category ignore nulls) over (order by rsp_dt) col1_category,
last_value(col2_category ignore nulls) over (order by rsp_dt) col2_category,
last_value(col3_category ignore nulls) over (order by rsp_dt) col3_category
from TEST_RECS)
is :
INDIV_ID PROMO_RSP_ID RSP_DT COL1_CATEGORY COL2_CATEGORY COL3_CATEGORY
---------- ------------ ----------- ------------- ------------- -------------
1 1 01-JAN-2008 ACCU-CHEK
not
INDIV_ID PROMO_RSP_ID RSP_DT COL1_CATEGORY COL2_CATEGORY COL3_CATEGORY
---------- ------------ ----------- ------------- ------------- -------------
1 1 01-JAN-2008 ACCU-CHEK
1 2 01-JAN-2009 ACCU-CHEK 2+ PER DAY
1 4 01-JUN-2009 ACCU-CHEK 2+ PER DAY
1 3 01-JAN-2010 ACCU-CHEK INSULIN 2+ PER DAY
1 5 01-JAN-2011 ACCU-CHEK INSULIN 2+ PER DAY
I Confused
[Updated on: Sat, 17 November 2012 03:47] Report message to a moderator |
|
|
|
| Re: Query help [message #570879 is a reply to message #570878] |
Sat, 17 November 2012 04:08 |
|
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Do you mean that I faked the output?
How a query without no restriction could return less rows that the table itself?
Post your Oracle version with 4 decimals.
Use SQL*Plus and copy and paste your session including the result of "select * from TEST_RECS;"
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
[Updated on: Sat, 17 November 2012 04:09] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 24 12:07:22 CDT 2013
Total time taken to generate the page: 0.09874 seconds
|
] 
Members
Search
Help
Register
Login
Home
