Peter Vazny Posted May 13, 2021 Share Posted May 13, 2021 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 More sharing options...
Warren Hinchliffe Posted May 14, 2021 Share Posted May 14, 2021 I wonder if DAYS_TO_ADD is the cause. Where does that field come from. Link to comment Share on other sites More sharing options...
David Beagan Posted May 14, 2021 Share Posted May 14, 2021 Did you try using DATEDIF in the Define and then use DAYS_TO_ADD in the filter Link to comment Share on other sites More sharing options...
Peter Vazny Posted May 14, 2021 Author Share Posted May 14, 2021 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 More sharing options...
Debra Waybright Posted May 14, 2021 Share Posted May 14, 2021 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. Link to comment Share on other sites More sharing options...
Peter Vazny Posted May 14, 2021 Author Share Posted May 14, 2021 @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 More sharing options...
Debra Waybright Posted May 17, 2021 Share Posted May 17, 2021 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now