Question

1
Replies
19
Views
JeffGo Member since 2017 14 posts
Appriss, Inc
Posted: 6 months ago
Last activity: 6 months 1 week ago
Solved

How to generate a specific sql statement where one filter is required only if a second filter exists

I've got a strange sql statement I need to build and I'm having trouble getting it done in Pega.

Suppose I have the following table:

Original DB table
NameOrderLinkStatusLinkDate
Jorge Ymeniz5331-abLinked20200215
Frank Wright8633-dwUnlinked 
Sasha Stromburg8135-bsOn Hold 
Liddy Dormund4872-ekLinked20200110

Now I need to return all Unlinked, On Hold, and only Linked if they exist between a couple of user input dates.

The SQL statement would look like:

select * 
from myTable
where (LinkDate >= '20200201T000000.000 GST' or LinkDate is null)
   or (LinkDate <= '20200227T235959.999 GST' or LinkDate is null)

This would return the following result:

Filtered table
NameOrderLinkStatusLinkDate
Jorge Ymeniz5331-abLinked20200215
Frank Wright8633-dwUnlinked 
Sash Stromburg8135-bsOn Hold 

And I can't use LinkStatus, because that is also a filter that the user can choose.

The problem I'm running into is that when I leave the FromDate and ToDate blank (from the user selected filter page), I get the following:

select * 
from myTable
where (LinkDate is null)
   or (LinkDate is null)

Which then excludes any record that has a LinkDate. In this situation, I want all the records returned.

Essentially, I need to do is:

if FromDate != ""
then return "SourceDate >= FromDate OR SourceDate is null";
else return;

if ToDate != ""
then return "SourceDate <= ToDate OR SourceDate is null";
else return;

 

Pega Platform 7.3.1 Low-Code App Development Dev/Designer Studio Government
Share this page LinkedIn