Incorrect syntax near keyword IF in outsystem ?

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,

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.

Regards,

Marcelo

Hi Huy,

Just as a complement to Marcelo's reply. 

When you use advanced queries the OutSystems functions aren't available, you only can use SQL.

Marcelo Ferreira wrote:

Hi Huy,

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.

Regards,

Marcelo

Hi Marcelo,


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

Regards,

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

Solution

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

Solution

Sravan Vanteru wrote:

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

Hi Sravan,

Can you explain what is SP?. How to wrap query in SP?

Regards,


Marcelo Ferreira wrote:

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

Hi Marcelo,

I have already solved this task by using aggregate.

Regards,