0

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 
      [ATC_1]
      ,[MESE]
      ,[ANNO]
      ,[Q_Scarto]
      ,[Importo_Scarto]
  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
2

The condition:

where ( ("ANNO","MESE") IN ('2017','10'),('2017','11'),
                           ('2018','01'),('2018','02'),('2018','03')
   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:

    from
          ( ...
          ) T  
        join
          ( values ('2017','10'), ('2017','11'), 
                   ('2018','01'), ('2018','02'), ('2018','03') 
          ) as V ("ANNO", "MESE")
        on
          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:

    select 
        T."ATC_1",
        SUM(T."Importo_Scarto") as "Importo_Scarto",
        SUM(T."Q_Scarto") as "Q_Scarto"
    from 
          dbo.[MyTable] as T  
        join 
          ( values ('2017','10'), ('2017','11'), 
                   ('2018','01'), ('2018','02'), ('2018','03') 
          ) as V ("ANNO", "MESE")
        on
          V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
    where 
        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.