?
Solved

SQL Retrieve Values

Posted on 2016-11-22
4
Medium Priority
?
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 20

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

752 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