SQL - Debugging dynamic PIVOT code with UNION ALL

Question

I am in need of help! The first part of my code is as follows:

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,'')

---

Up to now, there aren't any problems. Using the code above,

SELECT @cols  

gives the output:

[TL],[USD],[EUR]

---

The second part of the query is where the problem occurs. The code is structured as follows:

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)

---

The second part does not work. I can make it work by doing three things:

1- Making the pivot static (ie replacing '+ @cols +' with [TL],[USD],[EUR]

2- Removing Table 2, Table 3 or Table 4 from the UNION ALL combinations. Somehow, removing only one table makes the dynamic pivot work.

3- I can remove one of Table 2, Table 3, or Table 4 and add one of the remaining tables again as another UNION ALL addition. Strangely, this works as well. So the problem might be that there might be one aspect of the 4 tables together that interferes with the PIVOT statement.

But I would like to include all the tables from 1 to 4 and I would like to have a dynamic pivot that adjusts itself for changes!

Please note that if I run the SELECT statement without the PIVOT construct with all the UNION ALLs, the code works perfectly fine as well (As I have indicated below)

My original code is:

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)

---

and I get the following error when I execute the query:

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

I haven't been able to identify the problem so far. I don't think that there is a logical problem with my code as both parts of my code can run independently, but when I join them using a PIVOT operator, it fails. Could you please look through my code to see if I am making any mistakes in my reasoning or syntax?

Thanks!

---

UPDATE

I know for a fact that my UNION ALLs work. Below is the code that goes under the PIVOT construct in my original code:

(

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
)

The code works by itself, but when I put it into the PIVOT structure, it fails. Please help! Or can you tell me a method that I can use instead of PIVOT that will give me the same result?

---

UPDATE

I think there is something wrong with the length of my query as when I run print @query, I get the following (Thanks @KumarHarsh for directing me to the correct way!):

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 

There is something wrong because @query does not get all the data and I do not know how I can solve this issue. I would really appreciate if you could solve my problem!

Thanks!

Answer 1

How I will solve this problem,

i) before Comment execute(@query)

put print @query.

ii)Copy the whole rendered script in another window.Just some how make it work

iii)Incorporate those changes in dynamic query.

iv)Are you sure it is rendered exactly AS [TL],[USD],[EUR].dOES THESE columns exists in tables because you are using alias like col6 [VADE], ' + @cols + '