In db filter not working with Vertica when creating new fields

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?

In Database

In-Database

Accepted answers

Kelkomano27

The issue was solved by using a limit clause in the query. The issue is not the actual amount of data but not having the limit clause. Once I added this the workflow ran with no issue. As someone mentioned above these issues are hard to diagnose given the fact that the error I was getting does not have anything to do with the actual problem

All comments

jdminton

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?

simonaubert_bd

@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)?

Kelkomano27

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

Kelkomano27

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:

Kelkomano27

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?

simonaubert_bd

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.

jdminton

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?

nagakavyasri

Could you post Summarize tool field configuration here?

apathetichell

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?

Kelkomano27

I haven't I would give it a try

Kelkomano27

I'm not casting them because all of the fields appear to have the correct data type according to the metadata I see.

Kelkomano27

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703