Error en Subconsulta SBO

SAP Business One
1

Hola, espero que todos se encuentren muy bien, quisiera hacer una pregunta sobre el generador de consultas de SAP, es posible realizar subconsultas dentro del generador ? ya que tengo una subconsulta que me está arrojando error, y no me logra especificar el por qué, la consulta es la siguiente:

SELECT 
T0."CardCode" AS "CodigoCliente",
T3."CardName" AS "Cliente",
T4."U_GVS_Subzona" AS "Subzona",
T5."Name" AS "Vendedor Asignado",
T6."Name" AS "Municipio",
T6."U_NomDepartamento" AS "Departamento",
'Factura' AS "Tipo Documento",
T0."DocNum" AS "Número Factura",
T0."DocDate" AS "Fecha Factura",
DAYOFMONTH(T0."DocDate") AS "Día Factura",
MONTH(T0."DocDate") AS "Mes Factura",
YEAR(T0."DocDate") AS "Año Factura",
T7."Code" AS "Semana Factura",
T0."DocDueDate" AS "Vencimiento Factura",
DAYOFMONTH(T0."DocDueDate") AS "Día Vencimiento",
MONTH(T0."DocDueDate") AS "Mes Vencimiento",
YEAR(T0."DocDueDate") AS "Año Vencimiento",
T10."Code" AS "Semana Factura",
T8."Name" AS "Perfil",
T4."U_GVS_Frecuencia" AS "Frecuencia",
T4."U_GVS_Ruta" AS "Ruta",
T9."PymntGroup" AS "Plazo",
T3."CreditLine" AS "Cupo",
T0."DocTotal" AS "Total Factura",
null AS "NUM_RECONCILIACION",
T0."PaidToDate" AS "Valor Pago",
null AS "Fecha Reconciliación",
**(SELECT MAX(TT1."ReconDate") FROM ITR1 TT0 **
**INNER JOIN OITR TT1 ON TT1."ReconNum" = TT0."ReconNum"**
**WHERE TT0."TransId" = T0."TransId" AND TT0."IsCredit" = 'D' AND TT0."Account" = '13050501')** AS "Fecha Ultimo Pago"
FROM OINV T0
INNER JOIN OCRD T3 ON T3."CardCode" = T0."CardCode"
LEFT JOIN CRD1 T4 ON T4."CardCode" = T0."CardCode" AND T4."Address" = T0."ShipToCode" AND T4."AdresType" = 'S'
LEFT JOIN "@GVS_EMPLEADOS" T5 ON T5."Code" = T4."U_GVS_EmpVta"
LEFT JOIN "@HBT_MUNICIPIO" T6 ON T6."Code" = T4."U_HBT_MunMed"
LEFT JOIN "@GVS_SEMANAS" T7 ON T0."DocDate" BETWEEN T7."U_GVS_Desde" AND T7."U_GVS_Hasta"
LEFT JOIN "@GVS_PERFIL" T8 ON T8."Code"=T3."U_GVS_PerfClie" 
INNER JOIN OCTG T9 ON T9."GroupNum" = T3."GroupNum" 
LEFT JOIN "@GVS_SEMANAS" T10 ON T0."DocDueDate" BETWEEN T10."U_GVS_Desde" AND T10."U_GVS_Hasta"
WHERE T0."DocDate" BETWEEN '20210101' AND '20210131'
AND T0."Series" IN (144,4)

Lo que aparece con ** es la subconsulta.

Si pruebo este query en HANA directamente, sí me funciona, pero al pasarla al query manager da error, si quito la subconsulta, se ejecuta correctamente.

Agradezco si a alguien le pasa lo mismo y lo haya podido solucionar, me pueda dar su opinión.

2

la he añadido al generador de consultas y el error es este:
1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]‘DAYOFMONTH’ is not a recognized built-in function name.
2). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ‘*’.

3

