Excluding the unwanted business cases - Oracle 12c

Question

I've a requirement to exclude some business cases to filter the records.

SQL FIddle link: http://sqlfiddle.com/#!4/a130b/2

First, I've a table of stations, which has station number, area the station belong to and the direction whether its inbound or outbound as well as the run seq number.

CREATE TABLE stns (stn number, dir varchar2(10), seq number , area varchar2(20));  


INSERT INTO stns VALUES (314,'In',1,'Houston');  
INSERT INTO stns VALUES (315,'In',2,'Houston');  
INSERT INTO stns VALUES (316,'In',3,'Houston');  
INSERT INTO stns VALUES (317,'In',4,'Houston');  
INSERT INTO stns VALUES (324,'Out',72,'Houston');  
INSERT INTO stns VALUES (311,'Out',73,'Houston');  
INSERT INTO stns VALUES (312,'Out',74,'Houston');  
INSERT INTO stns VALUES (313,'Out',75,'Houston');  
INSERT INTO stns VALUES (133,'In',82,'Blacktown');  
INSERT INTO stns VALUES (148,'In',83,'Blacktown');  
INSERT INTO stns VALUES (136,'In',84,'Blacktown');  
INSERT INTO stns VALUES (135,'In',85,'Blacktown');  
INSERT INTO stns VALUES (134,'In',86,'Blacktown');  
INSERT INTO stns VALUES (127,'Out',1,'Blacktown');  
INSERT INTO stns VALUES (126,'Out',2,'Blacktown');  
INSERT INTO stns VALUES (125,'Out',3,'Blacktown');  
INSERT INTO stns VALUES (124,'Out',4,'Blacktown');  
INSERT INTO stns VALUES (176,'Out',12,'Christtown');  
INSERT INTO stns VALUES (177,'Out',13,'Christtown');  
INSERT INTO stns VALUES (178,'Out',14,'Christtown');  
INSERT INTO stns VALUES (179,'Out',15,'Christtown');  
INSERT INTO stns VALUES (919,'In',38,'Albian');  
INSERT INTO stns VALUES (920,'In',39,'Albian');  
INSERT INTO stns VALUES (208,'In',41,'Albian');  
INSERT INTO stns VALUES (922,'In',42,'Albian');

Next, I've the operational table, which has the origin and destination stn, the hours and the number of patrons.

-1 means we dont know where the ostn or dstn is ..UNKNOWN

CREATE TABLE DEMO ( dt number, ostn number, ohr number, dstn number , dhr number, patrons number );  


INSERT INTO DEMO VALUES (20171101, -1,-1,919,1,4);   
INSERT INTO DEMO VALUES (20171101, -1,-1,920,2,3);   
INSERT INTO DEMO VALUES (20171101, 208,2,922,2,2);   
INSERT INTO DEMO VALUES (20171101, 314,3,316,2,2);   
INSERT INTO DEMO VALUES (20171101, 133,1,-1,-1,1);   
INSERT INTO DEMO VALUES (20171101, 133,2,134,2,4);   
INSERT INTO DEMO VALUES (20171101,1000,1,314,2,3);   
INSERT INTO DEMO VALUES (20171101,1000,1,315,2,3);   
INSERT INTO DEMO VALUES (20171101,1000,1,315,2,3);   
INSERT INTO DEMO VALUES (20171101,-1,-1,177,2,3);   
INSERT INTO DEMO VALUES (20171101,178,1,179,2,3);   
INSERT INTO DEMO VALUES (20171101,178,1,-1,-1,3);   
INSERT INTO DEMO VALUES (20171101,178,1,888,2,3);   
INSERT INTO DEMO VALUES (20171101,999,1,11,2,3);   
INSERT INTO DEMO VALUES (20171101,998,1,12,2,3);   
INSERT INTO DEMO VALUES (20171101,997,1,13,2,3);   
INSERT INTO DEMO VALUES (20171101,313,1,777,2,3);   
INSERT INTO DEMO VALUES (20171101,313,2,312,4,4);   
INSERT INTO DEMO VALUES (20171101,919,1,179,2,3);   
INSERT INTO DEMO VALUES (20171101,178,2,208,4,4);   
INSERT INTO DEMO VALUES (20171101, 12321,1, 124,1,3);  
INSERT INTO DEMO VALUES (20171101, 127,1, 124,1,3);

Next, I've a configuration table where I included business cases, where the flag_ind tells whether to include the case or not. Y is include N is exclude.

create table cnfg ( o_in number, o_out number, d_in number,d_out number,flag_ind char(1));  

INSERT INTO CNFG VALUES (1,0,1,0,'Y');  
INSERT INTO CNFG VALUES (0,0,1,0,'Y');  
INSERT INTO CNFG VALUES (0,0,0,1,'Y');  
INSERT INTO CNFG VALUES (1,0,0,1,'N');  
INSERT INTO CNFG VALUES (0,1,0,1,'Y');  
INSERT INTO CNFG VALUES (1,0,0,0,'Y');  
INSERT INTO CNFG VALUES (0,1,1,0,'N');  
INSERT INTO CNFG VALUES (0,1,0,0,'Y');  
INSERT INTO CNFG VALUES (0,0,0,0,'N');

Till here, Ill transpose the demo table to figure out the o_in, o_out , d_in, d_out attributes. I was able to get the logic right (with the help of this community)

