I am trying to create a query in datasource desinger where the view I am using includes CREATED_DATE in a datetime format. I have no problem creating a new field in the query to translate this to date or month, but I am struggling to use the same statements in ‘Group by’.
Whenever I try to use any type of calculated value (such as to_char) in Group by, to get the results on a daily or monthly level, I get an error message. Is there any way to resolve this?
I made a simple test with a GROUP BY and it works fine.
If you post more information about your datasource it is easier to help you.
Now I can only guess, you are not selecting CREATED_DATE without exactly the same to_char function as in the GROUP BY clause?
Hi,
Thanks for the reply and that’s interesting! Here are two pictures from the query I am trying to run (one with and one without Group By). I have also tried to remove the filter but get the same result.
The column with the TO_CHAR in the GROUP BY example still has datatype Date. Can that be the reason?
No, still no success unfortunately. I tried to change the datatype to ‘Text’, ‘Date’, ‘Datetime’ but still get the same result no matter which I select.
The WHERE statement seems a bit different formatted in the GROUP BY example. No strange characters in that part?
No, even when the WHERE statement is blank, I still get the same behavior.
I suggest you enable the dev tools link and install the IFS Cloud devtools web extension to be able to see the real error: (Link is for workflows but it is the same window you can see other things.)
Here I have created a datasource with incorrect SQL and made a preview:
Thank you! That clarified the problem. There were two different problems that was generated when the Where-statement was included or not included.
The problem seemed to be using the #START_OF_THIS_MONTH# in the Where statement. I don’t know if this is a general issue or if it is due to me converting the same variable with to_char. But upon replace it with TRUNC(sysdate, ‘MM’) the query worked!
Thanks for learning me a new skill with the IFS Cloud DevTool, I can feel it will come in handy in more cases.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
