This is the code I used to use, it worked fine. Now, throwing only this following error:ERROR: CLI open cursor error: Unable to retrieve error message.
When I just did a select * from the first table it worked fine. I am thinking the data type issue ? not sure how to fix....
PROC SQL;
Connect to odbc (dsn="SQLserver123" uid=&sqluser. password=&sqlpwd. readbuff=32767 );
CREATE table dfrd AS SELECT * FROM connection to odbc
(
SELECT CAST(a.Claim_Num AS CHAR(11)) AS Claim_ID,
d.Line_DT AS Claim_Dt,
d.Debit_Card_Num AS Card_Num,
c.Code_Desc AS FraudType,
Loss_Desc,
e.ClaimMinTran AS MinTran,
e.ClaimMaxTran AS MaxTran,
SUM(d.Claim_Amt) AS Grs_Fraud_Amt,
SUM(d.Claim_Amt - (Credit_Amt - Credit_Rev_Amt)) AS Net_Fraud_Amt
FROM DATUSER.Fraud a
LEFT JOIN DATUSER.Code_Table c ON a.Fraud_Type_CD = c.Code_Value
INNER JOIN DATUSER.Dispute_Claim d ON a.Claim_Num = d.Claim_Num
INNER JOIN (
SELECT Claim_Num,
MIN(Tran_DT_TM) AS ClaimMinTran,
MAX(Tran_DT_TM) AS ClaimMaxTran
FROM DATUSER.Dispute_Transaction a
GROUP BY Claim_Num) e
ON a.Claim_Num = e.Claim_Num
WHERE (c.Code_Type IS NULL OR c.Code_Type = 62)
AND d.Merge_IND <> 'M'
AND d.Category IN (3,4,7,9)
AND d.Line_DT >= &DTB.
GROUP BY CAST(a.Claim_Num AS CHAR(11)),
d.Line_DT,
d.Debit_Card_Num,
c.Code_Desc,
Loss_Desc,
e.ClaimMinTran,
e.ClaimMaxTran);
DISCONNECT FROM odbc;
QUIT;
... View more
