小贝子编程

SQL - 使用 UNION ALL 调试动态 PIVOT 代码



我需要帮助！我的代码的第一部分如下：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols= STUFF((SELECT ','+QUOTENAME(c.CURCODE) FROM (
(
SELECT DISTINCT b.CURCODE,a.TRCURR FROM LG_011_BNCREDITCARD a INNER JOIN L_CURRENCYLIST b 
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
)
UNION
(
SELECT DISTINCT b.CURCODE,a.TRCURR
FROM LG_011_LEASINGPAYMENTSLNS a
INNER JOIN LG_011_PURCHOFFER z
ON a.LEASINGREF=z.LEASINGREF
INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF
FROM LG_011_PURCHOFFER
GROUP BY LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF
INNER JOIN L_CURRENCYLIST b
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
WHERE z.STATUS=4
)
UNION
(
SELECT DISTINCT cur.CURCODE,cs.TRCURR FROM 
LG_011_01_CSTRANS a
INNER JOIN
(
SELECT 
CSREF,
MAX(STATNO) [STATNO]
FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
)
UNION
(
SELECT DISTINCT cur.CURCODE, pt.TRCURR
FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU')
)
) c ORDER BY c.TRCURR FOR XML PATH(''), TYPE 
).value('.','NVARCHAR(MAX)'),1,1,'')

到目前为止，没有任何问题。使用上面的代码，

SELECT @cols

给出输出：

[TL],[USD],[EUR]

查询的第二部分是出现问题的位置。代码结构如下：

SET @query ='
SELECT
col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
' + @cols + '
FROM
(
Table 1
UNION ALL
Table 2
UNION ALL
Table 3
UNION ALL
Table 4
) x
PIVOT
(
SUM(AMOUNT)
FOR CURCODE IN ('+ @cols+ ')
) xx
ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
'
execute(@query)

第二部分不起作用。我可以通过做三件事来使其工作：

1-使枢轴静态（即用[TL],[USD],[EUR]替换'+ @cols +'

2-从UNION ALL组合中删除表2，表3或表4。不知何故，仅删除一个表会使动态透视工作。

3

-我可以删除表2，表3或表4之一，然后再次添加剩余表之一作为另一个UNION ALL添加。奇怪的是，这也有效。因此，问题可能是 4 个表中的一个方面可能干扰了 PIVOT 语句。

但是我想包括从 1 到 4 的所有表格，我希望有一个动态枢轴来调整自己以适应变化！

请注意，如果我运行 SELECT 语句而不使用所有 UNION ALL 的 PIVOT 构造，代码也可以正常工作（如下所述）

我的原始代码是：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols= STUFF((SELECT ','+QUOTENAME(c.CURCODE) FROM (
(
SELECT DISTINCT b.CURCODE,a.TRCURR FROM LG_011_BNCREDITCARD a INNER JOIN L_CURRENCYLIST b 
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
)
UNION
(
SELECT DISTINCT b.CURCODE,a.TRCURR
FROM LG_011_LEASINGPAYMENTSLNS a
INNER JOIN LG_011_PURCHOFFER z
ON a.LEASINGREF=z.LEASINGREF
INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF
FROM LG_011_PURCHOFFER
GROUP BY LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF
INNER JOIN L_CURRENCYLIST b
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
WHERE z.STATUS=4
)
UNION
(
SELECT DISTINCT cur.CURCODE,cs.TRCURR FROM 
LG_011_01_CSTRANS a
INNER JOIN
(
SELECT 
CSREF,
MAX(STATNO) [STATNO]
FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
)
UNION
(
SELECT DISTINCT cur.CURCODE, pt.TRCURR
FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU')
)
) c ORDER BY c.TRCURR FOR XML PATH(''), TYPE 
).value('.','NVARCHAR(MAX)'),1,1,'')

SET @query ='
SELECT
col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
'+ @cols +'
FROM
(
(
SELECT
''LEASİNG'' [col1],
 d.REGNR [col2],
 cl.DEFINITION_ [col3],
 '''' [col4],
 d.DESCRIPTION [col5],
 c.PAYMENTDATE [col6],
 a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
 c.TRCURR [TRCURR],
 e.CURCODE [CURCODE]

FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a
LEFT OUTER JOIN
(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF
INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF

INNER JOIN
LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF
INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF
INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF
INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)

WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0
)
UNION ALL
(
SELECT
''ÇEK'',
cs.NEWSERINO,
bn.DEFINITION_,
ban.DEFINITION_,
cl.DEFINITION_,
cs.DUEDATE,
cs.AMOUNT,
cs.TRCURR,
cur.CURCODE
FROM
LG_011_01_CSTRANS a
INNER JOIN
(
SELECT 
CSREF,
MAX(STATNO) [STATNO]
FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
CASE WHEN cl.SPECODE=''OTOMATİK'' THEN ''OTOMATİK ÖDEME'' WHEN cl.SPECODE=''ZORUNLU'' THEN ''ZORUNLU CARİ'' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'''',
'''',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE
FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE ''320%'' AND cl.SPECODE IN (''OTOMATİK'',''ZORUNLU'') AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
 CASE d.SPECODE WHEN '''' THEN ''KREDİ'' WHEN ''FORWARD'' THEN ''FORWARD'' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE
 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF
LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 1 
        GROUP BY PARENTREF) c 
ON b.LOGICALREF = c.PARENTREF
INNER JOIN LG_011_BNCREDITCARD d
ON b.CREDITREF=d.LOGICALREF
INNER JOIN L_CURRENCYLIST e
ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)
INNER JOIN LG_011_BNCARD f
ON d.BNCRREF=f.LOGICALREF
INNER JOIN LG_011_BANKACC g
ON d.BNACCREF=g.LOGICALREF
WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)<>0
)
) x
PIVOT
(
SUM(AMOUNT)
FOR CURCODE IN ('+ @cols +')
) xx
ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
'
execute(@query)

