Denodo HTTP Client given date in request

Question

I need to query HTTP/JSON datasource with parameters like this:

/values?Name=Foobar&Start=2024-01-16&End=2024-01-17 (the previous and the current days)

so I create a Base view with relative URL like this:

/values?Name=Foobar&Start=@start&End=@end

and then I try to use functions for START and END variables, e.g:

START: FORMATDATE('yyyy-MM-dd', ADDDAY(now(), -1)) (Is this correct for yesturday??)

END: FORMATDATE('yyyy-MM-dd', now())

but denodo uses formulas as a plaintext:

Unable to obtain the data structure:
com.denodo.util.exceptions.InternalErrorException:
Error retrieving data from '.../values?Name=Foobar&End=FORMATDATE('yyyy-MM-dd',%20now())'.
HTTP error code: 404.

What is the correct way to do this?

Answer 1

This question was answered at Denodo Community website with Denodo team

Hi,

Generally the values in interpolation variables are passed as strings. If I need to pass dynamic values in the interpolation variables, I would follow the below steps:

1. I would create a new HTTP/JSON datasource named ds_sample with the base URL as mentioned below:

    <BASE_URL>/values?Start=@start&End=@end
   

2. After saving the data source, I would create a base view, bv_sample over the ds_sample source.

3. Then, I would create a new view named dates with two fields Start and End with the field expression formatdate('yyyy-MM-dd', addday(now(), -1)) for Start and formatdate('yyyy-MM-dd', now()) for End fields.

4. I would create a Join view for the two base views dates and bv_sample and I would join the fields dates.start, dates.end and bv_sample.start, bv_sample.end respectively.

5. Finally, on executing the join view the results of the query matching the given conditions will be retrieved.

Hope this helps!

UPD

For beginners, the least obvious item may be the one about "create a new view named dates", I still don't know the correct way, too=) So you can create any type of View in the UI, open it's VQL editor and put this code there:

CREATE OR REPLACE VIEW dates FOLDER = '/general'
 LAYOUT (bv_sample = [20, 20, 200, 205])
 AS SELECT
  formatdate('yyyy-MM-dd', addday(now(), -1)) AS "Yesterday",
  formatdate('yyyy-MM-dd', addday(now(), -1)) AS "Start",
  formatdate('yyyy-MM-dd', now()) AS "Today",
  formatdate('yyyy-MM-dd', now()) AS "End"
 CONTEXT ('formatted' = 'yes');