SELECT dt,  
       ostn,  
       ohr,  
       dstn,  
       dhr,  
       patrons,  
       CASE  
          WHEN ostn IN (SELECT stn  
                          FROM stns  
                         WHERE dir = 'In')  
          THEN  1 ELSE   0  
       END  o_in,  
       CASE  
          WHEN ostn IN (SELECT stn  
                          FROM stns  
                         WHERE dir = 'Out')  
          THEN 1 ELSE  0  
       END o_out,  
       CASE  
          WHEN dstn IN (SELECT stn  
                          FROM stns  
                         WHERE dir = 'In')  
          THEN 1  ELSE 0  
       END d_in,  
       CASE  
          WHEN dstn IN (SELECT stn  
                          FROM stns  
                         WHERE dir = 'Out')  
          THEN 1  ELSE  0  
       END  d_out  
  FROM demo;

I can join the table demo with cnfg and exclude those cases where flag_ind is N. Now the Problem is there are more business cases identified those needs to excluded from the final result set

For dir IN , customers tapped on at Blacktown and tapped off outside corridor
For dir Out, customers tapped on outside corridor and tapped off at Blacktown
For dir IN, customers tapped on outside corridor and tapped off at Houston
For dir OUT, customers tapped on at Houston and tapped off outside corridor For Houston cases I need to check the seq number and exclude only particular stn details, Please have a look in the query.

select * FROM demo  
      WHERE ostn IN  
               (SELECT stn  
                  FROM stns  
                 WHERE dir='In'  
                 AND area = 'Blacktown')  
            AND dstn NOT IN (SELECT stn FROM stns); -- Case1                                            


select * FROM demo  
 WHERE dstn IN  
              (SELECT stn  
                  FROM stns  
                 WHERE dir='Out'  
                 AND area = 'Blacktown')  
            AND ostn NOT IN (SELECT stn FROM stns);   -- Case2  



 select * FROM demo             
   WHERE (dstn IN  
                (SELECT stn  
                   FROM stns  
                  WHERE     dir = 'In'  
                        AND area = 'Houston'  
                        AND seq = 1)  
             AND ostn NOT IN (SELECT stn FROM stns));  -- Case3              


select * from demo  
  WHERE (ostn IN  
                (SELECT stn  
                   FROM stns  
                  WHERE     dir = 'Out'  
                        AND area = 'Houston'  
                        AND seq = 75)  
             AND dstn NOT IN (SELECT stn FROM stns)); -- Case4

Can you guide or help in excluding the above cases along with the cases mentioned in the cnfg table.?

Ponder Stibbons · Accepted Answer · 2017-11-29T14:30:09.287

with t1 as (
    select dm.dt, dm.ostn, dm.ohr, dm.dstn, dm.dhr, dm.patrons,
           so.area area_in,  so.dir dir_in,  so.seq seq_in, 
           sd.area area_out, sd.dir dir_out, sd.seq seq_out, 
           case when so.dir  = 'In'  then 1 else 0 end o_in,
           case when so.dir  = 'Out' then 1 else 0 end o_out,
           case when sd.dir  = 'In'  then 1 else 0 end d_in,
           case when sd.dir  = 'Out' then 1 else 0 end d_out
      from demo dm 
        left join stns so on dm.ostn = so.stn
        left join stns sd on dm.dstn = sd.stn),
t2 as (
    select * 
      from t1
      join cnfg using (o_in, o_out, d_in, d_out)
      where flag_ind = 'Y')
select * 
  from t2
  where not (   
       (area_in = 'Blacktown' and dir_in = 'In' and area_out is null)
    or (area_in is null and area_out = 'Blacktown' and dir_out = 'Out') 
    or (area_in is null and area_out = 'Houston'   and dir_out = 'In') 
    or (area_in = 'Houston' and dir_in = 'Out' and seq_in = 75 and area_out is null) )

^{SQL Fiddle}

step first (subquery t1) - join table stations twice, this gives you clear, readable picture. Also assign o_in, o_out, d_in, d_out here,
step two (t2) - join with configuration table using these values and exclude unwanted flags,
last step - from above result exclude four unwanted cases.

Edit:

If I need to replace seq_in = 75 and seq_out = 1 with max(seq) and min(seq) of houstons, then how should I approach the case.

Replace seq_in = 75 with seq_in = (select max(seq) from stns where area = 'Houston')

I want to exclude those records where the tap off seq number is less than tap on seq number. e.g to check if the transport goes backwards from seq 4 to seq 3 or 2

... or (seq_in is not null and seq_out is not null and seq_in > seq_out)

Thank you, Ill test this with the real thing and let you know. — user2653353, Nov 29 '17 at 13:36
Thank you, Ill test this with the real thing and let you know. I've two questions, If I need to replace seq_in = 75 and seq_out = 1 with max(seq) and min(seq) of houstons, then how should I approach the case. Another question is If I want to exclude those records where the tap off seq number is less than tap on seq number. e.g to check if the transport goes backwards from seq 4 to seq 3 or 2 .. how to check these cases and exclude. e.g ostn = 313 and dstn = 312 … — user2653353, Nov 29 '17 at 13:46
Thanks @ponder Stibbons, it worked well in my instances. The only caveat was dealing with null values for which NOT clause was not handling. Do you think if this cases can be put into another table and can join back to filter out just like the cnfg table. — user2653353, Nov 30 '17 at 10:23
When handling null values we have to be very careful. You cannot include them in `IN` clause for instance. You have to check with `IS NULL` or `IS NOT NULL`, simple comparison does not work. Please look for some questions and answers in this topic, for instance [this](https://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null) and [this](https://stackoverflow.com/questions/1758409/sql-join-on-null-values). — Ponder Stibbons, Nov 30 '17 at 11:16
I just used NVL(area_in,’-1’) and then used area_in =‘-1’.... in the where clause ... converted everything like this — user2653353, Nov 30 '17 at 11:25

Excluding the unwanted business cases - Oracle 12c

1 Answers1