I'm testing knowage system, my backend is MSSQL while knowage server is my sql. I found on dataset audit log this query:

select "ATC_1" AS "ATC_1",  SUM("Importo_Scarto") AS "Importo_Scarto", SUM("Q_Scarto") AS "Q_Scarto"
from (SELECT 
  FROM [dbo].[MyTable]) T  
  where (("ANNO","MESE") IN ('2017','10'),('2017','11'),('2018','01'),('2018','02'),('2018','03')
  and ("Invio") IN ('3'),('1'),('2') )
  group by "ATC_1"

the error I get is this:

com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near ','

closed as off-topic by LowlyDBA, mustaccio, RDFozz, MDCCL, hot2use Jun 21 '18 at 8:06

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, mustaccio, RDFozz, MDCCL, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    SQL Server does not allow row constructors for IN clauses. The first could be refactored as an inner join and the second as a simple IN list of values. – Dan Guzman Jun 20 '18 at 11:38
  • 1
    What tool auto-generated this query? You should get a tool that understands the syntax of the database platform you're using. – Aaron Bertrand Jun 20 '18 at 12:08
  • The tool is Knowage CE, and is supposed to handle sql-server syntax – DDS Jun 20 '18 at 12:23
  • IN ('3'),('1'),('2') will not work in sql-server. Change it to columnName IN ('3','1','2') – jean Jun 20 '18 at 14:15

The condition:

where ( ("ANNO","MESE") IN ('2017','10'),('2017','11'),
   and  ("Invio") IN ('3'),('1'),('2') 

has 2 issues:

  • first, it needs parentheses around the IN lists (and the ones you have around the two sub-conditions are redundant):

    where   ("ANNO", "MESE") IN ( ('2017','10'), ('2017','11'),
                                  ('2018','01'), ('2018','02'), ('2018','03')
       and  "Invio" IN ( '3', '1', '2' ) 
  • second, even if you fix the above, it won't work in SQL Server as this type of syntax (the first sub-condition, that combines IN with tuples) has not yet been implemented. You can rewrite in various ways, eg. using a JOIN and VALUES syntax:

          ( ...
          ) T  
          ( values ('2017','10'), ('2017','11'), 
                   ('2018','01'), ('2018','02'), ('2018','03') 
          ) as V ("ANNO", "MESE")
          V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
    where  "Invio" IN ( '3', '1', '2' ) 
  • by the way, the subquery (aliased T) is not really needed. It ony adds complexity for those trying to read it:

        SUM(T."Importo_Scarto") as "Importo_Scarto",
        SUM(T."Q_Scarto") as "Q_Scarto"
          dbo.[MyTable] as T  
          ( values ('2017','10'), ('2017','11'), 
                   ('2018','01'), ('2018','02'), ('2018','03') 
          ) as V ("ANNO", "MESE")
          V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
        T."Invio" in ( '3', '1', '2' ) 
    group by
        T."ATC_1" ;

Not the answer you're looking for? Browse other questions tagged or ask your own question.