Optional Dates (ORACLE) == null works != null does not work?
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
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
0
Comments
-
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')"};0
Categories
- All Categories
- 108 Developer Announcements
- 53 Articles
- 106 General Questions
- 144 IM Services
- 43 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 899 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 119 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management