执行查询时出现以下错误：

Msg 102, Level 15, State 1, Line 205
Incorrect syntax near ','.

到目前为止，我还没有能够确定问题所在。我不认为我的代码存在逻辑问题，因为我的代码的两个部分都可以独立运行，但是当我使用 PIVOT 运算符连接它们时，它会失败。你能看看我的代码，看看我的推理或语法是否有任何错误吗？

谢谢！

更新

我知道我的 UNION ALL 有效。下面是我的原始代码中 PIVOT 构造下的代码：

(
SELECT
'ÇEK' [col1],
cs.NEWSERINO [col2],
bn.DEFINITION_ [col3],
ban.DEFINITION_ [col4],
cl.DEFINITION_ [col5],
cs.DUEDATE [col6],
cs.AMOUNT [AMOUNT],
cs.TRCURR [TRCURR],
cur.CURCODE [CURCODE]
FROM
LG_011_01_CSTRANS a
INNER JOIN
(
SELECT 
CSREF,
MAX(STATNO) [STATNO]
FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
'LEASİNG',
 d.REGNR,
 cl.DEFINITION_,
 '',
 d.DESCRIPTION,
 c.PAYMENTDATE,
 a.KDVLI- Isnull(b.KDVLI,0),
 c.TRCURR,
 e.CURCODE

FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a
LEFT OUTER JOIN
(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF
INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF

INNER JOIN
LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF
INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF
INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF
INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)

WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0
)
UNION ALL
(
SELECT
CASE WHEN cl.SPECODE='OTOMATİK' THEN 'OTOMATİK ÖDEME' WHEN cl.SPECODE='ZORUNLU' THEN 'ZORUNLU CARİ' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'',
'',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE
FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU') AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
 CASE d.SPECODE WHEN '' THEN 'KREDİ' WHEN 'FORWARD' THEN 'FORWARD' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE
 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF
LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 1 
        GROUP BY PARENTREF) c 
ON b.LOGICALREF = c.PARENTREF
INNER JOIN LG_011_BNCREDITCARD d
ON b.CREDITREF=d.LOGICALREF
INNER JOIN L_CURRENCYLIST e
ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)
INNER JOIN LG_011_BNCARD f
ON d.BNCRREF=f.LOGICALREF
INNER JOIN LG_011_BANKACC g
ON d.BNACCREF=g.LOGICALREF
WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)<>0
)

代码可以自行工作，但是当我将其放入 PIVOT 结构中时，它失败了。请帮忙！或者你能告诉我一种我可以使用的方法而不是 PIVOT 给我相同的结果吗？

更新

我认为我的查询长度有问题，因为当我运行print @query时，我得到以下内容（感谢@KumarHarsh将我引导到正确的方式！

SELECT
col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
[TL],[USD],[EUR]
FROM
(
(
SELECT
'LEASİNG' [col1],
 d.REGNR [col2],
 cl.DEFINITION_ [col3],
 '' [col4],
 d.DESCRIPTION [col5],
 c.PAYMENTDATE [col6],
 a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
 c.TRCURR [TRCURR],
 e.CURCODE [CURCODE]

FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a
LEFT OUTER JOIN
(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF
INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF

INNER JOIN
LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF
INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF
INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF
INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)

WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0
)
UNION ALL
(
SELECT
'ÇEK',
cs.NEWSERINO,
bn.DEFINITION_,
ban.DEFINITION_,
cl.DEFINITION_,
cs.DUEDATE,
cs.AMOUNT,
cs.TRCURR,
cur.CURCODE
FROM
LG_011_01_CSTRANS a
INNER JOIN
(
SELECT 
CSREF,
MAX(STATNO) [STATNO]
FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
CASE WHEN cl.SPECODE='OTOMATİK' THEN 'OTOMATİK ÖDEME' WHEN cl.SPECODE='ZORUNLU' THEN 'ZORUNLU CARİ' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'',
'',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE
FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU') AND cur.FIRMNR=11
)
UNION ALL
(
SELECT
 CASE d.SPECODE WHEN '' THEN 'KREDİ' WHEN 'FORWARD' THEN 'FORWARD' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE
 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF
LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR

有问题，因为@query没有得到所有数据，我不知道如何解决这个问题。如果您能解决我的问题，我将不胜感激！

谢谢！

我将

如何解决这个问题，

i） 在注释执行之前（@query）

把打印@query。

二）在另一个窗口中复制整个呈现的脚本。只是一些如何让它工作

三）将这些更改合并到动态查询中。

四）你确定它完全呈现为 [tl]，[美元]，[欧元].dOES 这些列存在于表中，因为您使用的别名像 col6 [VADE]，' + @cols + '

允许使用 "（[sql1]） UNION （[sql2]

）" 而不是 "[sql1] UNION [sql2]"。 这可能有助于隔离问题。 没有这个，我很难确保 （） 在我的编辑器中匹配。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium