Categorize rows as first, last or in between

casegroup byoracleoracle-12c

I have a table called road_events.

  create table road_events 
   (
    event_id number(5,0),
    road_id number(5,0),
    event_type nvarchar2(50),
    lifecycle_number number(5,0)
   );

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (1,100,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (2,100,'RECONSTRUCTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (3,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (4,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (5,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (6,100,'INSPECTION',2);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (7,200,'INSPECTION',0);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (8,200,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (9,200,'INSPECTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (10,200,'INSPECTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (11,200,'RECONSTRUCTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (12,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (13,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (14,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (15,200,'RECONSTRUCTION',3);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (16,300,'RECONSTRUCTION',1);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (17,400,'INSPECTION',0);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (18,500,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (19,500,'RECONSTRUCTION',2);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (20,600,'INSPECTION',0);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (21,600,'INSPECTION',0);

select
    event_id,
    road_id,
    substr(event_type,0,15) as event_type,
    lifecycle_number
from
    road_events
order by
    event_id

  EVENT_ID    ROAD_ID EVENT_TYPE      LIFECYCLE_NUMBER
---------- ---------- --------------- ----------------
         1        100 RECONSTRUCTION                 1
         2        100 RECONSTRUCTION                 2
         3        100 INSPECTION                     2
         4        100 INSPECTION                     2
         5        100 INSPECTION                     2
         6        100 INSPECTION                     2

         7        200 INSPECTION                     0
         8        200 RECONSTRUCTION                 1
         9        200 INSPECTION                     1
        10        200 INSPECTION                     1
        11        200 RECONSTRUCTION                 2
        12        200 INSPECTION                     2
        13        200 INSPECTION                     2
        14        200 INSPECTION                     2
        15        200 RECONSTRUCTION                 3

        16        300 RECONSTRUCTION                 1

        17        400 INSPECTION                     0

        18        500 RECONSTRUCTION                 1
        19        500 RECONSTRUCTION                 2

        20        600 INSPECTION                     0
        21        600 INSPECTION                     0

For each road, I would like to categorize the rows in each lifecycle with a lifecycle_name (in this order):

The rows in the last(max) lifecycle would be called current lifecycle
The rows in the first(min) lifecycle would be called original lifecycle (if applicable)
Any others would be called past lifecycle (if applicable)

It would look like this:

+----------+---------+----------------+------------------+--------------------+
| EVENT_ID | ROAD_ID |   EVENT_TYPE   | LIFECYCLE_NUMBER |   LIFECYCLE_NAME   |
+----------+---------+----------------+------------------+--------------------+
|        1 |     100 | RECONSTRUCTION |                1 | ORIGINAL LIFECYCLE |
|        2 |     100 | RECONSTRUCTION |                2 | CURRENT LIFECYCLE  |
|        3 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        4 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        5 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        6 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|        7 |     200 | INSPECTION     |                0 | ORIGINAL LIFECYCLE |
|        8 |     200 | RECONSTRUCTION |                1 | PAST LIFECYCLE     |
|        9 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       10 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       11 |     200 | RECONSTRUCTION |                2 | PAST LIFECYCLE     |
|       12 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       13 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       14 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       15 |     200 | RECONSTRUCTION |                3 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       16 |     300 | RECONSTRUCTION |                1 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       17 |     400 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       18 |     500 | RECONSTRUCTION |                1 | ORIGINAL LIFECYCLE |
|       19 |     500 | RECONSTRUCTION |                2 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       20 |     600 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
|       21 |     600 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+

Is there a way to succinctly categorize the rows into current lifecycle, original lifecycle, and past lifecycle, where applicable?

I can think of some lengthy/cumbersome ways to do it with case statements and subqueries, but I'm looking for something a bit more elegant than that.

Update:

I've updated the question with a few more scenarios (roads 300-600). Credit goes to @markp for thinking of this.
@markp also highlighted that current lifecycle trumps original lifecycle.

Best Answer

You can use two window functions, min() and max() with the same window, over the partition by road_id:

select  event_id,
        road_id,
        event_type,
        lifecycle_number,
        case lifecycle_number
            when max(lifecycle_number) over (partition by road_id)
                then 'CURRENT LIFECYCLE'
            when min(lifecycle_number) over (partition by road_id)  
                then 'ORIGINAL LIFECYCLE'
                else 'PAST LIFECYCLE'
        end as LIFECYCLE_NAME
from    road_events re
order by event_id;

You could also use first_value() and last_value() window functions.

Test at dbfiddle.uk

Related Solutions

Oracle – How to Exclude Rows Containing Null Values

SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
) 
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right? If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN                                                                
---------- --------------------                                                 
       100 MKarki                                                               
       200 VKarki                                                               
       300 CKarki                                                               

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim  
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim   
SQL> conn / as sysdba
Connected.    
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Joe                                                                  
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Best Answer

Related Solutions

Oracle – How to Exclude Rows Containing Null Values

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

Related Question