none
Erro no SQL Server (Execução da Consulta no ERP) RRS feed

  • Pergunta

  • Pessoal Bom dia,

    Preciso de uma ideia para o caso, tenho uma consulta que roda no SQL Server (2104), quando transfiro o código para rodar dentro do meu ERP (SAP B1 - Gerador de Consultas) me retorna o seguinte erro:

     1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

    no final do código tem um linha que realizo o período manual: and b.[Data da Venda] Between '2017-10-01' and '2017-10-31'

    no SAP tenho que trocar essa informação por uma variável e ficará assim: and b.[Data da Venda] Between [%0] and [%1]

    Segue abaixo o código completo;

    with CTE_RN as
    (
    select
    --a.DocDate,
    b.ItemCode as [Código do Item], /*Codigo do Item*/
    --b.Dscription,
    b.Price as [Vlr NF Entrada], /*Preço do NF Entrada*/
    --a.Serial,
     ROW_NUMBER() OVER(PARTITION BY b.ItemCode ORDER BY a.DocDate DESC) as RN
    from OPCH a
    inner join PCH1 b
     on b.DocEntry = a.DocEntry
    where a.CardName != 'BIODENTAL MEDICAL DIST. LTDA'
    --and b.ItemCode Between '000001' and '060000'
    ), CTE_RN1 as
    (
    select
    c.DocDate as [Data da Venda],
    b.ItemCode as [Código do Item],
    b.Dscription as [Descrição],
    e.FirmName as [Fornecedor],
    b.Quantity as [Quantidade Vendida],
    a.AvgPrice as [Vlr do Item NF Entrada],
    b.Price as [Vlr do Item NF Saida (Venda)],
    b.LineTotal as [Vlr Total do Item],
    c.Serial as [Numero NF],
    f.NfmName as [Modelo NF],
    b.CFOPCode as [CFOP],
    (8.00) as 'Desp. ADM %',
    (a.[AvgPrice]*0.08) as 'Vlr Desp. ADM',
    (1.08) as 'Cont. Social %',
    (a.[AvgPrice]*0.0108) as 'Vlr Cont. Social',
    (4.08) as 'IRPL %',
    (a.[AvgPrice]*0.0408) as 'Vlr IRPL',
    (3.00) as 'Comissão %',
    (a.[AvgPrice]*0.03) as 'Vlr Comissão',
    (1.80) as 'Tx Cartão %',
    (a.[AvgPrice]*0.0180) as 'Vlr Tx Cartão',
    (5.00) as 'RT %',
    (a.[AvgPrice] * 5.00) as 'Vlr RT',
    (2.00) as 'Desconto MKT%',
    (a.[AvgPrice] * 0.02) as 'Vlr Desconto MKT',
    (3.00) as 'Antecipação %',
    (a.[AvgPrice] * 0.03) as 'Vlr Antecipação',
    ((a.[AvgPrice]*0.08) + (a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.0408) + (a.[AvgPrice]*0.03) + (a.[AvgPrice]*0.0108) +
    (a.[AvgPrice] * 0.05) + (a.[AvgPrice] * 0.02) + (a.[AvgPrice] * 0.03)) as 'Vlr Despesas Vendas',
    (a.[AvgPrice] - ((a.[AvgPrice]*0.08) + (a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.0408) + (a.[AvgPrice]*0.03) +
    (a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.05) + (a.[AvgPrice]*0.02) + (a.[AvgPrice]*0.06))) as 'Ponto de Equilibrio'
     
    from OITW a
    inner join INV1 b
     on b.ItemCode = a.ItemCode
    inner join OINV c
     on c.DocEntry = b.DocEntry
    inner join OITM d
     on d.ItemCode = a.ItemCode
    inner join OMRC e
     on e.FirmCode = d.FirmCode
    inner join ONFM f
     on f.AbsEntry = c.Model 

    where a.WhsCode = '01'
    --and c.DocDate Between '2017-10-01' and '2017-10-31'
    and c.CANCELED = 'N'
    and b.CFOPCode != '5405, 5117, 5152, 5910, 6403, 1152, 1914'
    and c.CardName != 'BIODENTAL MEDICAL DIST LTDA'
    and c.CardName != 'BIODENTAL MEDICAL DISTRIBUIDORA LTDA'
    and c.CardName != 'SERGIO ROBERTO MELO BRINGEL'
    and c.CardName != 'SEBASTIAO RAMILO BULCÃO BRINGEL'
    and c.CardName != 'CD IRANDUBA SRB COMERCIO E SERVIÇOS LTDA'
    and c.CardName != 'CD Iranduba - SRB COMERCIO E SERVICOS DE MATERIAIS DE CONSTRUCAO LTDA'
    and c.CardName != 'SRB COMERCIO CONSTRUCAO'
    --order by c.DocDate asc
    )
    select *
    from CTE_RN a
    full join CTE_RN1 b
     on b.[Código do Item] = a.[Código do Item]
    where b.[Data da Venda] != ''
    and RN = 1
    --and b.[Código do Item] = '016017'
    and b.[Data da Venda] Between '2017-10-01' and '2017-10-31'
    order by b.[Data da Venda], b.[Código do Item] asc

     

    terça-feira, 28 de novembro de 2017 12:40

Todas as Respostas