SELECT  
	COUNT(CASE WHEN(  A.TMS1> SYSTIMESTAMP - INTERVAL '30' MINUTE)  THEN 1  END),
	COUNT(CASE WHEN  A.TMS1>= SYSTIMESTAMP - INTERVAL '24' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
	
        COUNT(*) AS CNT 
FROM MQ A,CODE B
WHERE SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=680 Card=34430 Bytes
          =4303750)

   1    0   SORT (GROUP BY) (Cost=680 Card=34430 Bytes=4303750)
   2    1     HASH JOIN (Cost=211 Card=34430 Bytes=4303750)
   3    2       INDEX (FAST FULL SCAN) OF 'PK_MQ1' (UNIQUE) (
          Cost=7 Card=2469 Bytes=34566)

   4    2       MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
          4)

   5    4         TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
          tes=528)

   6    4         BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
   7    6           TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
          130 Bytes=13000)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         92  consistent gets
         68  physical reads
          0  redo size
      50934  bytes sent via SQL*Net to client
       1017  bytes received via SQL*Net from client
        108  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1598  rows processed

	   
	   

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16913 Card=770744 By
          tes=96343000)

   1    0   SORT (GROUP BY) (Cost=16913 Card=770744 Bytes=96343000)
   2    1     NESTED LOOPS (Cost=6438 Card=770744 Bytes=96343000)
   3    2       MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
          4)

   4    3         TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
          tes=528)

   5    3         BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
   6    5           TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
          130 Bytes=13000)

   7    2       INDEX (RANGE SCAN) OF 'PK_MQ1' (UNIQUE) (Cost
          =1 Card=123 Bytes=1722)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7333  consistent gets
          0  physical reads
          0  redo size
      50934  bytes sent via SQL*Net to client
       1017  bytes received via SQL*Net from client
        108  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1598  rows processed