Focal Point
(CLOSED) Error in SQL Editor

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4737047926

March 25, 2013, 03:19 PM
SPetrey
(CLOSED) Error in SQL Editor
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,
March 25, 2013, 03:52 PM
globalwm
Do you have any CASE statements by chance? Have you also checked for strange hidden characters?


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
March 25, 2013, 06:15 PM
Tony A
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
March 26, 2013, 02:27 AM
FrankDutch
Maybe start with posting your query and then the error message.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

March 26, 2013, 09:19 AM
Alex
That error code could indicate that you need to use a "." to separate the library and file name.


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
March 26, 2013, 10:16 AM
SPetrey
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');


WebFOCUS 7.6
Windows, All Outputs
March 26, 2013, 10:22 AM
SPetrey
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,


WebFOCUS 7.6
Windows, All Outputs