Hello Alexander,
This is the application server's configuration
<local-tx-datasource>
<jndi-name>maxdb</jndi-name>
<connection-url>jdbc:sapdb://XXX/XXX?sqlmode=ORACLE</connection-url>
<driver-class>com.sap.dbtech.jdbc.DriverSapDB</driver-class>
<user-name>XXX</user-name>
<password>XXX</password>
</local-tx-datasource>
We are using PreparedStatement in our Java code and the mode is ORACLE both in
Java and in Sql studio.
Thanks,
Matteo
----- Original Message -----
From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
To: "'Matteo Gattoni'" <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 3:48 PM
Subject: RE: Invalid Exponent (3)
Hello Matteo,
can you try using a 'PreparedStatement' in Java (even if there are no parameters)?
Are you using the same SQL Mode in SQL Studio as in Java ?
Regards
Alexander Schr�der
SAP DB, SAP Labs Berlin
> -----Original Message-----
> From: Matteo Gattoni [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 03, 2004 3:41 PM
> To: [EMAIL PROTECTED]
> Subject: Invalid Exponent (3)
>
>
> I've changed the function in the following way
>
> CREATE FUNCTION ISEMPINUNITDIVISION (
> UNITDIVISIONID FIXED(9,0),
> ROLETYPEID FIXED(9,0),
> EMPCOMPANYID FIXED(9,0),
> EMPHUMANRESOURCEID FIXED(9,0),
> EMPDSTARTCHR TIMESTAMP,
> F_UD_LTD CHAR(1))
> RETURNS NUMBER AS
> VAR RES INT; N_PATH VARCHAR(4000); MSG VARCHAR(1000);
> L_EMPDSTARTCHR TIMESTAMP;
> BEGIN
> IF UNITDIVISIONID IS NULL OR EMPCOMPANYID IS NULL OR
> EMPHUMANRESOURCEID IS NULL OR
> EMPDSTARTCHR IS NULL THEN
> RETURN 0;
>
> SET L_EMPDSTARTCHR = EMPDSTARTCHR;
>
> TRY
> IF F_UD_LTD IS NOT NULL AND F_UD_LTD='T' THEN
> SELECT 1 INTO :RES
> FROM
> PSF.UNIT_DIVISION B
> ,PSF.COMPANY_HR_ROLE A
> WHERE
>
> DATE(VALUE(DATE(B.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(B.D_START)<=DATE(TIMESTAMP)
> AND B.K_UNIT_DIVISION=A.E_U_DIV_K_UNIT_DIVISION
> AND
> DATE(VALUE(DATE(A.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(A.D_START)<=DATE(TIMESTAMP)
> AND
> A.E_ROLE_TYP_K_ROLE_TYPE=VALUE(:ROLETYPEID,A.E_ROLE_TYP_K_ROLE_TYPE)
> AND A.E_U_DIV_K_UNIT_DIVISION=:UNITDIVISIONID
> AND A.E_COMP_HR_K_COMPANY=:EMPCOMPANYID
> AND A.E_COMP_HR_K_HUMAN_RESOURCE=:EMPHUMANRESOURCEID
> AND DATE(A.E_COMP_HR_D_START)=DATE(:L_EMPDSTARTCHR)
> AND ROWNO<=1
> ELSE
> BEGIN
> SELECT 1 INTO :RES
> FROM PSF.COMPANY_HR_ROLE A
> INNER JOIN PSF.UNIT_DIVISION B
> ON A.E_U_DIV_K_UNIT_DIVISION =
> B.K_UNIT_DIVISION
> WHERE
>
> DATE(VALUE(DATE(B.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(B.D_START)<=DATE(TIMESTAMP)
> AND
> UNIT_DIVISION_ANCESTOR(B.K_UNIT_DIVISION) = :UNITDIVISIONID
> AND
> DATE(VALUE(DATE(A.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(A.D_START)<=DATE(TIMESTAMP)
> AND
> A.E_ROLE_TYP_K_ROLE_TYPE=VALUE(:ROLETYPEID,A.E_ROLE_TYP_K_ROLE_TYPE)
> AND A.E_COMP_HR_K_COMPANY=:EMPCOMPANYID
> AND
> A.E_COMP_HR_K_HUMAN_RESOURCE=:EMPHUMANRESOURCEID
> AND DATE(A.E_COMP_HR_D_START)=DATE(:L_EMPDSTARTCHR)
> AND ROWNO<=1;
> END;
> RETURN 1;
> CATCH
> IF $RC <> 100 THEN
> STOP($RC,$ERRMSG)
> ELSE RETURN 0;
> END;
>
> where
>
> CREATE FUNCTION UNIT_DIVISION(C NUMBER) RETURNS NUMBER AS
> VAR FATHER FIXED(9,0); CHILD FIXED(9,0);
> TRY
>
> SET CHILD = C;
>
> WHILE 1 = 1 DO
> BEGIN
> SELECT E_U_DIV_K_UNIT_DIVISION
> INTO :FATHER
> FROM PSF.UNIT_DIVISION
> WHERE
> K_UNIT_DIVISION = :CHILD
> AND D_END IS NULL;
>
> IF FATHER IS NULL THEN
> RETURN CHILD;
>
> SET CHILD = FATHER;
> END;
>
> RETURN FATHER;
>
> CATCH
> IF $RC <> 100 THEN STOP($RC, $ERRMSG)
> ELSE RETURN CHILD;
> //
>
> Recreating all the procedure at once, the first time a submit
> the statement
>
> SELECT
> 1
> FROM
> dual
> WHERE
> 1=isEmpInAggregation(to_number('200004'),
> NULL,to_number('1'),
> to_number('200002'),to_date('21-05-2004','dd/mm/yyyy'))
>
> I receive the error 'Numeric value out of range;-3019 Invalid
> exponent.'
> From that moment on, everything goes ok.....but using Java I
> receive the same error
> 'exception [-3019]: Invalid exponent'.
>
> Why a SQL statement goes with SQL Studio and doesn't with Java?
> Why the first time the statement goes wrong and then everything is ok?
> What's my mistake?
>
> If you need trace or everything else, just ask me.
>
> Thanks,
> Matteo
>
>
