AnsweredAssumed Answered

Adding a filter to a SQL string

Question asked by remco on Dec 31, 2018
Latest reply on Dec 31, 2018 by comment

I have a Global field to calculate a Total with a SQL string. I'am not very familiar with SQL and i want to add an additional filter.

 

I would like to add the following additional filter to the SQL String: Count only field "Totaal" from table "Facturen" where field "Type" is "Origineel".

 

Let (

  [

  month = Bedrijfsdashboard::Huidige maand ;

  year = Bedrijfsdashboard::Huidig jaar ;

  status = Statusfilter ;

        decimal = Left ( Evalueren ( 1/2 ) ; 1 ) ;

  SQL = ExecuteSQL (

  "

  SELECT SUM ( a.\"Totaal\" )

  FROM \"Facturen\" a "  &

  If (

  IsEmpty ( status ) ;

  "WHERE a.\"Maand\" = ? AND a.\"Jaar\" = ? AND a.\"Statusvolgorde\" IN ( 0 ,1, 2 ) " ;

  "WHERE a.\"Maand\" = ? AND a.\"Jaar\" = ? AND a.\"Statusvolgorde\" = ? "

  )

  ; "" ; ""  ;

  month ; year ; status

  )

  ] ;

 

  If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 ) 

 

  )

Outcomes