PRD Custom report failing when using OR in WHERE?

[stianbl]

Hi,
I'm creating some custom reports for SAP LMS, but I have some strange issues regarding adding custom filters.

This should be fairly simple, but seems it's not.

Quick breakdown:
-Adding a new filter parameter. Type Integer, Text box.
-Adding filter to SQL WHERE:

and (days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion] or [DeltaDaysCompletion] = 0)

This is failing. Tried IS NULL, and others, but all are failing.

If I remove the OR condition it works:
and days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion]

I really need a way to be able to check multiple OR conditions. This is what I actually try to acieve:

  AND (days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion] OR [DeltaDaysCompletion] = 0)
  AND (days_between(c.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedUser] OR [DeltaDaysUpdatedUser] = 0)
  AND (days_between(a.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedLearning] OR [DeltaDaysUpdatedLearning] = 0)

The reason for this is I want to fetch history records for an item, completed past 4 days, OR history records where history have been added the past 4 days (In case record learning have been used, back more than 4 days), OR the user profile have been updated past 4 days, in case it was missing important information for target system, and now have this information provided.

If anyone have had the same issue and have found a way, please let me know 🙂

I also tried using CASE, but still failing:

  and 1 = (CASE WHEN ( (days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion]
  			OR days_between(c.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedUser]
  			OR days_between(a.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedLearning]))
  			OR ([UseDelta] = false)
  			THEN 1
  			ELSE 0 END)

[stianbl]

boegersb: I did try again, with parenthesis.

This works, but is not what I want:

and days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion]
and days_between(c.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedUser]
and days_between(a.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedLearning]
and days_between(a.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedLearning]

This does NOT work (Report says failed), but it's what I want: (Actually, I'd like to disregard the filters if the value is 0 or null. But first step is actually making an OR condition work for my report.

and ((days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion]) or (days_between(c.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedUser]) or (days_between(a.LST_UPD_TSTMP, current_date) <= [DeltaDaysUpdatedLearning]))

Anyone got any information or tips regarding this? Temporary I just add a large value to the two other delta filters, so they always return true. But that is the same as just removing them. I need a way to fetch learning history for records that have been either:

1. completes past X days.
2. registered into the Learning system in the past X days (for completions in the past, but newly registered)
3. user account have been updated in the past X days.
If any of the above is true, include it in the report.

[Brian_Boegs]

Did you try adding some parethesis to it:

and

(

(days_between(a.compl_dte, current_date) <= [DeltaDaysCompletion])

or

([DeltaDaysCompletion] = 0)

)

[stianbl]

Thanks for your comment. Yes, I also did try that (forgot to mention it). I can give it another try just to be absolute sure.

What I want is simply a way to have a "delta" filter. Where if I leave it blank (or 0) it is disregarded.