CODEWITHSUNDEEP
sqloracle-databaseoracle11gr2
Bhuvan
Bhuvan

Reputation: 2229

SQL, Oracle11g: Find records matching particular set of ID's

Please refer SQL Oracle : how to find records maching a particular id in the column

Now my query is on the similar lines: My table has the following data:enter image description here

now I want to find the records that have asso_entity_id as a combination of 3 values. For eg:- if I select asso_entities from my jsp as 30000, 30001 and 80002 (in any order), I should get the first record of the above table.

Upvotes: 2

Views: 304

Answers (3)

Art
Art

Reputation: 5792

A bit simplified version of techdo's example:

This is the test table structure:

ID  DATASET_NAME    DATASET_VAL
------------------------------------------
1   DATASET1        3000 | 30001 | 80002
2   DATASET1        3000 | 80002
3   DATASET1        3000 | 80002


SELECT LISTAGG(str, ' | ') WITHIN GROUP (ORDER BY str) asso_ety_id
  FROM
  (
   SELECT DISTINCT id, dataset_name
        , TRIM(REGEXP_SUBSTR (dataset_val, '[^|]+', 1, LEVEL)) str  
    FROM your_tab 
   CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(dataset_val, '[^|]+'))  + 1
  )
  WHERE str IN ('80002', '30001', '3000') -- in any order --
   -- AND id = 1 -- optional --
  /

Output:

3000 | 30001 | 80002

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191305

Following on from my answer on your previous question, and continuing to use that as an example becuase I can see the underlying query that's doing the listagg... you can match on multiple values, count how many you matched on, and apply a further filter to ensure you matched on all of them. Something like:

select distinct role_id, role_name, active, companyName, permission_id,
    permission_name, rn, total_rows, roleCreated
from (
    select t.*,
        count(raw_permission_id) over (partition by role_id) as cnt
    from (
        select r.role_id, 
            r.role_name, 
            r.active, 
            decode(r.entity_type_id, 1000, m.name, 3000, cour.name,
                4000, 'Ensenda') companyName,
            p.permission_id as raw_permission_id,
            listagg(p.permission_id, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_id, 
            listagg(p.permission_name, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_name, 
            dense_rank() over (order by r.created_ts desc) as rn,
            count(distinct r.role_id) over () as total_rows, 
            r.created_ts roleCreated
        from t_role r
        left join t_role_permission rp ON r.role_id = rp.role_id
        left join t_permission p ON rp.permission_id = p.permission_id
        left join merchant m on r.entity_id = m.merchantkey 
        left join courier cour on r.entity_id = cour.courierkey 
    ) t
    where raw_permission_id in (301446, 301445)
)
where cnt = 2
and rn between 1 and 100
order by roleCreated desc;

The only difference from that previous answer is the outer two layers of the query:

select ...
from (
    select t.*,
        count(raw_permission_id) over (partition by role_id) as cnt
    from (
    ... -- no changes here
    ) t
    where raw_permission_id in (301446, 301445)
)
where cnt = 2
...

So it's now looking for two possible permissions, and counting - analytically, so for each role - how many were matched. The outer query checks that the number that matched is 2, which you'll obviously have to adjust depending on how many options you're trying to match against.

For this question it would add something like:

        count(raw_asso_entity_id) over (partition by <some_id>) as cnt
    ...
    where raw_asso_id in (30000, 30001 and 80002)
...
where cnt = 3

Upvotes: 0

TechDo
TechDo

Reputation: 18649

Please check the query... Bit long... will try to shorten it.

with test as  (
  select * from YOURTABLE
)
SELECT distinct DATASETNAME FROM(
  select x.*, COUNT(*) OVER (partition by DATASETNAME ORDER BY DATASETNAME) CNT From(
    select DATASETNAME, regexp_substr (ASSO_ENTITY_ID, '[^|]+', 1, row_number() OVER (partition by DATASETNAME ORDER BY DATASETNAME)) split  
    from test  
    connect by level <= length (regexp_replace (ASSO_ENTITY_ID, '[^|]+'))  + 1
  )x where SPLIT IS NOT NULL
)xx 
  WHERE SPLIT IN ('300000', '300001', '800002') AND
  CNT =3;

Upvotes: 1

Related Questions