Syntax error in with SQL Server

Asked

Viewed 481 times

1

Tenh the following error in sql server:

Incorrect syntax near the keyword 'with'. If this statement is a common table Expression, an xmlnamespaces clause or a change tracking context clause, the Previous statement must be terminated with a semicolon

What can it be?

Follows the code:

  declare @data date='2018-05-21'

  With SomaDebCred AS (
  SELECT ContabLancNumCtrl, 
   sum(case when ContabLancCtaCred is not null then ContabLancValor else 0 
  end) as soma_debito,
   sum(case when ContabLancCtaDeb is not null then ContabLancValor else 0 
  end) as soma_credito
  from CONTAB_LANCAMENTO
  where ContabLancData = @data
    and EmpCod='01.02'
  group by ContabLancNumCtrl

 ),
 NomeClientes AS (
 SELECT A.ContabLancNumCtrl, A.soma_debito, A.soma_credito,
   L.ContabLancValor, L.ContabLancHistComp,
   L.ContabLancCtaCred, L.ContabLancCtaDeb
 from SomaDebCred as A
   inner join CONTAB_LANCAMENTO as L on L.ContabLancNumCtrl = 
   A.ContabLancNumCtrl
   where A.soma_debito <> A.soma_credito

   )

  SELECT * FROM SomaDebCred
   UNION ALL
   SELECT * FROM NomeClientes
  • "Incorrect syntax near "com" keyword. If this statement is a common table expression, xmlnamespaces clause, or change control context clause, the previous statement must be completed with a semicolon"

  • 1

    puts a point and comma of declaring @data date='2018-05-21'

1 answer

2


When using with, you need to use semicolon after the previous command.

declare @data date='2018-05-21';
  • Now there’s another error I don’t know about: All queries Combined using a UNION, INTERSECT or EXCEPT Operator must have an Equal number of Expressions in their target lists.

  • 1

    When vc uses UNION, querys must return the same number of columns.

  • It’s already worked out here, you don’t need it anymore!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.