SELECT COALESCE not working

Applications Forms
#1

Hello,
sc9 with Microsoft SQL 2012 as DB.

In a single line form I use the following SELECT COALESCE statement to update the value of the “versione” column (declared as Select type).

SELECT COALESCE(1+MAX(versione),1)
FROM dbo.SAMPLES_CH
WHERE season=’{season}’ AND pattern=’{pattern}’

The syntax in the SQL GUI is working.
I enabled debugging in the SC form application, and the SQL syntax is the same , but in Scriptacse, the field does not update.
Is this by design (problem with COALESCE maybe ? ) , or is it a bug ?
I am looking for a workaround .

Thanks, Federico

#2

Update:

  1. SELECT COALESCE(1+MAX(version),1) … does not work
  2. SELECT 1+ISNULL(MAX(version),0) … neither .

I guess it is a bug in the scriptcase sql parser . Anybody from scriptacase team can confirm ?
Both SELECT work toward the sql server outside scriptcase .

Best Reagards
Federico

#3

Workaround:

SELECT
CASE WHEN MAX(version) IS NULL THEN 1 ELSE 1 + MAX(version) END
FROM …

Bug in sql parser exists I guess