Does anyone know what could give this error. The DBA says he can run this without an error, although I'm still getting an Oracle error. (WF 5.2.3)

  

SELECT TO_NUMBER(auth_amt) auth_amt,
 card_nbr account_no,
 reply_cd auth_resp_cd,
 auth_cd  approval_cd,
 TRUNC(auth_timestamp) auth_date,
 TO_CHAR(auth_timestamp,'HH24:MI:SS') auth_time,
 cvv2_result,
 visa_product_id,
 mc_bank_net_ref_nbr
 FROM TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03, NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08, :09, :10, NULL, :11, :12, :13, :14));
 END
 SQL SQLORA BIND DEF USING VARCHAR(20), VARCHAR(20), VARCHAR(50), VARCHAR(20), VARCHAR(20), DECIMAL(20), VARCHAR(1), VARCHAR(30), VARCHAR(10), DECIMAL(13,2), VARCHAR(20), DECIMAL(10), VARCHAR(10), VARCHAR(10)
 END
 SQL SQLORA EXECUTE DEF USING '08-NOV-2010', '08-NOV-2010', 'SSAVILLE', '0004707527', '08-NOV-2010', 1112868609470340011, 'E', '0007340000000004707527', '00552B', 1200.00, 'CRAUDCRD_Q1', 50000, 'Y', 'C' ;
END
 DEFINE FILE DEF
 END
 TABLE FILE DEF
 PRINT
 *
 ON TABLE HOLD AS AUTHDTL FORMAT ALPHA
 END
 -RUN
 (FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
 : ORA-01722: invalid number
 : ORA-06512: at "IMC.DA_IMC_TRAN", line 3293
 : ORA-06512: at line 1

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

quote:

1112868609470340011

Could be this number.
I think it is a bit more than what WebFOCUS can handle. It might result in an overflow situation, thereby causing this error.
Check if that's the case by activating traces and investigating them.

I've run it as a string and and integer with the same error. At one point this was working fine. And I don't have the ability to activate traces.

quote:

And I don't have the ability to activate traces

If you can code a fex, then you can activate traces. There are plenty of threads in this forum that can tell you how to do that. It can be (or is) very helpful when you need to analyze sql translation things.

I added this to the report.

SET TRACEOFF = ALL
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = STMTRACE/2/CLIENT
 SET TRACEUSER = ON

And view source is this.

SELECT TO_NUMBER(auth_amt) auth_amt,
 card_nbr account_no,
 reply_cd auth_resp_cd,
 auth_cd  approval_cd,
 TRUNC(auth_timestamp) auth_date,
 TO_CHAR(auth_timestamp,'HH24:MI:SS') auth_time,
 cvv2_result,
 visa_product_id,
 mc_bank_net_ref_nbr
 FROM TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03, NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08, :09, :10, NULL, :11, :12, :13, :14));
 END
 SQL SQLORA BIND DEF USING VARCHAR(20), VARCHAR(20), VARCHAR(50), VARCHAR(20), VARCHAR(20), DECIMAL(20), VARCHAR(1), VARCHAR(30), VARCHAR(10), DECIMAL(13,2), VARCHAR(20), DECIMAL(10), VARCHAR(10), VARCHAR(10)
 END
 SQL SQLORA EXECUTE DEF USING '08-NOV-2010', '08-NOV-2010', 'SSAVILLE', '0004707527', '08-NOV-2010', 1112868609470340048, 'E', '0007340000000004707527', '003877', 1092.00, 'CRAUDCRD_Q1', 50000, 'Y', 'C' ;
 END
 DEFINE FILE DEF
 END
 TABLE FILE DEF
 PRINT
 *
 ON TABLE HOLD AS AUTHDTL FORMAT ALPHA
 END
 -RUN
 17.46.14 AE   SELECT TO_NUMBER(auth_amt) auth_amt, card_nbr account_no,
 17.46.14 AE   reply_cd auth_resp_cd, auth_cd  approval_cd,
 17.46.14 AE   TRUNC(auth_timestamp) auth_date, TO_CHAR(auth_timestamp,
 17.46.14 AE   'HH24:MI:SS') auth_time, cvv2_result, visa_product_id,
 17.46.14 AE   mc_bank_net_ref_nbr FROM
 17.46.14 AE   TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03,
 17.46.14 AE   NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08,
 17.46.14 AE   :09, :10, NULL, :11, :12, :13, :14));
 (FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
 : ORA-01722: invalid number
 : ORA-06512: at "IMC.DA_IMC_TRAN", line 3293
 : ORA-06512: at line 1
 L    (FOC1406) SQL OPEN CURSOR ERROR.  : DEF

Only now I notice that it may very well be the END that screws things up. The very first END after the FROM line.
END usually is a very meaningfull token in the webfocus / sql language, I'm suspecting it won't be any different in this case.
It may be best to open a case with IB tech support to ask if the construction you have is supported at all. I suspect it isn't. If that is the case, then maybe they can also tell you if, and if so how, what you are trying to do this can be done using webfocus.

Thank you I will open a case, I don't think it could be the construction because we have about 30 other reports with the same construction that work.