System Variables - Giving Error 7481 - Inconsistent Types

I'm getting an error in my connector when I try to use the '@MONTHSTART' system variable:

I have the following SQL statement (Which works perfectly, giving the desired result):
Select "vm-stock", "vm-make", "vm-comment", "vm-buydate", "vm-serial", "vm-cost", "vm-addcost" FROM "vm-inventory"
Where ("vm-sold" = 0 OR ("vm-sold" = 1 AND "vm-buydate" > '2017-06-01'))

Now to automate the report to run every month, I want to use the system variable @MONTHSTART@ in place of the static '2017-06-01' (start of this month), So I attempted the following:

Select "vm-stock", "vm-make", "vm-comment", "vm-buydate", "vm-serial", "vm-cost", "vm-addcost" FROM "vm-inventory"
Where ("vm-sold" = 0 OR ("vm-sold" = 1 AND "vm-buydate" > @MONTHSTART@))

I receive the error "Inconsistent Types"

Views: 68

Reply to This

Replies to This Discussion

Hi modhorat.

Try placing @MONTHSTART@ in quotes, i.e. '@MONTHSTART@'.

Let me know if this doesn't work.

Now it gives me "Error in row"

What Sage business solution are you using?

Alchemex standalone on a Progress Database

Hi modhorat.

Just to let you know that I am looking at this and will get back to you shortly.

Don't stress, I think the error comes from the progress side, when the actual fields (columns) have some 'funny' incorrect data (example text in a numeric)

This is what eventually worked: '@YEAR@' + '-' + '@MONTH@' + '-' + '01'

Cool.  I was going to suggest checking the format of the system variable and making sure it aligns to the column format.  The other option would be to modify the column using something like a SQL CONVERT function.  Not sure if this would work with Progress through.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2017   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service