Here is the table structure that I need to query:

UserID

Type

Name

Department

Status

Classification

Enrollment_Status

Where “Type” and “Department” are foreign keys of the other two tables. The query criteria are:

If the user type is “Student” (type ID = 1), then only retrieve enrolled students (Enrollment_Status = ‘EN’);

If the user type is not “Student” (type ID <> 1), then check Classification field and only retrieve data with Classification <> ‘R’

It is a perfect scenario for using CASE statement in WHERE clause, and here is the complete query in Oracle PL/SQL:

SELECT    U.UserID, U.NAME, 

          D.DEPARTMENT,  U.STATUS, 

          U.CLASSIFICATION

FROM      Users U, Departments D, UserType T    

WHERE CASE WHEN (T.ID = '1') AND  (U.ENROLLMENT_STATUS = 'EN') THEN 1

           WHEN (T.ID <> '1') AND (U.CLASSIFICATION <> 'R') THEN 1

           ELSE 0

      END = 1

AND  U."TYPE" = T.ID 

AND (U.DEPARTMENT = D.DEPARTMENT_CODE (+) )

ORDER BY UserID;

For more information about CASE statement in MS SQL, check here: http://msdn.microsoft.com/en-us/library/ms181765.aspx

For more information about CASE statement in Oracle PL/SQL, check here:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/04_struc.htm#484

[UPDATE]: I just found out that .NET TableAdapter does not recognize the CASE statement above, thought the above query runs well in Oracle SQL Developer. After further investigation, I realize the above query can be changed to this:

SELECT    U.UserID, U.NAME, 

          D.DEPARTMENT,  U.STATUS, 

          U.CLASSIFICATION

FROM      Users U, Departments D, UserType T    

WHERE 

((T.ID = '1' AND  U.ENROLLMENT_STATUS = 'EN') OR (T.ID <> '1' AND U.CLASSIFICATION <> 'R'))

AND  U."TYPE" = T.ID 

AND (U.DEPARTMENT = D.DEPARTMENT_CODE (+) )

ORDER BY UserID;

And TableAdapter will recognize the above query and execute it without any error.