Forum Stats

  • 3,825,197 Users
  • 2,260,480 Discussions
  • 7,896,437 Comments

Discussions

filter out service id base on multiple selection criterias.

User_AFYOO
User_AFYOO Member Posts: 13 Red Ribbon
edited Nov 13, 2021 10:58AM in SQL & PL/SQL

My selection criteria for a given service id must have the following.

For example, 0432296082 and 0459436899 would meet the selection criteria the other would not. Consider for the following data sample. I'm using oracle 9i...Thank you

 CREATE TABLE TMP_SELECT  

  (SERVICE_ID VARCHAR2(20), 

CHARGE_TYPE VARCHAR2(20),

DOMAIN VARCHAR2(10), 

ISSUE VARCHAR2(30),

UNDER_OVER VARCHAR2(30) 

  ) ;

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0407259613','Device Payment','Mobile','Miss Aligned','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0407259613','Monthly Plan','Mobile','Miss Aligned','Overcharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0407259613','Recurring Charge','Mobile','Missing','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0432296082','Device Payment','Mobile','Missing in IGEN','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0432296082','Monthly Plan','Mobile','Miss Aligned','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0432296082','Monthly Plan','Mobile','Missing in IGEN','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0432296082','Recurring Charge','Mobile','Missing','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0434496444','Monthly Plan','Mobile','Miss Aligned','Overcharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0435554455','Device Payment','Mobile','Missing in IGEN','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0435554455','Monthly Plan','Table','Missing in IGEN','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0435554455','Recurring Charge','Mobile','Missing','Undercharge');

Insert into tmp_select(SERVICE_ID,CHARGE_TYPE,DOMAIN,ISSUE,UNDER_OVER) values ('0459436899','Monthly Plan','Mobile','Miss Aligned','Undercharge');


 

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,032 Red Diamond

    Hi, @User_AFYOO

    So, the desired results from this sample data are just:

    SERVICE_ID
    ----------
    0432296082
    0459436899
    

    that is, one row per service_id, only including service_ids where every row meets certain conditions: is that it?

    Getting one row per service_id sounds like a job for GROUP BY service_id  . To see if every row meets certain conditions, you can use a HAVING clause, where you compare COUNT (*) to COUNT (exp) . where exp is an expression that is NULL whenever one of the conditions is not met. For example:

    SELECT   service_id
    FROM	 tmp_select
    GROUP BY service_id
    HAVING	 COUNT ( CASE
    	 	   WHEN domain	        = 'Mobile'
    		   AND  under_over	= 'Undercharge'
    		   AND (charge_type, issue)
    	 		  		IN ( ('Device Payment',   'Missing in IGEN')
    			  		   , ('Recurring Charge', 'Missing')
    			  		   , ('Monthly Plan',     'Miss Aligned')
    			  		   , ('Monthly Plan',     'Missing in IGEN')
    			  		   )
    		   THEN 'OK'
    		   ELSE NULL  -- Not needed; included for clarity only
    	 	 END
    	      ) = COUNT (*)
    ORDER BY service_id	-- or whatever you want
    ;
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,032 Red Diamond

    Hi, @User_AFYOO

    So, the desired results from this sample data are just:

    SERVICE_ID
    ----------
    0432296082
    0459436899
    

    that is, one row per service_id, only including service_ids where every row meets certain conditions: is that it?

    Getting one row per service_id sounds like a job for GROUP BY service_id  . To see if every row meets certain conditions, you can use a HAVING clause, where you compare COUNT (*) to COUNT (exp) . where exp is an expression that is NULL whenever one of the conditions is not met. For example:

    SELECT   service_id
    FROM	 tmp_select
    GROUP BY service_id
    HAVING	 COUNT ( CASE
    	 	   WHEN domain	        = 'Mobile'
    		   AND  under_over	= 'Undercharge'
    		   AND (charge_type, issue)
    	 		  		IN ( ('Device Payment',   'Missing in IGEN')
    			  		   , ('Recurring Charge', 'Missing')
    			  		   , ('Monthly Plan',     'Miss Aligned')
    			  		   , ('Monthly Plan',     'Missing in IGEN')
    			  		   )
    		   THEN 'OK'
    		   ELSE NULL  -- Not needed; included for clarity only
    	 	 END
    	      ) = COUNT (*)
    ORDER BY service_id	-- or whatever you want
    ;
    


  • User_AFYOO
    User_AFYOO Member Posts: 13 Red Ribbon
    edited Nov 13, 2021 12:18PM

    Hello Frank, Yes, it is one row per service_id, only including service_ids where every row meets certain conditions. And after that, I have the service id to go by whatever I want to do with the rest. Thank you so much