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

3 Me gusta
3

HOLA @Jesus_Gomez , muchas gracias por tu apoyo me sirvió mucho para otras consultas que estoy generando.
y agradecido con la consulta que tienes solo una duda por que utilizas … antes de un from

ya que me muestra el siguiente error

 Syntax error or access violation;257 sql syntax error: incorrect syntax near "...": line 2 col 37  '' (ECM2) (at pos 51)
4

Hola @sergio9 , en ‘…’ tendrías que poner los campos que necesitas, por ejemplo nuestra consulta es esta.

SELECT * 
FROM (
    SELECT 
        T0."DocNum", 
        T0."DocDate", 
        T0."ObjType", 
        T0."CardCode", 
        T0."CardName", 
        T0."DocTotal", 
        (T0."DocTotal" - T0."VatSum") AS "Total Base Imponible", 
        T0."PeyMethod", 
        T2."PymntGroup",  
        T0."LicTradNum"
    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' 
      AND (T0."CardCode" = '[%2]' OR '[%2]' = '') 
      AND (T0."DocDate" >= [%0] OR '[%0]' = '') 
      AND (T0."DocDate" <= [%1] OR '[%1]' = '')
    
    UNION ALL

    SELECT 
        T0."DocNum", 
        T0."DocDate",  
        T0."ObjType", 
        T0."CardCode", 
        T0."CardName",  
        0 - T0."DocTotal", 
        0 - (T0."DocTotal" - T0."VatSum") AS "Total Base Imponible", 
        T0."PeyMethod", 
        T2."PymntGroup",  
        T0."LicTradNum"
    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'   
      AND (T0."CardCode" = '[%2]' OR '[%2]' = '') 
      AND (T0."DocDate" >= [%0] OR '[%0]' = '') 
      AND (T0."DocDate" <= [%1] OR '[%1]' = '')

    UNION ALL

    SELECT 
        T0."DocNum", 
        T0."DocDate",  
        T0."ObjType", 
        T0."CardCode", 
        T0."CardName",  
        0 - T0."DocTotal", 
        0 - (T0."DocTotal" - T0."VatSum") AS "Total Base Imponible", 
        T0."PeyMethod", 
        T2."PymntGroup",  
        T0."LicTradNum"
    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'  
      AND (T0."CardCode" = '[%2]' OR '[%2]' = '')  
      AND (T0."DocDate" >= [%0] OR '[%0]' = '') 
      AND (T0."DocDate" <= [%1] OR '[%1]' = '')

) 
ORDER BY "DocDate";

Nota:OR '[%X]' = ''**: Permite que el filtro se ignore si el parámetro correspondiente ([%0], [%1] o [%2]) está vacío.

Un saludo