Solved

SQL Retrieve Values

Posted on 2016-11-22
4
41 Views
Last Modified: 2016-11-29
Hi - My data is like below

Col1   COL2                 col3
A      12-Apr-13      12-APR-16 09.27.20.000000000 AM
B      12-Apr-13      12-APR-16 09.27.34.000000000 AM
A      12-Apr-13      13-APR-16 09.27.34.000000000 AM
A      14-May-13      15-APR-16 09.17.31.000000000 AM
B      14-May-13      15-APR-16 09.17.38.000000000 AM
A      22-May-13      28-APR-16 09.24.14.000000000 AM
B      4-Jun-13              10-MAY-16 11.28.24.000000000 AM
A      14-Jun-13      25-MAY-16 09.30.45.000000000 AM
B      22-Jun-13      26-MAY-16 11.00.25.000000000 AM


Now what I am trying to do is show the results if there is A then show B
output table is like below i mapped
col1 - Astatus, Bstatus
col2 - Adate, Bdate

AStatus       ADate               BStatus       BDate
A                  12-Apr-13            B                12-Apr-13
A                  12-Apr-13            null                  null
A                  14-May-13             B              14-May-13


If you observe my output you can see in second line that there is null values in Bstatus and Bdate reason for that is in the input table  the row 4 is A and not B, I am expecting B after each A if I did not get it then I will store null values.

My approach for this problem is

Sort the input table based on col3 and create a cursor to get each row after sorted
then check the values of each row and set the values in output table

If my procedure is wrong let me know or else if there is a simple procedure let me know.

DECLARE
 CURSOR c1 IS
  SELECT col1,col2,col3  FROM input_table
    ORDER BY col3 ASC;
  my_col3 date;
  my_col1  VARCHAR2(20);
  my_col2 date;

BEGIN
   OPEN c1;
    LOOP
     FETCH c1 INTO my_col1 , my_col2 , my_col3 ;
     EXIT WHEN c1%NOTFOUND;
      COMMIT;
    END LOOP;
   CLOSE c1;
END;

Open in new window


I wrote the cursor but not sure how to do select from this
0
Comment
Question by:shragi
  • 2
4 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 125 total points
ID: 41899029
You could use sql to get your results (my result is different but you did not say what checks you want to make)
SELECT ASTATUS,
ADATE,
CASE LSTATUS
WHEN 'B' THEN LSTATUS
ELSE
  NULL
END BSTATUS,
CASE LSTATUS
WHEN 'B' THEN LDATE
ELSE
  NULL
END BDATE
--, LDATE
FROM
(
SELECT COL1 ASTATUS, COL2 ADATE, LEAD(COL1) OVER(ORDER BY COL3) LSTATUS, LEAD(COL2) OVER (ORDER BY COL3) LDATE
FROM INPUT_TABLE
ORDER BY COL3
) S
WHERE ASTATUS = 'A'

Using pl_sql  you need to be able to have the information of 2 row at once : this is an example of doing it
DECLARE
 CURSOR c1 IS
  SELECT col1,col2,col3  FROM input_table
    ORDER BY col3 ASC;
  current_rec c1%rowtype;
  prev_rec    c1%rowtype;
BEGIN
   OPEN c1;
    LOOP
     FETCH c1 INTO current_rec;
     EXIT WHEN c1%NOTFOUND;
      if prev_rec.col1 = 'A' then
        if current_rec.col1 = 'B' then
           dbms_output.put_line(prev_rec.col1 || ';' || prev_rec.col2 || ';' || current_rec.col1 || ';' || current_rec.col2);
        else
           dbms_output.put_line(prev_rec.col1 || ';' || prev_rec.col2 || ';' || null || ';' || null);
        end if;
      end if;
      prev_rec := current_rec;
    END LOOP;
   CLOSE c1;
END;
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 41899050
I seemed to get the correct result with this:
SELECT * 
FROM   (SELECT col1, 
               col2, 
               Lead(col1) 
                 over ( 
                   PARTITION BY col2 
                   ORDER BY col3), 
               Lead(col2) 
                 over ( 
                   PARTITION BY col2 
                   ORDER BY col3) 
        FROM   mytab) 
WHERE  col1 = 'A'; 

Open in new window

Didn't need all the case statements.

The sample output definitely doesn't match what should come out.  Since there are 5 A records in the sample data, you should get back 5 rows.

If you need it in a procedure, just use one of the queries that does all the work.  You should need to have to code the looking ahead at records in the procedure.  Let the database do the work.  It is much more efficient at it.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 41899341
select astatus, adate,
case when bstatus != astatus then bstatus end as bstatus,
case when bstatus != astatus then bdate end as bdate
from
(select col1 as astatus, col2 as adate,
 lead(col1) over (order by col3, col1) as bstatus,
 lead(col2) over (order by col3, col1) as bdate
 from yourtable)
where astatus = 'A';
0
 
LVL 31

Expert Comment

by:awking00
ID: 41899352
You could also use decode in place of case -
select astatus, adate,
decode(bstatus,astatus,null,bstatus) as bstatus,
decode(bstatus,astatus,null,bdate) as bdate
from
(select col1 as astatus, col2 as adate,
 lead(col1) over (order by col3, col1) as bstatus,
 lead(col2) over (order by col3, col1) as bdate
 from yourtable)
where astatus = 'A';
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 81
Clone Oracle 12c Database 5 24
Adoquery sql  left join does not work 25 44
SSIS currency format 5 9
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now