这是来自 SAP Business One - Hana 数据库,但概念与 SQL 相同。我有一个查询,列出了系统中的所有发票和付款。SELECT T2.\'TotalExpns\' AS \'
这是来自 SAP Business One - Hana 数据库,但概念对于 SQL 是相同的。
我有一个查询,列出了系统中所有的发票和付款。
SELECT T2."TotalExpns" AS "Total Freight",T2."NumAtCard", T6."LineTotal",T7."SlpName",
'' AS "CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type
13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode",
T1."ReconSum", '' AS "DocDate", '' AS "CheckNo" FROM OITR T0
INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum"
INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN INV1 T6 ON T2."DocEntry" = T6."DocEntry"
INNER JOIN OSLP T7 ON T2."SlpCode" = T7."SlpCode"
WHERE T0."ReconNum" ='52837' AND T0."Canceled" = 'N' AND T0."IsCard" = 'C' AND
T1."SrcObjTyp" = '13'
UNION ALL
SELECT 0 AS "Total Freight", '' AS "NumAtCard",0 AS "LineTotal", '' AS "SlpName",
T4."CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13
Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum",
T3."DocDate", T3."CounterRef" AS "CheckNo" FROM OITR T0 INNER JOIN ITR1 T1 ON
T0."ReconNum" = T1."ReconNum" INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN ORCT T3 ON T3."DocEntry" = T1."SrcObjAbs"
INNER JOIN OINV T4 ON T2."DocNum" = T4."DocNum"
WHERE T0."ReconNum" ='52837' AND T0."Canceled" = 'N' AND T0."IsCard" = 'C' AND
T1."SrcObjTyp" = '24'
ORDER BY T0."ReconNum"
结果在屏幕截图中,我需要的是类型 24 位于所有类型 13 记录的同一行。系统只将文件日期和支票号码放在第 24 行
任何帮助都将不胜感激。
如果你有 SQL 格式的,我可以将其转换为 Hana
我不确定我是否理解了该要求。您可以分享一个您想要获得的结果示例,而不是描述它吗?
使用 CTE/子查询而不是联合:
;WITH CHECKDATE
AS
(
SELECT 0 AS "Total Freight", '' AS "NumAtCard",0 AS "LineTotal", '' AS "SlpName",
T4."CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13
Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum",
T3."DocDate", T3."CounterRef" AS "CheckNo" FROM OITR T0 INNER JOIN ITR1 T1 ON
T0."ReconNum" = T1."ReconNum" INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN ORCT T3 ON T3."DocEntry" = T1."SrcObjAbs"
INNER JOIN OINV T4 ON T2."DocNum" = T4."DocNum"
WHERE T0."ReconNum" ='52837' AND T0."Canceled" = 'N' AND T0."IsCard" = 'C' AND
T1."SrcObjTyp" = '24'
ORDER BY T0."ReconNum"
)
SELECT T2."TotalExpns" AS "Total Freight",T2."NumAtCard", T6."LineTotal",T7."SlpName",
'' AS "CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type
13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode",
T1."ReconSum",
CHECKDATE."DocDate",
CHECKDATE."CheckNo"
FROM OITR T0
INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum"
INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN INV1 T6 ON T2."DocEntry" = T6."DocEntry"
INNER JOIN OSLP T7 ON T2."SlpCode" = T7."SlpCode"
LEFT JOIN CHECKDATE ON CHECKDATE."ReconNum" = T0."ReconNum"
WHERE T0."ReconNum" ='52837' AND T0."Canceled" = 'N' AND T0."IsCard" = 'C' AND
T1."SrcObjTyp" = '13'