Welcome and thank you for joining our new OpenText forum. Your questions, responses, best practices, and tips shared with other members will help make this channel vibrant. We're glad you're joining us and look forward to collaborating with you online.

Check out the Getting Started With OpenText Forums for tips on personalizing your experience.

Optional Dates (ORACLE) == null works != null does not work?

SailRCGSailRCG Junior Member
edited December 31, 1969 in Designing Analytics Reports
Hi,

I have a data set where I am trying to modify the query depending on date selections or no date values. Getting this to work with a single date attribute is easy enough, provided I am evaluating for == null date parameter first. On the other hand if I evaluate != null and do the " where " append with the date parameters first, the script fails.

For example: (THIS WORKS)

if (
params["StartParam1Date"].value == null &&
params["EndParam1Date"].value == null ||
params["StartParam1Date"].value == 'null' &&
params["EndParam1Date"].value == 'null' ||
params["StartParam1Date"].value == ' ' &&
params["EndParam1Date"].value == ' '){
this.queryText = this.queryText }
else {
this.queryText = this.queryText +
" where DATE1 >= to_date('" + params["StartParam1Date"].value +
"','YYYY/MM/DD') and DATE1 <= to_date('" +<br> params["EndParam1Date"].value + "','YYYY/MM/DD')"};

(THIS DOES NOT)

if (
params["StartParam1Date"].value != null &&
params["EndParam1Date"].value != null ||
params["StartParam1Date"].value != 'null' &&
params["EndParam1Date"].value != 'null' ||
params["StartParam1Date"].value != ' ' &&
params["EndParam1Date"].value != ' '){
this.queryText = this.queryText +
" where DATE1 >= to_date('" + params["StartParam1Date"].value +
"','YYYY/MM/DD') and DATE1 <= to_date('" +<br> params["EndParam1Date"].value + "','YYYY/MM/DD')"}
else {
this.queryText = this.queryText };

The reason I prefer the second method is that I'll need to add another set of date range parameters and anticipate needing to evaluate for paramSet1 != null followed by else if paramSet2 != null, then ending with the final drop-out-the-bottom this.queryText = this.queryText if neiter date parameter sets are selected. For example I anticipate it looking something like:

if (
params["StartParam1Date"].value != null &&
params["EndParam1Date"].value != null ||
params["StartParam1Date"].value != 'null' &&
params["EndParam1Date"].value != 'null' ||
params["StartParam1Date"].value != ' ' &&
params["EndParam1Date"].value != ' '){
this.queryText = this.queryText +
" where DATE1 >= to_date('" + params["StartParam1Date"].value +
"','YYYY/MM/DD') and DATE1 <= to_date('" +<br> params["EndParam1Date"].value + "','YYYY/MM/DD')"}
else if(
params["StartParam2Date"].value != null &&
params["EndParam2Date"].value != null ||
params["StartParam2Date"].value != 'null' &&
params["EndParam2Date"].value != 'null' ||
params["StartParam2Date"].value != ' ' &&
params["EndParam2Date"].value != ' '){
this.queryText = this.queryText +
" where DATE2 >= to_date('" + params["StartParam2Date"].value +
"','YYYY/MM/DD') and DATE2 <= to_date('" +<br> params["EndParam2Date"].value + "','YYYY/MM/DD')"}
else{
this.queryText = this.queryText };


Any ideas why the second variation does not work when a date selection does not have a value?

Thanks

RG

Comments

  • SailRCGSailRCG Junior Member
    edited December 31, 1969
    Though I am still not sure why the reverse of the first if() did not work for me, I've found a way to make two date attributes date ranges Optionally selectable:

    if( (params["StartDate1Date"].value == null && params["EndDate1Date"].value == null) &&
    (params["StartDate2Date"].value == null && params["EndDate2Date"].value == null)){
    this.queryText = this.queryText}
    else if ( (params["StartDate1Date"].value != null && params["EndDate1Date"].value != null) ||
    (params["StartDate1Date"].value != 'null' && params["EndDate1Date"].value != 'null')&&
    (params["StartDate2Date"].value == null && params["EndDate2Date"].value == null) ||
    (params["StartDate2Date"].value == 'null' && params["EndDate2Date"].value == 'null')){
    this.queryText = this.queryText + " where DATE1 >= to_date('" + params["StartDate1Date"].value + "','YYYY/MM/DD') and DATE1 <= to_date('" + params["EndDate1Date"].value + "','YYYY/MM/DD')"}<br> else if ( (params["StartDate1Date"].value == null && params["EndDate1Date"].value == null) ||
    (params["StartDate1Date"].value == 'null' && params["EndDate1Date"].value == 'null')&&
    (params["StartDate2Date"].value != null && params["EndDate2Date"].value != null) ||
    (params["StartDate2Date"].value != 'null' && params["EndDate2Date"].value != 'null')){
    this.queryText = this.queryText + " where DATE2 >= to_date('" + params["StartDate2Date"].value + "','YYYY/MM/DD') and DATE2 <= to_date('" + params["EndDate2Date"].value + "','YYYY/MM/DD')"};
Sign In or Register to comment.