cancel
Showing results for 
Search instead for 
Did you mean: 

Query pedido de compra

former_member533398
Discoverer
0 Kudos

Estou fazendo uma consulta formatada e está com o seguinte erro no SQL.

Msg 156, Level 15, State 1, Line 37

Incorrect syntax near the keyword 'FROM'

Segue o Código

SELECT
A."CardCode" AS "FORNECEDOR",
F."CardName" AS "Nome",
C."DocNum" AS "SOLICITAÇÃO",
C."DocDate" AS "SOLICITAÇÃO - DATA",
C."ReqName" AS "SOLICITANTE",
A."DocNum" AS "PEDIDO",
A."DocDate" AS "PEDIDO - DATA",
B."LineNum" + '1' AS "PEDIDO - LINHA",
B."ItemCode" AS "PEDIDO - ITEM",
B."Dscription" AS "PEDIDO - DESCRIÇÃO",
B."Quantity" AS "PEDIDO - VALOR UNITARIO",
B."Currency" AS "PEDIDO - MOEDA",
B."Price" AS "PEDIDO - PREÇO UNITARIO",
B."DiscPrcnt" AS "PEDIDO - % DESCONTO",
D."DocNum" AS "NOTA FISCAL DE ENTRADA", CASE
WHEN B."TargetType" = '18' THEN 'NOTA FISCAL DE ENTRADA' | '-' | D."DocNum" | '- NFe Fornecedor: ' | D."DocNum"
WHEN B."TargetType" = '20' THEN 'RECEBIMENTO DE MERCADORIA' | ': ' | G."DocNum" | '- NFe FORNECEDOR: ' | G."Serial"

END AS "DOCUMENTO DESTINO", CASE
WHEN B."TargetType" = '18' THEN E."AcctCode" | '-' | J."AcctName"

END AS "CONTA CONTABIL", CASE
WHEN B."TargetType" = '18' THEN D."DocDate"
WHEN B."TargetType" = '20' THEN G."DocDate"

END AS "DATA RECEBIMENTO", --PEDIDO DE COMPRA FROM "OPOR" A
LEFT OUTER JOIN "POR1" B ON B."DocEntry" = A."DocEntry" --SOLICITAÇÃO DE COMPRA LEFT OUTER JOIN "OPRQ" C ON C."DocNum" = B."BaseDocNum" --NOTA FISCAL DE ENTRADA

LEFT OUTER JOIN "OPCH" D ON D."DocEntry" = B."TargetType"
LEFT OUTER JOIN "PCH1" E ON E."BaseEntry" = B."DocEntry" AND E."BaseLine" = B."LineNum" -- CADASTRO PARCEIRO DE NEGOCIOS LEFT OUTER JOIN "OCRD" F ON F."CardCode" = A."CardCode" --RECEBIMENTO DE MERCADORIA LEFT OUTER JOIN "OPDN" G ON G."DocEntry" = B."TargetType"
LEFT OUTER JOIN "PDN1" H ON H."DocEntry" = G."DocEntry" AND H."BaseLine" = B."BaseLine" --PLANO DE CONTAS LEFT OUTER JOIN "OACT" I ON I."AcctCode" = E."AcctCode"
LEFT OUTER JOIN "OACT" J ON J."AcctCode" = H."AcctCode"
WHERE A."CANCELED" = 'N'
AND A."DocDate" >= [%0]
AND A."DocDate" <= [%1] ORDER BY A."DocNum", B."LineNum"

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member419030
Active Participant
0 Kudos

Dear Samuel,

END AS "DATA RECEBIMENTO", --PEDIDO DE COMPRA FROM "OPOR" A
LEFT OUTER JOIN "POR1" B ON B."DocEntry" = A."DocEntry" --SOLICITAÇÃO DE COMPRA LEFT OUTER JOIN "OPRQ" C ON C."DocNum" = B."BaseDocNum" --NOTA FISCAL DE ENTRADA

1) try to remove the comma

2) FROM "OPOR" A, try to move to the next line. (Currently it is read as comment)

kvbalakumar
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT
A."CardCode" AS "FORNECEDOR",
F."CardName" AS "Nome",
C."DocNum" AS "SOLICITAÇÃO",
C."DocDate" AS "SOLICITAÇÃO - DATA",
C."ReqName" AS "SOLICITANTE",
A."DocNum" AS "PEDIDO",
A."DocDate" AS "PEDIDO - DATA",
B."LineNum" + '1' AS "PEDIDO - LINHA",
B."ItemCode" AS "PEDIDO - ITEM",
B."Dscription" AS "PEDIDO - DESCRIÇÃO",
B."Quantity" AS "PEDIDO - VALOR UNITARIO",
B."Currency" AS "PEDIDO - MOEDA",
B."Price" AS "PEDIDO - PREÇO UNITARIO",
B."DiscPrcnt" AS "PEDIDO - % DESCONTO",
D."DocNum" AS "NOTA FISCAL DE ENTRADA", CASE
WHEN B."TargetType" = '18' THEN 'NOTA FISCAL DE ENTRADA' || '-' || D."DocNum" || '- NFe Fornecedor: ' || D."DocNum"
WHEN B."TargetType" = '20' THEN 'RECEBIMENTO DE MERCADORIA' || ': ' || G."DocNum" || '- NFe FORNECEDOR: ' || G."Serial"
END AS "DOCUMENTO DESTINO", CASE 
WHEN B."TargetType" = '18' THEN E."AcctCode" || '-' || J."AcctName"
END AS "CONTA CONTABIL", CASE 
WHEN B."TargetType" = '18' THEN D."DocDate"
WHEN B."TargetType" = '20' THEN G."DocDate"
END AS "DATA RECEBIMENTO" --PEDIDO DE COMPRA 
FROM "OPOR" A 
LEFT OUTER JOIN "POR1" B ON B."DocEntry" = A."DocEntry" --SOLICITAÇÃO DE COMPRA 
LEFT OUTER JOIN "OPRQ" C ON C."DocNum" = B."BaseDocNum" --NOTA FISCAL DE ENTRADA
LEFT OUTER JOIN "OPCH" D ON D."DocEntry" = B."TargetType" 
LEFT OUTER JOIN "PCH1" E ON E."BaseEntry" = B."DocEntry" AND E."BaseLine" = B."LineNum" -- CADASTRO PARCEIRO DE NEGOCIOS 
LEFT OUTER JOIN "OCRD" F ON F."CardCode" = A."CardCode" --RECEBIMENTO DE MERCADORIA 
LEFT OUTER JOIN "OPDN" G ON G."DocEntry" = B."TargetType"
LEFT OUTER JOIN "PDN1" H ON H."DocEntry" = G."DocEntry" AND H."BaseLine" = B."BaseLine" --PLANO DE CONTAS 
LEFT OUTER JOIN "OACT" I ON I."AcctCode" = E."AcctCode"
LEFT OUTER JOIN "OACT" J ON J."AcctCode" = H."AcctCode"
WHERE A."CANCELED" = 'N'
AND A."DocDate" >= [%0]
AND A."DocDate" <= [%1] ORDER BY A."DocNum", B."LineNum"

Regards,

Bala

former_member533398
Discoverer
0 Kudos

Hi,

The error that appeared from FROM does not happen anymore.
Now another error occurs.


Msg 207, Level 16, State 1, Line 37
Invalid column name '% 0'.
Msg 207, Level 16, State 1, Line 38
Invalid column name '% 1'.

Can you help me ?

kvbalakumar
Active Contributor
0 Kudos

Hi,

This query should be executed from Query Generator.

This error usually occurs if you run the query in HANA studio.