Concat function in Mendix OQL Query

0
In Mendix i have to use Reports to show data in the form of Graphs. To show Reports i am using below OQL Query : FROM Texisle.InquiryProduct AS inqProduct WHERE inqProduct/Type_ = 'Product' and inqProduct/InquiryProductType = 'Line_Pipe' and inqProduct/createdDate IN $IP_Date and inqProduct/RecordType = 'Main' GROUP BY inqProduct/OD_DisplayAs, inqProduct/WallPPFSDR_DisplayAs SELECT sum(inqProduct/Quantity) AS Quantity ,ROUND(inqProduct/OD_DisplayAs,2)+'-'+ROUND(inqProduct/WallPPFSDR_DisplayAs,2) AS OD_WallPPF As you can see in the OQL Query i am using '+' concat sign in line no 5 to concat 2 columns in a single column with a name as OD_WallPPF. This is because in my project i have a requirement that we need to show Y axis attribute as a combination of 2 different attributes. That means in the Report my Y Axis will show value as "23-34" where 23 is the OD_DisplayAs value and 34 is the WallPPDSDR_DisplayAs value.  The above Query works fine in my localhost. But when i tries to publish this application with server as SQL Server then it throws exception "Arithmetic overflow error converting varchar to data type numeric". I tried debugging this using SQL Server tool to find why this SQL Query fails and found that SQL Query failes due to the use of '+' concat sign to concat 2 columns in a single column. Then i tried using concat function in SQL Server instead of + sign and that works fine with SQL Server. Means instead of concating using + sign i am using concat function, something like : Concat(ROUND([od_displayas],2),'-',ROUND([wallppfsdr_displayas],2)) AS OD_WallPPF Then i tried using the concat function in my OQL Query so that whever OQL Query is converted into SQL Query it will have concat function instead of + sign. But unfortunately i didn't found any concat function for OQL Query.    Could you please help me to know how to use concat function in OQL Query or any alternate function that we can use to concat 2 or more columns. If there is no as such method that we can use then please help me to fix this issue.
asked
1 answers
1

 

Try to add a string '' before your ROUND or add a CAST to string (CAST(ROUND(inqProduct/OD_DisplayAs,2) AS STRING))

SELECT  sum(inqProduct/Quantity) AS Quantity ,''+ROUND(inqProduct/OD_DisplayAs,2)+'-'+ROUND(inqProduct/WallPPFSDR_DisplayAs,2)+'' AS OD_WallPPF

 

let us know :)

answered