Hey everyone...really need help on what I'm missing here. I've added a couple of screenshots to help. I'm simply trying to filter a date column from a table in an Oracle Data Warehouse to be equal to 1 year ago. The logic seems correct, but I continue getting the error stating an identifier was expected. Any thoughts?
Hi @wcsmith
This might be due to date formatting. Can you please check what is the date format on the Oracle DB (LICENSE_EFFECTIVE_DATE column)? The expression you used in flows gives you MM/dd/yyyy. I am guessing this what is causing the error. You can use the formatdatetime()function to appropriately format the date type.
Hope this Helps!
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Hi @wcsmith ,
Please try the following ways:
Hope this could be helpful.
Best regards,
Mabel
I think I have the formatting the same now.... but I get the following error...
We cannot apply operator < to types Text and DateTime. Below is what I'm getting as a result...
Can you please share a screenshot of the flow? I also wanted to see a sample value from the date column of your database.
Screenshots below...I've tried many different formats and can't seem to get them to work. I either get the error about missing something in position XX...or I get the error stating it can't do the < operator with DateTime or Text
FLOW...Format date is 'g' I've tried to put ' ' around the function as well
Power BI date results
SQL Developer date results
This is what the date looks like in the FLOW if I don't filter anything....
Okay. can you modify your filter statement as LICENSE_EFFECTIVE_DATE ge 'ticks(getPastTime(1,'Year','d'))' ?
Edit: you might aso want to have a look at https://docs.microsoft.com/en-us/connectors/oracle/
@yashag2255 wrote:
Okay. can you modify your filter statement as LICENSE_EFFECTIVE_DATE ge 'ticks(getPastTime(1,'Year','d'))' ?
Edit: you might aso want to have a look at https://docs.microsoft.com/en-us/connectors/oracle/
That allowed the flow to run, but it did not return any results. Also...this format yyyy-MM-dd allows the flow to run, but doesn't return any results. I'm positive results exists, so for some reason it's still not recognizing the date.
I see that the date format in the database is "30-SEP-19" so you can try formatting the date that way. It would be ticks(formatDateTime(getPastTime(1,'Year','d'),'dd-MMM-yy')). Can you check with this expression once?
using the 'eq' function...the flow works but I get zero results. using the 'ge' function..which is what I need...it gives me
@wcsmith This looks like a limitation in the Oracle DB connection. Have you had a chance to look at this ->
https://docs.microsoft.com/en-us/connectors/oracle/
Hope this Helps!
I'm not sure it's work for SQL but have you try endsWith( LICENSE_EFFECTIVE_DATE , '18' ) because i try on an Excel file and it's actually work for your date format but only for 1 year .
endsWith( LICENSE_EFFECTIVE_DATE , '@{formatDateTime( utcNow() ,'yy')}' )
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Did you know that you could restore a deleted flow? Check out this helpful article.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
User | Count |
---|---|
6 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
8 | |
8 | |
7 | |
5 |