When I run this Script in SQL Query, OutSystem pop up this error as picture below
I have already checked again syntax of IF function in SQL and make sure this syntax is correct. I don't understand why it pop up this error. Here is script:
SELECT {Analytic_NewLogin}.[CreateDate] as "CreateDate", sum(if({Analytic_NewLogin}.[SRCTierId] <> 0, 1, 0)) as "SRCNewLoginQuantity", sum(if({Analytic_NewLogin}.[SRLTierId] <> 0, 1, 0)) as "SRLNewLoginQuantity" from {Analytic_NewLogin}
Any help is appreciated. Thanks!
Hi Huy,
If you are using MySql with your Outsystems that should work but I never try it. Did you try to use the IIF or the CASE?
If this is the final query and isn't getting more complex you can transform this into an aggregate and that would be the best option.
@Sravan SP isn't a good option maintenance wise. One of the good things of using Outsystems is having all the code in the same place instead of scattered in multiple places
Regards,
Marcelo
Marcelo Ferreira wrote:
Hi Marcelo,
I have already solved this task by using aggregate.
Normally inside the select you use CASE statement for it, unless you are using SQL server 2012 or recent where you can use IIF statement.
So in this case, you mean I can't use IF function in Sum function to calculate data base on condition ?
I can run this script easily in MySQL but can't in SQL Outsystem.
Ironically, I can use aggregate (aim to solve basis query) in Outsystem to resolve this quite complex calculate requirement base on condition while can't do it in SQL(aim to solve complex query).
Just as a complement to Marcelo's reply.
When you use advanced queries the OutSystems functions aren't available, you only can use SQL.
Hi Huy Nguyen,
Try to wrap query in SP and use SP in OutSystems while querying, this way we can avoid things which are working in SQL Server and not working in OutSystems.
Sravan
Sravan Vanteru wrote:
Hi Sravan,
Can you explain what is SP?. How to wrap query in SP?