Search code examples
sqloracle-databasepeoplesoft

Not getting expected results in Oracle SQL

I have a query with 5 prompts

:1 - EMPLID
:2 - APPL_NBR
:3 - CHECKLIST_CD
:4 - CHECKLIST STATUS
:5 - ADMIT_TERM

The problem with my query is that it does not follow the results of the criteria given below:

  1. There are no mandatory prompts, so if the report is run where all the criteria are blank, then the following will happen:
    • All students with incomplete / in progress checklists will be shown regardless of when they were created.
    • All students whose checklists were completed within the current Term will be shown.
  2. If the (past) Admit term is specified, cases from e-i will show the completed checklists of the specified term.
  3. If the (past) Admit term is specified, only checklists that have been created during that term who are still incomplete / in progress checklists will be shown.
  4. If a future Admit term is specified, then only the current incomplete / in progress checklists will be show.

Here is the sql:

SELECT A.EMPLID, A.NAME, A.ADM_APPL_NBR, A.ADMIN_FUNCTION, A.STDNT_CAR_NBR, 
A.ACAD_CAREER, A.APPL_PROG_NBR, A.ADMIT_TERM, A.CHECKLIST_CD, A.DESCRSHORT, 
A.CHECKLIST_STATUS, TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'), 
TO_CHAR(A.DUE_DT,'YYYY-MM-DD'), A.COMM_COMMENTS, A.SEQ_3C, A.CHKLST_ITEM_CD, 
A.DESCR, A.ITEM_STATUS, TO_CHAR(A.UM_STATUS_DT_CL,'YYYY-MM-DD'), 
TO_CHAR(A.UM_DUE_DT_CL,'YYYY-MM-DD'), A.RESPONSIBLE_ID 
FROM PS_UM_7902_VW2 A 

--for Condition 1, for prompts with no values provided
WHERE ( 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
 AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
 AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3) 
 AND A.CHECKLIST_STATUS = :4 
 OR '' = :4 
 AND A.CHECKLIST_STATUS = 'I' 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 -- end for Condition 1

 OR A.EMPLID = :1 
 AND ( 1 = 1 AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
 AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3) 
 AND A.CHECKLIST_STATUS = 'I' 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT)) 
 OR A.ADM_APPL_NBR = :2 
 AND ( 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
 AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3) 
 AND A.CHECKLIST_STATUS = 'I' 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT)) 
 OR A.CHECKLIST_CD = :3 
 AND ( 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
 AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
 AND A.CHECKLIST_STATUS = 'I' 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT)) 
 OR A.CHECKLIST_STATUS = :4 
 AND ( 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
 AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
 AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3)) 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 

 --for Conditions 2, 3 and 4 for ADMIT_TERM
 OR A.ADMIT_TERM = :5 
 AND 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
 AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
 AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3) 
 AND (( SYSDATE > A.TERM_END_DT 
 AND A.CHECKLIST_STATUS = 'C') 
 OR SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.CHECKLIST_STATUS = 'I' 
 OR ( SYSDATE < A.TERM_BEGIN_DT 
 AND A.CHECKLIST_STATUS = 'I')));
 -- end for Conditions 2, 3 and 4

I have isolated the part where I did conditions 1, 2, 3 and 4. Most of the parts in the middle are working fine with their given conditions. Have I done anything wrong? Especially in the first part where I think it should be easy to pull all values for that prompt as default.

Appreciate all the help I can get.

Thanks!

Solution

  • I think your main issue is in using '' to represent null. I would recommend not doing that, since other languages treat empty strings very differently than SQL does.

    So first, decode is frowned upon these days, for several reasons. Instead of e.g. 

    EMPLID LIKE DECODE (:1, '', '%', :1)

    I'd just do

    EMPLID LIKE NVL(:1, '%')

    Second, and this is probably the part giving you trouble, these lines aren't doing what you want:

    AND A.CHECKLIST_STATUS = :4 
OR '' = :4

    The second line will never be true. Any comparison against a null will be null, not true. So '' = '' will return null, not true. And it's generally considered good practice to use parentheses with mixed AND/OR, for clarity. So instead, I would do something like this:

    AND (A.CHECKLIST_STATUS = :4 OR :4 is null)