CREATE TABLE `UNPTEST` (
|
`REC_GROUP` char(2) DEFAULT NULL,
|
`CLIENT_INFO` text CHARACTER SET utf8,
|
`NAME` text,
|
`PHONE_NUMBER` text,
|
`ATTENTION_NAME` text,
|
`PAYMENT_TERM` text CHARACTER SET utf8,
|
`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
|
`LAST_PAY_DATE` text CHARACTER SET utf8,
|
`TOTAL` double DEFAULT NULL,
|
`TOTAL_MCL` double DEFAULT NULL,
|
`TOTAL_MFS` double DEFAULT NULL,
|
`TOTAL_MIS` double DEFAULT NULL,
|
`BEFORE_DUE_7_MCL` double DEFAULT NULL,
|
`BEFORE_DUE_7_MFS` double DEFAULT NULL,
|
`BEFORE_DUE_7_MIS` double DEFAULT NULL,
|
`PER1_MCL` double DEFAULT NULL,
|
`PER1_MFS` double DEFAULT NULL,
|
`PER1_MIS` double DEFAULT NULL,
|
`PER2_MCL` double DEFAULT NULL,
|
`PER2_MFS` double DEFAULT NULL,
|
`PER2_MIS` double DEFAULT NULL,
|
`PER3_MCL` double DEFAULT NULL,
|
`PER3_MFS` double DEFAULT NULL,
|
`PER3_MIS` double DEFAULT NULL,
|
`PER4_MCL` double DEFAULT NULL,
|
`PER4_MFS` double DEFAULT NULL,
|
`PER4_MIS` double DEFAULT NULL,
|
`PER5_MCL` double DEFAULT NULL,
|
`PER5_MFS` double DEFAULT NULL,
|
`PER5_MIS` double DEFAULT NULL,
|
`PER6_MCL` double DEFAULT NULL,
|
`PER6_MFS` double DEFAULT NULL,
|
`PER6_MIS` double DEFAULT NULL,
|
`PER7_MCL` double DEFAULT NULL,
|
`PER7_MFS` double DEFAULT NULL,
|
`PER7_MIS` double DEFAULT NULL,
|
`BEFORE_DUE_7` double DEFAULT NULL,
|
`PER1` double DEFAULT NULL,
|
`PER2` double DEFAULT NULL,
|
`PER3` double DEFAULT NULL,
|
`PER4` double DEFAULT NULL,
|
`PER5` double DEFAULT NULL,
|
`PER6` double DEFAULT NULL,
|
`PER7` double DEFAULT NULL,
|
`REF` varchar(30) DEFAULT NULL,
|
`TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
|
) DEFAULT CHARSET=latin1;
|
|
CREATE TABLE `UNPTEST1` (
|
`RECEIVABLE_GROUP` char(2) DEFAULT NULL,
|
`CLIENT_NUMBER` varchar(35) DEFAULT NULL,
|
`CLIENT_NAME` varchar(73) DEFAULT NULL,
|
`PHONE_NUMBER` char(12) DEFAULT NULL,
|
`ATTENTION_NAME` char(26) DEFAULT NULL,
|
`PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
|
`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
|
`LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL,
|
`TOTAL` decimal(12,2) DEFAULT NULL,
|
`BEFORE_DUE_7` decimal(12,2) DEFAULT NULL,
|
`PER1` decimal(12,2) DEFAULT NULL,
|
`PER2` decimal(12,2) DEFAULT NULL,
|
`PER3` decimal(12,2) DEFAULT NULL,
|
`PER4` decimal(12,2) DEFAULT NULL,
|
`PER5` decimal(12,2) DEFAULT NULL,
|
`PER6` decimal(12,2) DEFAULT NULL,
|
`PER7` decimal(12,2) DEFAULT NULL,
|
`DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL,
|
`CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL,
|
`EXCHANGE_RATE` double NOT NULL,
|
`REF` varchar(30) DEFAULT NULL
|
) DEFAULT CHARSET=latin1;
|
|
SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL,
|
COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL,
|
COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS,
|
COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS,
|
COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL,
|
COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS,
|
COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS,
|
COALESCE(I.PER1_MCL,0) AS PER1_MCL,
|
COALESCE(J.PER1_MFS,0) AS PER1_MFS,
|
COALESCE(K.PER1_MIS,0) AS PER1_MIS,
|
COALESCE(L.PER2_MCL,0) AS PER2_MCL,
|
COALESCE(M.PER2_MFS,0) AS PER2_MFS,
|
COALESCE(N.PER2_MIS,0) AS PER2_MIS,
|
COALESCE(O.PER3_MCL,0) AS PER3_MCL,
|
COALESCE(P.PER3_MFS,0) AS PER3_MFS,
|
COALESCE(R.PER3_MIS,0) AS PER3_MIS,
|
COALESCE(S.PER4_MCL,0) AS PER4_MCL,
|
COALESCE(T.PER4_MFS,0) AS PER4_MFS,
|
COALESCE(U.PER4_MIS,0) AS PER4_MIS,
|
COALESCE(V.PER5_MCL,0) AS PER5_MCL,
|
COALESCE(X.PER5_MFS,0) AS PER5_MFS,
|
COALESCE(Z.PER5_MIS,0) AS PER5_MIS,
|
COALESCE(Q.PER6_MCL,0) AS PER6_MCL,
|
COALESCE(Y.PER6_MFS,0) AS PER6_MFS,
|
COALESCE(W.PER6_MIS,0) AS PER6_MIS,
|
COALESCE(A1.PER7_MCL,0) AS PER7_MCL,
|
COALESCE(B1.PER7_MFS,0) AS PER7_MFS,
|
COALESCE(C1.PER7_MIS,0) AS PER7_MIS,
|
A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7,
|
CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,
|
GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO,
|
GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME,
|
GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER ,
|
GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' ) AS ATTENTION_NAME,
|
GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM,
|
CREDIT_LIMIT ,
|
GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE,
|
SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL,
|
SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7,
|
SUM( `PER1`*EXCHANGE_RATE ) AS PER1,
|
SUM( `PER2`*EXCHANGE_RATE ) AS PER2,
|
SUM( `PER3`*EXCHANGE_RATE ) AS PER3,
|
SUM( `PER4`*EXCHANGE_RATE ) AS PER4,
|
SUM( `PER5`*EXCHANGE_RATE ) AS PER5,
|
SUM( `PER6`*EXCHANGE_RATE ) AS PER6,
|
SUM( `PER7`*EXCHANGE_RATE ) AS PER7
|
FROM `UNPTEST1`
|
WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND
|
A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER
|
AND
|
A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND
|
A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND
|
A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND
|
A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND
|
A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER AND
|
A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND
|
A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND
|
A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND
|
A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND
|
A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND
|
A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND
|
A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND
|
A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND
|
A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND
|
A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND
|
A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND
|
A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND
|
A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND
|
A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND
|
A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND
|
A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND
|
A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND
|
A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND
|
A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND
|
A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT
|
LEFT JOIN
|
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS
|
FROM `UNPTEST1`
|
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND
|
A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT
|
ORDER BY TOTAL DESC;
|
|
DROP TABLE UNPTEST, UNPTEST1;
|
explain_bug.sql