Hola @meqs, que pena contigo, es que hay que quitarle los ‘*’ es que tiene los * para que fuera fácil identificar la subconsulta, como tal debería correr así.

SELECT 
T0."CardCode" AS "CodigoCliente",
T3."CardName" AS "Cliente",
T4."U_GVS_Subzona" AS "Subzona",
T5."Name" AS "Vendedor Asignado",
T6."Name" AS "Municipio",
T6."U_NomDepartamento" AS "Departamento",
'Factura' AS "Tipo Documento",
T0."DocNum" AS "Número Factura",
T0."DocDate" AS "Fecha Factura",
DAYOFMONTH(T0."DocDate") AS "Día Factura",
MONTH(T0."DocDate") AS "Mes Factura",
YEAR(T0."DocDate") AS "Año Factura",
T7."Code" AS "Semana Factura",
T0."DocDueDate" AS "Vencimiento Factura",
DAYOFMONTH(T0."DocDueDate") AS "Día Vencimiento",
MONTH(T0."DocDueDate") AS "Mes Vencimiento",
YEAR(T0."DocDueDate") AS "Año Vencimiento",
T10."Code" AS "Semana Factura",
T8."Name" AS "Perfil",
T4."U_GVS_Frecuencia" AS "Frecuencia",
T4."U_GVS_Ruta" AS "Ruta",
T9."PymntGroup" AS "Plazo",
T3."CreditLine" AS "Cupo",
T0."DocTotal" AS "Total Factura",
null AS "NUM_RECONCILIACION",
T0."PaidToDate" AS "Valor Pago",
null AS "Fecha Reconciliación",
(SELECT MAX(TT1."ReconDate") FROM ITR1 TT0 
INNER JOIN OITR TT1 ON TT1."ReconNum" = TT0."ReconNum"
WHERE TT0."TransId" = T0."TransId" AND TT0."IsCredit" = 'D' AND TT0."Account" = '13050501') AS "Fecha Ultimo Pago"
FROM OINV T0
INNER JOIN OCRD T3 ON T3."CardCode" = T0."CardCode"
LEFT JOIN CRD1 T4 ON T4."CardCode" = T0."CardCode" AND T4."Address" = T0."ShipToCode" AND T4."AdresType" = 'S'
LEFT JOIN "@GVS_EMPLEADOS" T5 ON T5."Code" = T4."U_GVS_EmpVta"
LEFT JOIN "@HBT_MUNICIPIO" T6 ON T6."Code" = T4."U_HBT_MunMed"
LEFT JOIN "@GVS_SEMANAS" T7 ON T0."DocDate" BETWEEN T7."U_GVS_Desde" AND T7."U_GVS_Hasta"
LEFT JOIN "@GVS_PERFIL" T8 ON T8."Code"=T3."U_GVS_PerfClie" 
INNER JOIN OCTG T9 ON T9."GroupNum" = T3."GroupNum" 
LEFT JOIN "@GVS_SEMANAS" T10 ON T0."DocDueDate" BETWEEN T10."U_GVS_Desde" AND T10."U_GVS_Hasta"
WHERE T0."DocDate" BETWEEN '20210101' AND '20210131'
AND T0."Series" IN (144,4)

Gracias

4

Hola Leonardo70338, soy un poco nuevo en esto, pero quise intentar ver si podía ayudar en algo, en lo personal, me estaba marcando el error en el comando DAYOFMONTH, lo que hice fue cambiarlo por el comando DAY, ya con ese cambio me corrio perfectamente tu código, espero haber podido ayudar aunque sea un poco, saludos!!!
DAYOFMONTH(T0.“DocDate”) AS “Día Factura”, → DAY(T0.“DocDate”) AS “Día Factura”,

5

Perdón, me di cuenta que es al reemplazar las fechas que pongo aquí WHERE T0."DocDate" BETWEEN '20210101' AND '20210131' por los parámetros [%0] and [%1]

7

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.