Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Incremental Refresh Hourly: how to use range start and end?

Hi everyone, I have a dataset I need to refresh with incrementals, since each hour bring approximately 20M registers. The field in the dataset that I want to use for incrementals is 'date' and it's ...
Need Help
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
3 years ago

I Think the error you're encountering is due to a mismatch in data types. You are trying to concatenate a date/time value (RangeStartSearches) with a text string in your query, which Power Query doesn't allow by default. To fix this issue, you need to convert the date/time parameter values to text before concatenating them into your query string. Here's how you can modify your query to achieve this:

```M
let
// Convert date/time parameters to text
RangeStartSearchesText = Text.From(RangeStartSearches),
RangeEndSearchesText = Text.From(RangeEndSearches),

// Use the text values in your query
Source = Odbc.Query("dsn=Simba Athena",
"SELECT *, now() ""Last Refresh"" FROM ""database"".""searches_hourly""
where ""date"" >= cast(" & RangeStartSearchesText & " as timestamp)
and ""date"" < cast(" & RangeEndSearchesText & " as timestamp) limit 10 ")
in
Source
```

By converting the date/time parameters (RangeStartSearches and RangeEndSearches) to text using the `Text.From` function, you ensure that their values are treated as text strings in your query, allowing you to concatenate them without encountering a data type mismatch error.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks for your reply!
I have done this by creating to new consults with the text form of the parameters but is still not working. Altough now the error message is different:

DataSource.Error: ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 1:114: mismatched input '12'. Expecting: '%', '*', '+', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', '[', '||', <EOF>, <predicate> [Execution ID: ]
Detalles:
DataSourceKind=Odbc
DataSourcePath=dsn=Simba Athena
OdbcErrors=[Table]

 

Edit: I've realized this is due to missing quotation in the where statements, when fixing that, this is the error I'm having now:


DataSource.Error: ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: COLUMN_NOT_FOUND: line 1:104: Column ' 9/15/2023 12:00:00 am ' cannot be resolved or requester is not authorized to access requested resources [Execution ID: f2c169d4-ac33-4ecb-894a-7356117471d4]
Detalles:
    DataSourceKind=Odbc
    DataSourcePath=dsn=Simba Athena
    OdbcErrors=[Table]