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

SailRCG

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('" +
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('" +
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('" +
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('" +
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

SailRCG

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')"}
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')"};