Name is required.
Email address is required.
Invalid email address
Answer 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."

Example: dateadd('d', 1, current_date(), 'd')

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

dateadd('d', 1-datepart('dw', current_date()), current_date())

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
dateadd('d', 1-datepart('dw', current_date()), current_date())
from whatever
where startdate=dateadd('d', 1-datepart('dw', current_date()), current_date())

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.

2 Community Answers

Matillion Agent  

Ian Funnell —

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 —

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

Post Your Community Answer

To add an answer please login