• 1975061
• 2487672
• 1991954
• 2342958
• 2234727
• 2963147
• 2786817
Name is required.
Exceeding max length of 5KB Issuses with Calculations in API Queries

A bunch of seemingly similar issues...

1. I'm looking at https://redshiftsupport.matillion.com/customer/portal/articles/2895383?b_id=8915

The month() function is missing here. It works for me just fine.

2. The dateformat parameter to the dateadd function does not seem to work. Any time I try to use it I get the error "Function 'dateadd' accepts at most 3 arguments."

3. Arithmetic when assigning to a variable is flawed...
I can create a field expression of

smalldatetimefromparts(year(current_date()), round(month(current_date())/3,0)*3+1, 1, 0, 0)

however if I assign that to a variable I get the error "The value of the attribute could not be accessed: The attribute does not exist.

example:

SELECT *,
--next line is fine
smalldatetimefromparts(year(current_date()), round(month(current_date())/3,0)*3+1, 1, 0, 0)
from whatever
where
startdate=smalldatetimefromparts(year(current_date()), round(month(current_date())/3,0)*3+1, 1, 0, 0)

BTW, this complicated formula is attempting to make the startdate be the beginning of this quarter.

4. Another similar example where arithmetic is flawed:
I can create a field expression of

however if I assign that to a variable I get the same error as above - "The value of the attribute could not be accessed: The attribute does not exist.

example:

select *,
--next line works fine
from whatever

BTW, this formula is attempting to make the startdate be the beginning of this week. In this example, if you remove "1-", then at least I get no error.

Note that for example 3 and 4 here, I've tried taking substring() of each formula, but it makes no difference.

I have a solution for problems 3 and 4. I create a small python script which does the calculation and assign the result to a vriable, which then the API query uses. So, I don't necessarily need any assistance here. Just reporting some issues I have found. Matillion Agent

Ian Funnell — Apr 17, 2019 01:53PM UTC

Hi Quinn,

Thanks for bringing point 1 to our attention.

For point 2, does it work if you remove the 4th argument ‘d’?

For points 3 and 4, you’re pretty much at (maybe beyond) the limit of sophistication for that component! I agree with your workaround and very much recommend that approach to other customers reading this thread… split the logic into smaller pieces and take a ‘divide and conquer’ approach:

• Use a Python Script to set a Matillion Text variable to the date you need (similar logic but done in Python)
• Using a Text variable rather than a DateTime means you can explicitly control the format mask
• Send the variable to the API call following the guidelines in this document
• Run the API Query

Best regards,
Ian Quinn Wildman — Apr 17, 2019 02:34PM UTC

Yes, for point 2, if I remove the fourth argument it works fine.