?
Solved

SQL Retrieve Values

Posted on 2016-11-22
4
Medium Priority
?
95 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 21

Assisted Solution

by:flow01
flow01 earned 500 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 35

Accepted Solution

by:
johnsone earned 1000 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 32

Assisted Solution

by:awking00
awking00 earned 500 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 32

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

807 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