Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Retrieve Values

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

618 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