Select except values (SQL / Oracle)

Question

I need to improve this select that already exists here to perform a query where the ICCID initiated by 8995 are not returned, but I do not have much knowledge of database.

How can I change this select? I use SQLDEVELOPER, but the client database is probably Oracle.

SELECT a.rp_package_value AS ICCID,
  c.rrs_resource_value AS IMSI,
  (
  CASE
    WHEN (SUBSTR (c.rrs_resource_value, 6, 1) = SUBSTR (a.rp_package_value, 9, 1))
    AND (SUBSTR (c.rrs_resource_value, 6, 2) <> '00') -- valida se o HLR existe
    THEN 'Valid'
    ELSE 'Invalid'
  END ) AS IMSI_CHECK ,
  rrs_resource_sts Status, rp_package_sts
FROM rm_packages a,
  rm_package_content b,
  rm_resource_stock c
WHERE a.rp_package_value IN
  (SELECT RRS_RESOURCE_VALUE
  FROM rm_resource_stock c
  WHERE c.rrs_resource_tp_id = 6
  AND c.rrs_resource_pool = 30
  --AND a.rp_package_value like '89955053110002178148'
  AND SUBSTR (RRS_RESOURCE_VALUE, 9, 1) = '1' 
  AND c.rrs_resource_sts = 'ASSIGNED' 

  )
AND a.rp_package_id = b.rpc_package_id
AND c.rrs_resource_sts = a.rp_package_sts
AND b.rpc_component_tp_id = 5
AND b.rpc_component_vl_id = c.rrs_id
ORDER BY a.sys_creation_date DESC;

Answer 1

You can simply add this condition to your WHERE

WHERE a.rp_package_value NOT LIKE '8995%'

'8995%' - Search for everything that starts with 8995...

LIKE - Search for a similar string

NOT - Denies the Like

Your final SELECT will be:

SELECT a.rp_package_value AS ICCID,
  c.rrs_resource_value AS IMSI,
  (
  CASE
    WHEN (SUBSTR (c.rrs_resource_value, 6, 1) = SUBSTR (a.rp_package_value, 9, 1))
    AND (SUBSTR (c.rrs_resource_value, 6, 2) <> '00') -- valida se o HLR existe
    THEN 'Valid'
    ELSE 'Invalid'
  END ) AS IMSI_CHECK ,
  rrs_resource_sts Status, rp_package_sts
FROM rm_packages a,
  rm_package_content b,
  rm_resource_stock c
WHERE a.rp_package_value IN
  (SELECT RRS_RESOURCE_VALUE
  FROM rm_resource_stock c
  WHERE c.rrs_resource_tp_id = 6
  AND c.rrs_resource_pool = 30
  AND a.rp_package_value NOT LIKE '8995%' /* aqui */
  AND SUBSTR (RRS_RESOURCE_VALUE, 9, 1) = '1' 
  AND c.rrs_resource_sts = 'ASSIGNED' 

  )
AND a.rp_package_id = b.rpc_package_id
AND c.rrs_resource_sts = a.rp_package_sts
AND b.rpc_component_tp_id = 5
AND b.rpc_component_vl_id = c.rrs_id
ORDER BY a.sys_creation_date DESC;