I'm working with Vertica in DB. I created a new field which is simply the year difference between two dates. The field is created with no issue as an int64 as expected. Then I tried to filter by those with a difference over 7 years but for some reason in database filter throws an error when using new fields, this does not happen when filtering using fields already existing in the database. I have tried all possible casting but I don't know what the issue is. Any help?
Solved! Go to Solution.
I'm not sure we can see enough of your workflow to provide much assistance. It appears the only error is related to a timezone that is somehow showing as "u-". Is it possible that the field you are creating is creating that value or that the data type for the field in the formula tool is looking for some other format without timezone? I don't see a write to database tool, so it shouldn't be related to creating a field.
Could you at least provide the formulas you are using and the fields, lengths, and data types for the formula tool?
@Kelkomano27 Could you please disable the cache in all the browse in-db tools, relaunch and give the content of the said browse tools (especially on the new field)?
Hey Thanks for replying,
The formulas I am creating are working fine because I am able to see the results as expected is when applying the filter I get that error in the browse tool
I cleared the Cache in all browses. All of them show the data as expected except the last one where I get the error mentioned above:
It seems the problem is with the Summarize in db tool. No matter which field I tried to group by I get the same error. Any idea on why is behaving like this?
Ok, you can use a Dynamic output in db to retrieve the query (or consult your logs). with that, I think you can have a meeting with your Database administrator. Strange behavior by the way.
I'm not able to recreate, but I don't have the DB you're working with. I'm wondering if you filter out the nulls prior to the formula tools if it will no longer have the error. Have you tried this?
Could you post Summarize tool field configuration here?
with in-db the errors can be fairly hard to diagnose. this may be totally unrelated to your calculations and instead be related to something totally totally different. Are you casting any fields in your initial query?