AnsweredAssumed Answered

OpenDB ignoring flags in query

Question asked by am37849 on Jan 16, 2018

Hi All,

 

I have been having an interesting issue lately when running a query in OpenDB. When I run a query to select users that are not disabled in the system or course as well as disabled courses, it still pulls the disabled users. This is causing issues when attempting to produce an accurate report.

 

 

 

 

This happens with the following queries:

 

 

Select users who are only enabled:

 

 

select u.firstname, u.lastname, u.student_id, c.course_id,

TO_CHAR(cu.last_access_date, 'DD-MM-YYYY HH:MI:SSam') as TIMESTAMP,

u.row_status,c.row_status,cu.row_status

from BBLEARN.course_users cu

inner join BBLEARN.course_main c on cu.crsmain_pk1=c.pk1

inner join BBLEARN.users u on cu.users_pk1=u.pk1

where c.course_id like '%-%-OL0%-18SP1'

or c.course_id like '%-%-OL1%-18SP1'

or c.course_id like '%-%-OL2%-18SP1'

or c.course_id like '%-%-OL3%-18SP1'

or c.course_id like '%-%-OL4%-18SP1'

and (u.user_id not like '%_admin' or u.user_id not like '%_preview%')

and u.row_status ='0'

and cu.row_status ='0'

and c.row_status ='0'

and cu.role='S'

order by c.course_id asc;

 

 

 

 

Select the users who are not disabled:

 

 

select u.firstname, u.lastname, u.student_id, c.course_id,

TO_CHAR(cu.last_access_date, 'DD-MM-YYYY HH:MI:SSam') as TIMESTAMP,

u.row_status,c.row_status,cu.row_status

from BBLEARN.course_users cu

inner join BBLEARN.course_main c on cu.crsmain_pk1=c.pk1

inner join BBLEARN.users u on cu.users_pk1=u.pk1

where c.course_id like '%-%-OL0%-18SP1'

or c.course_id like '%-%-OL1%-18SP1'

or c.course_id like '%-%-OL2%-18SP1'

or c.course_id like '%-%-OL3%-18SP1'

or c.course_id like '%-%-OL4%-18SP1'

and (u.user_id not like '%_admin' or u.user_id not like '%_preview%')

and u.row_status !='2'

and cu.row_status !='2'

and c.row_status !='2'

and cu.role='S'

order by c.course_id asc;

 

 

Anyone else having a similar issue?

Outcomes