sql - Oracle using correct filter -


i got sql-statement far:

   select *           table1,     table2     table1.fk = table2.id     , table2.id = 4

which gives me following output:

[...] [...] rights                             [...] [...]   ...   ...  ( id in ( 10 , 700000 , 80 , 5 ) )  ...   ...  ...   ...  ( id in ( 500000 , 10 , 80 , 5 ) )  ...   ...  ...   ...  ( id in ( 10 , 5 , 80 , 900000 ) )  ...   ...

i need write in where shows me rows in numbers in rights example 700000 or below.

in case row 1 , 2 should shown.

any ideas how solve this?

this might work out need do:

with      t1 (select 1 fk dual union                  select 2 fk dual union                  select 3 fk dual),           t2 (select 1 id, 'id in ( 10 , 700000 , 80 , 5 )' rights dual union                  select 2 id, 'id in ( 500000 , 10 , 80 , 5 )' rights dual union                  select 3 id, 'id in ( 10 , 5 , 80 , 900000 , 203 )' rights dual),   pivoted_t2 (select t2.id,                         t2.rights,                         rtrim(ltrim(upper(t2.rights), 'id n('), ' )') rights_stripped,                         to_number(trim(regexp_substr(rtrim(ltrim(upper(t2.rights), 'id n('), ' )'), '[^,]+', 1, level))) rights_item,                         max(level) on (partition t2.id) num_items                    t2                  connect prior t2.id = t2.id                             , prior dbms_random.value not null                             , trim(regexp_substr(rtrim(ltrim(upper(t2.rights), 'id n('), ' )'), '[^,]+', 1, level)) not null) select t1.fk,        pt2.rights   t1        inner join pivoted_t2 pt2 on (t1.fk = pt2.id)  pt2.rights_item <= 700000 group t1.fk,          pt2.rights,          pt2.num_items having count(*) = pt2.num_items;          fk rights                               ---------- ------------------------------------          1 id in ( 10 , 700000 , 80 , 5 )                2 id in ( 500000 , 10 , 80 , 5 )

basically, you'll need split out rights individual items, work out how many items there in total each set of rights, , work out how many below specified value. if same number of values returned in set, know items match filter.

eta: can't guarantee perform well, large amount of data, if you're going insist on storing data in manner, you'll have accept performance suffer.


Comments

Post a Comment

Popular posts from this blog

android - Pass an Serializable object in AIDL -

i have defined aidl file interface imyfeature { void dotask(class clazz); } but need pass class object process. possible in android? if so, how? i tried: interface imyfeature { serializable class; void dotask(class clazz); } but doesn’t work. aidl in android supports custom class implement parcelable interface. in case want send class object not possible. can send class canonical name , retrieve in service. for example have aidl file interface imyfeature { void dotask(string classname); } then call : binder.dotask(myclass.class.getcanonicalname()); and in service binder stub implementation : private final imyfeature mbinder = new imyfeature.stub() { public void dotask(string classname) { try { class<?> clazz = class.forname(classname); constructor<?> ctor = clazz.getconstructor((class[]) null); object object = ctor.newinstance(); ...
Read more

How to provide Authorization & Authentication using Asp.net, C#? -

Image
i have asp.net application in c#. i have provide page authorization specific user/usergroup. i have created 2 tables in database: 1) pagename 2)permissions[insert, update, view, delete] but i'm unable match tables , permissions assigning... can 1 please me, how created tables in database , how coding. please me. you can below explanation. configure access specific file , folder, set forms-based authentication. request page in application redirected logon.aspx automatically. in web.config file, type or paste following code. this code grants users access default1.aspx page , subdir1 folder . <configuration> <system.web> <authentication mode="forms" > <forms loginurl="login.aspx" name=".aspnetauth" protection="none" path="/" timeout="20" > </forms> </authentication> <!-- section denies access files in...
Read more

How to use Authorization & Authentication in Asp.net, C#? -

i using roll management , trying give page , folder access according user or user group, using server created ad group user authentication. i have default1.aspx page default , subdir1 folder give different access separate user group i using below logic in web.config. <location path="subdir1"> <system.web> <authorization> <allow users ="?" /> </authorization> </system.web> </location> i facing problem provide same access 2 or more directory same user should have provide allow user code twice both folder? i can use logic repeating value folder want access providing in 1 logic. i have got answer configure folder/page access, have make different access shown below.. configure access specific file , folder, set forms-based authentication. request page in application redirected logon.aspx automatically. in web.config file, done following code. this code grants user...
Read more