ORACLE query using NOT IN too many values -


i have oracle query below:

select * wa_ga_tbl_activity a, wa_ga_tbl_users u a.userid_fk = u.userid , (a.groupid_fk = 'gr0001' or u.groupid_fk = 'gr0001') , a.userid_fk != 'us0007' , (a.activityid) not in(select activityid_fk wa_ga_tbl_accessactivity  userid_fk = 'us0007'

the first logic check in wa_ga_tbl_activity when userid != us0007. , want user details in not in:

not in(select activityid_fk wa_ga_tbl_accessactivity  userid_fk = 'us0007'

when userid_fk = us0007

i tried this:

select * wa_ga_tbl_activity a, wa_ga_tbl_users u a.userid_fk = u.userid , (a.groupid_fk = 'gr0001' or u.groupid_fk = 'gr0001') , a.userid_fk != 'us0007' , (a.activityid) not in(select s.userid, s.dateadded, w.activityid_fk wa_ga_tbl_users s, wa_ga_tbl_accessactivity  w s.userid = w.userid_fk , w.userid_fk = 'us0007')

got error:

ora-00913: many values

sample data

table wa_ga_tbl_activity

activityid | activityname | userid_fk | activitydate | groupid_fk 001        | add new user | us0001    | 4/6/2017     | gr0001

table wa_ga_tbl_users

userid | username | dateadded us0001 | testuser1| 4/6/2017 us0007 | testuser2| 4/6/2017

table wa_ga_tbl_accessactivity

activityid_fk | userid_fk

is there way that?

the number of columns in projection of in sub-query must match number referenced predicate. ora-00913: many values error because (a.activityid) not match (select s.userid, s.dateadded, w.activityid_fk .

all need correlate

select        * wa_ga_tbl_activity a, wa_ga_tbl_users u a.userid_fk = u.userid , (a.groupid_fk = 'gr0001' or u.groupid_fk = 'gr0001') , a.userid_fk != 'us0007' , (a.activityid) not in (select w.activityid_fk                         wa_ga_tbl_users s,                             wa_ga_tbl_accessactivity w                            s.userid = w.userid_fk                             , w.userid_fk = 'us0007')

it seems simplify subquery this

 (select w.activityid_fk     wa_ga_tbl_accessactivity w    w.userid_fk = 'us0007')

but know business logic better do.


Comments

Post a Comment

Popular posts from this blog

'hasOwnProperty' in javascript -

i use obj.hasownproperty judge whether object has property, when replaced obj[prop] !== undefined , not normal implementation, ask, why behind method can not use it? object.hasownproperty(prop); object[prop] !== undefined; obj[prop] !== undefined wrong 2 reasons: you can explicitly set property undefined , obj[prop] = undefined; . obj.hasownproperty(prop) return true in case. obj[prop] follow prototype chain, return property that's inherited. obj.hasownproperty(prop) returns true if property exists directly in object, returns false inherited properties.
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

android - Unable to generate FCM token from dynamically instantiated Firebase -

our business logic requires instantiate firebase during runtime. fetch firebase credentials (app id, api key etc.) default firebase location after knowing user's public key , create firebase instance using credentials. this means there 2 firebase instances used within app: the default "index" firebase gives credentials second the actual firebase intend use point forward the second firebase initialised this: firebaseapp app = firebaseapp.initializeapp(<context>, <options>, <app_label>); our problem traditional method of retrieving fcm token using firebaseinstanceidservice , ontokenrefresh() fails since ontokenrefresh() method called first firebase instance. directly calling string token = firebaseinstanceid.getinstance(app).gettoken(); returns null never ready when called. have tried polling test if token generated @ point. no luck. how can generate fcm token reliably firebase instance instantiated during runtime? i...
Read more