Consulta de Facturas por Cliente

SAP Business One
1

Hola buenas Tardes Amigos,
quiero solicitar su apoyo para poder saber que cual es error que me muestra en el siguiente query que estoy creando.

Syntax error or access violation;257 sql syntax error: incorrect syntax near “T0”: line 13 col 15 ‘’ (ECM2) (at pos 384)

SELECT
YEAR(T0.“DocDate”) as “Año”
,Month(T0.“DocDate”) as “Mes”
,T0.“CardCode” as “#Cliente”
,T0.“CardName” as “Nombre”
,SUM(T0.“DocTotal”) as “Total Facturado”
FROM
OINV T0
WHERE
T0.“CANCELED” = ‘N’
AND T0.“DocDate” >=[%0] AND T0.“DocDate” <=[%1]
T0.“CardCode”= [%2]
GROUP BY
YEAR(T0.“DocDate”)
,MONTH(T0.“DocDate”)
,T0.“CardCode”
,T0.“CardName”
ORDER BY
T0.“CardCode”

2

Hola,

Me parece que la solución es esta:

SELECT 
    YEAR(T0."DocDate") AS "Año",
    MONTH(T0."DocDate") AS "Mes",
    T0."CardCode" AS "#Cliente",
    T0."CardName" AS "Nombre",
    SUM(T0."DocTotal") AS "Total Facturado"
FROM 
    OINV T0
WHERE 
    T0."CANCELED" = 'N'
    AND T0."DocDate" >= [%0]
    AND T0."DocDate" <= [%1]
    AND T0."CardCode" = '[%2]'
GROUP BY 
    YEAR(T0."DocDate"),
    MONTH(T0."DocDate"),
    T0."CardCode",
    T0."CardName"
ORDER BY 
    T0."CardCode";

Había una falta de AND antes de la condición “T0.“CardCode” = ‘[%2]’” y unas comillas sencillas en ‘[%2]’.

Nosotros usamos una consulta similar, pero añadiendo los abonos en negativo, y las facturas de anticipo

SELECT * FROM
( SELECT T0."DocNum", T0."DocDate", ...  FROM OINV T0  INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" INNER JOIN OCTG T2 ON T0."GroupNum" = T2."GroupNum" WHERE T0."CANCELED" ='N'
UNION ALL
SELECT T0."DocNum", T0."DocDate", ...  FROM ORIN T0  INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" INNER JOIN OCTG T2 ON T0."GroupNum" = T2."GroupNum" WHERE T0."CANCELED" ='N'  
UNION ALL
SELECT T0."DocNum", T0."DocDate", ... 0 - T0."DocTotal", 0 - (T0."DocTotal" - T0."VatSum") AS "Total Base Imponible",
 ...FROM ODPI T0  INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" INNER JOIN OCTG T2 ON T0."GroupNum" = T2."GroupNum" WHERE T0."CANCELED" ='N' 
 ) 
ORDER BY "DocDate"

Un saludo

1 me gusta