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

[Peter Vazny]

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.

[Warren Hinchliffe]

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

[David Beagan]

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

[Peter Vazny]

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.

[Debra Waybright]

I tried this and it worked fine for me, didnt error on missing Days to Add. What format is your DAYS_TO_ADD What format is DATE_LAST_CONTACT Maybe the issue is in there.

[Peter Vazny]

@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.

[Debra Waybright]

Ah. Yes, WebFocuss Give me everything and let me do the filtering. That can cause issues.

That is typically when I convert it to SQL myself and put WebFocus code around it.