SQL Retrieve Values

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
shragiAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
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
 
flow01Commented:
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
 
awking00Commented:
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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.