Skip to main content

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.

Without Group By
With Group By

 

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.)

https://docs.ifs.com/techdocs/23r2/040_tailoring/500_business_process_automation/040_workflow_tooling/#how_to_enable_trace_details

 

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.

Reply


Terms & ConditionsCookie settings