Select with exception of values (SQL / Oracle)

Question

Select with exception of values (SQL / Oracle)

Navigation

#1 by (2 votes)

0

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

How can I change this select? I use SQLDEVELOPER, however 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;

sql sql-server oracle

asked by anonymous 08.06.2015 / 17:06

1 answer

Return values from two columns of different tables Place 4 columns in a row

score 2 · Accepted Answer

You can simply add this condition to your WHERE

WHERE a.rp_package_value NOT LIKE '8995%'

'8995%' - Search for everything starting with 8995 ...

LIKE - Search for a similar string

NOT - Deny 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;