Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Is there no way to make a date calculation where the number ...


Peter Vazny

Recommended Posts

Is there no way to make a date calculation where the number of units is based on a database field without breaking SQL translation

I need to create a filter based on a date that is calculated from a date + number of days, both of which are fields in a database. Pseudo code

FUTURE_DATE/MDY = DATEADD(DATE_LAST_CONTACT, 'D', DAYS_TO_ADD)

 

When I try to run it, I get

 

COMBINATION OF PARAMETERS OF FUNCTION DATEADD CANNOT BE CONVERTED TO SQL

 

This would not be terrible if the FUTURE_DATE was not involved in a filter. But as is, the database returns all records and WEBFOCUS weeds them out. Which is less than optimal.

I am aware of DB_EXEC, but I am not aware of how to use it within InfoAssist. I can make the edit manually and add WITH DATE_LAST_CONTACT to the define, but this should not be this much hassle.

Link to comment
Share on other sites

warren.hinchliffe:

 

I wonder if DAYS_TO_ADD is the cause. Where does that field come from.

 

 

Yes, that is the issue, but should it be I need to calculate a date based on last contact and a number of days specific to each customer. So DAYS_TO_ADD also comes from the database. If I change it to a constant, lets say 30, this gets accurately converted to SQL:

DATE_LAST_CONTACT + (30) days

 

However as soon as I try to use anything else but a constant, it flakes out.

 

 

 

david.beagan:

 

Did you try using DATEDIF in the Define and then use DAYS_TO_ADD in the filter

 

 

Yes that works, but is it really a solution I have heard that one can build a functioning processor that supports only a single instruction, but that does not mean it should be built. I am sorry for my rant, as it is not meant to be directed at you. I am just dumbfounded that there exist a tool in the 21st century that is not able to add a variable to a date without crippling consequences.

Link to comment
Share on other sites

@debra.waybright did you run SQL trace This is not a hard stop error. It only shows when you run SQL trace, but it has consequences. Since the DATEADD is used in a filter and is not translated to SQL, WebFOCUS pulls all rows instead of just the necessary rows, which as you can imagine is not desired.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...