Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 10g, AIX 5.3)
Query help [message #546886] Fri, 09 March 2012 12:10 Go to next message
sspn2010
Messages: 147
Registered: October 2008
Senior 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 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
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 #570872 is a reply to message #546891] Fri, 16 November 2012 13:51 Go to previous messageGo to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Thank you for your help.

Regards
Sri
Re: Query help [message #570878 is a reply to message #546891] Sat, 17 November 2012 03:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
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

Re: Query help [message #570884 is a reply to message #570879] Sat, 17 November 2012 05:58 Go to previous messageGo to next message
Rasoul_57
Messages: 8
Registered: October 2012
Location: Iran
Junior Member
Why are you angry ?
Oracle 10g

Query Without no restriction returned 5 row

your query retuned 1 row

i use pl/sql developer 9.0.6.1665

./fa/10517/0/
  • Attachment: 1.JPG
    (Size: 109.94KB, Downloaded 222 times)
Re: Query help [message #570885 is a reply to message #570884] Sat, 17 November 2012 06:00 Go to previous message
Rasoul_57
Messages: 8
Registered: October 2012
Location: Iran
Junior Member
You are right

[Updated on: Sat, 17 November 2012 06:11]

Report message to a moderator

Previous Topic: UTL_MAIL package
Next Topic: SQL Error: ORA-00911: invalid character - HELP!
Goto Forum:
  


Current Time: Mon Dec 22 23:31:51 CST 2014

Total time taken to generate the page: 0.08505 seconds