i have a working sql statement works find in Iseries Navigator and in sequel viewpoint, but when I put it in the SQL editor I get the following error. What is WebFocus missing? Or what am I missing?

(FOC1400) SQLCODE IS -5016 (HEX: FFFFEC68)
: [42833] Qualified object name TRANSACTION_ITEM not valid.
L (FOC1405) SQL PREPARE ERROR.
0 ERROR AT OR NEAR LINE 151 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

This message has been edited. Last edited by: SPetrey,

Do you have any CASE statements by chance? Have you also checked for strange hidden characters?

It would appear that you may not have issued the prerequisite SQL SQLxxx statement to declare the flavour of SQL that your target requires.
"xxx" in the above is something like ORA or DB2 etc.

T

Maybe start with posting your query and then the error message.

That error code could indicate that you need to use a "." to separate the library and file name.

Here is the statement. It is quite long. I have it set to pull only one batch for testing purposes.

ENGINE DB2 SET DEFAULT_CONNECTION WCONAQHA
SQL DB2

SELECT A.*, (A.TOTALAMOUNTRECEIVED - a.USEDAMOUNT)*-1 As UnusedAmount FROM (SELECT TRANSACTION_ID, STATUS, PAYMENT_STATUS,
SUM(INVOICEPAID) AS INVOICEPAID,
SUM(TEMP.AMOUNTRECEIVED) AS TOTALAMOUNTRECEIVED,
SUM(TEMP.SERVICEAMOUNT) AS TOTALSERVICECOST,
SUM(TEMP.DISCOUNT) AS TOTALDISCOUNT,
SUM(TEMP.TAX) AS TOTALTAX,
SUM(TEMP.RUSHAMOUNT) AS TOTALRUSHAMOUNT,
SUM(TEMP.INVOICED) AS INVOICED,
SUM(TEMP.EXCESSHOLDAMOUNT) AS TOTALEXCESSHOLD,
SUM(TEMP.EXCESSUSEDAMOUNT) AS TOTALEXCESSUSED,
SUM(TEMP.REFUNDAMOUNT) AS TOTALREFUND ,
SUM(TEMP.UNUSEDAMOUNT) AS USEDAMOUNT,batch_id, batch_number,
SUM(TEMP.SERVICEAMOUNT + TEMP.RUSHAMOUNT + TEMP.TAX - TEMP.DISCOUNT + TEMP.EXCESSHOLDAMOUNT + TEMP.REFUNDAMOUNT - TEMP.AMOUNTRECEIVED - TEMP.EXCESSUSEDAMOUNT - TEMP.INVOICED + INVOICEPAID) AS AMOUNTDUE
FROM
(SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
SUM(TI.SERVICE_AMOUNT) AS SERVICEAMOUNT,
SUM(TI.DISCOUNT) AS DISCOUNT,
SUM(TI.TAX) AS TAX,
SUM(TI.RUSH_AMOUNT) AS RUSHAMOUNT,
0.00 AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/TRANSACTION_ITEM TI
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = TI.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND TI.IS_DELETED=0 GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
0 AS SERVICEAMOUNT,
0 AS DISCOUNT,
0 AS TAX,
0 AS RUSHAMOUNT,
0.00 AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT,
(SUM(TI.SERVICE_AMOUNT) + SUM(TI.TAX) + SUM(TI.RUSH_AMOUNT) - SUM(TI.DISCOUNT)) AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/TRANSACTION_ITEM TI
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = TI.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND TI.IS_DELETED=0 AND TI.STATUS = 100
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
SUM(CASE WHEN P.MODE = 70800000006 THEN (P.AMOUNT_RECEIVED) ELSE P.AMOUNT_RECEIVED END) AS AMOUNTRECEIVED,
0.00 AS SERVICEAMOUNT,
0.00 AS DISCOUNT,
0.00 AS TAX, 0.00 AS RUSHAMOUNT,
0.00 AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/PAYMENT P
LEFT OUTER JOIN AQHPRDDAT/OTHER_PAYMENT OP ON P.PAYMENT_ID = OP.PAYMENT_ID
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = P.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID
AND (OP.OTHER_PAY_TYPE IS NULL OR OP.OTHER_PAY_TYPE <> 71400000005 ) AND P.STATUS= 100 AND P.IS_DELETED=0
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
SUM(P.AMOUNT_RECEIVED) AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
0.00 AS SERVICEAMOUNT,
0.00 AS DISCOUNT,
0.00 AS TAX, 0.00 AS RUSHAMOUNT,
0.00 AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/PAYMENT P
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = P.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID
INNER JOIN AQHPRDDAT/INVOICE I ON P.INVOICE_ID = I.INVOICE_ID
AND P.STATUS= 100 AND P.IS_DELETED=0 AND I.IS_DELETED=0 AND I.INVOICE_STATUS NOT IN ( 70)
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
0.00 AS SERVICEAMOUNT,
0.00 AS DISCOUNT,
0.00 AS TAX,
0.00 AS RUSHAMOUNT,
0.00 AS INVOICED,
SUM(CASE WHEN EH.TYPE = 71500000002 THEN EH.AMOUNT ELSE 0.00 END) AS EXCESSHOLDAMOUNT,
SUM(CASE WHEN EH.TYPE = 71500000003 THEN EH.AMOUNT ELSE 0.00 END) AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/EXCESS_HOLD EH
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =EH.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID
AND EH.TYPE NOT IN (71500000004 , 71500000005 )
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
0.00 AS SERVICEAMOUNT,
0.00 AS DISCOUNT,
0.00 AS TAX,
0.00 AS RUSHAMOUNT,
SUM(I.INVOICE_AMOUNT) AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
0.00 AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/INVOICE I
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =I.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID
AND I.IS_DELETED=0 AND I.INVOICE_STATUS NOT IN ( 70)
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
UNION ALL
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS,
0.00 AS INVOICEPAID,
0.00 AS AMOUNTRECEIVED,
0.00 AS SERVICEAMOUNT,
0.00 AS DISCOUNT,
0.00 AS TAX,
0.00 AS RUSHAMOUNT,
0.00 AS INVOICED,
0.00 AS EXCESSHOLDAMOUNT,
0.00 AS EXCESSUSEDAMOUNT,
SUM(PA.PAYOUT_AMOUNT) AS REFUNDAMOUNT ,
0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number
FROM AQHPRDDAT/PAYOUT PA
INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =PA.TRANSACTION_ID
INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND (PA.PAYOUT_SOURCE= 71700000004 OR PA.PAYOUT_SOURCE= 71700000005)
AND PA.PAYOUT_STATUS <> 430 AND PA.PAYOUT_STATUS <> 110 AND PA.IS_DELETED=0
GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
) TEMP GROUP BY TRANSACTION_ID,STATUS,PAYMENT_STATUS, Batch_Id, Batch_Number) A
WHERE BATCH_NUMBER in ('013059408');

And i forogt to mention it now runs with no errors but produces no data. I had to change the "." to a "/" on the file names. Is this a connection problem maybe?

This message has been edited. Last edited by: SPetrey,