Searching for the solution, I realised that the problem is in the function
ISEMPINUNITDIVISION.
It seems that the select statement
SELECT trim(substr(C.C_TREE_PATH||'%',1,1000)) INTO :PATH
FROM PSF.UNIT_DIVISION C
WHERE
DATE(VALUE(DATE(C.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(C.D_START)<=DATE(TIMESTAMP)
AND C.K_UNIT_DIVISION=:UNITDIVISIONID
AND ROWNO<=1;
in it doesn't work properly.
The association of the new value into the variable PATH doesn't work!!!
At the same time if I give a default value to PATH like '/1/%' and comment
the select as follow, the function doesn't work however.
SET PATH = '/1/%'
The only way to make it works is to eliminate in the like condition in the
select following
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 B.C_TREE_PATH LIKE :PATH */
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
Unfortunatly this isn't what I expect for.
What's wrong with the assignment to a VARCHAR variable?
----- Original Message -----
From: "Matteo Gattoni" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 10:12 AM
Subject: Invalid Exponent
Hello,
I have an error 'Numeric value out of range;-3019 Invalid exponent.' while
using the query
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'))
The function is the following
CREATE FUNCTION ISEMPINAGGREGATION (
PARTYAGGREGATIONID FIXED(9,0),
ROLETYPEID FIXED(9,0),
EMPCOMPANYID FIXED(9,0),
EMPHUMANRESOURCEID FIXED(9,0),
EMPDSTARTCHR TIMESTAMP)
RETURNS NUMBER AS
VAR RES NUMBER; MSG VARCHAR(1000); L_EMPDSTARTCHR TIMESTAMP;
L_PARTYAGGREGATIONID FIXED(9,0);
L_ROLETYPEID FIXED(9,0);
L_EMPCOMPANYID FIXED(9,0);
L_EMPHUMANRESOURCEID FIXED(9,0);
BEGIN
IF PARTYAGGREGATIONID IS NULL OR EMPCOMPANYID IS NULL OR
EMPHUMANRESOURCEID IS NULL OR EMPDSTARTCHR IS NULL THEN
RETURN 0;
SET L_EMPDSTARTCHR = EMPDSTARTCHR;
SET L_PARTYAGGREGATIONID = PARTYAGGREGATIONID;
SET L_ROLETYPEID = ROLETYPEID;
SET L_EMPCOMPANYID = EMPCOMPANYID;
SET L_EMPHUMANRESOURCEID = EMPHUMANRESOURCEID;
TRY
SELECT 1 INTO :RES
FROM
PSF.PARTY B
INNER JOIN
PSF.RESOURCE_AGGREGATION A
ON B.K_PARTY=A.E_PTY_K_PARTY_AGG
INNER JOIN
(select
ISEMPINUNITDIVISION(
A2.E_U_DIV_K_UNIT_DIVISION,
NULL,
:L_EMPCOMPANYID,
:L_EMPHUMANRESOURCEID,
:L_EMPDSTARTCHR,
'F') af1,
ISEMPINCOMPANY(
A2.E_COMPANY_K_COMPANY,
NULL,
:L_EMPCOMPANYID,
:L_EMPHUMANRESOURCEID,
:L_EMPDSTARTCHR) af2,
A2.K_RESOURCE_AGGREGATION
,A2.E_PTY_K_PARTY_AGG
,A2.E_ROLE_TYP_K_ROLE_TYPE
,A2.E_DB_TYP_K_DATABASE_TYPE
,A2.E_OPERATOR_K_OPERATOR
From
PSF.RESOURCE_AGGREGATION A2
WHERE
DATE(VALUE(DATE(A2.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(A2.D_START)<=DATE(TIMESTAMP)
) A1
ON A.K_RESOURCE_AGGREGATION = A1.K_RESOURCE_AGGREGATION AND
A.E_PTY_K_PARTY_AGG = A1.E_PTY_K_PARTY_AGG AND
A.E_ROLE_TYP_K_ROLE_TYPE = A1.E_ROLE_TYP_K_ROLE_TYPE
AND
A.E_DB_TYP_K_DATABASE_TYPE =
A1.E_DB_TYP_K_DATABASE_TYPE AND
A.E_OPERATOR_K_OPERATOR = A1.E_OPERATOR_K_OPERATOR AND
((A.E_U_DIV_K_UNIT_DIVISION IS NOT NULL AND 1= A1.af1) OR
(A.E_COMPANY_K_COMPANY IS NOT NULL AND 1= A1.af2) OR
(A.E_COMP_HR_K_COMPANY=:L_EMPCOMPANYID
AND A.E_COMP_HR_K_HUMAN_RESOURCE=:L_EMPHUMANRESOURCEID
AND DATE(A.E_COMP_HR_D_START)=DATE(:L_EMPDSTARTCHR)))
WHERE
DATE(VALUE(ADDDATE(B.D_END,0),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(B.D_START)<=DATE(TIMESTAMP)
AND B.E_PTY_TYP_K_PARTY_TYPE=2
AND
DATE(VALUE(ADDDATE(A.D_END,0),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(A.D_START)<=DATE(TIMESTAMP)
AND
A.E_ROLE_TYP_K_ROLE_TYPE=VALUE(:L_ROLETYPEID,A.E_ROLE_TYP_K_ROLE_TYPE)
AND A.E_PTY_K_PARTY_AGG=:L_PARTYAGGREGATIONID
AND ROWNO<2;
RETURN 1;
CATCH
IF $RC <> 100 THEN
STOP($RC,$ERRMSG)
ELSE RETURN 0;
END;
The function called are the following
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; PATH VARCHAR(1000); 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(ADDDATE(B.D_END,0),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(ADDDATE(A.D_END,0),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<2
ELSE
BEGIN
SELECT substr(trim(''||C.C_TREE_PATH||'%'),1,1000) INTO
:PATH
FROM PSF.UNIT_DIVISION C
WHERE
DATE(VALUE(ADDDATE(C.D_END,0),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(C.D_START)<=DATE(TIMESTAMP)
AND C.K_UNIT_DIVISION=:UNITDIVISIONID
AND ROWNO<2;
SELECT 1 INTO :RES
FROM PSF.COMPANY_HR_ROLE A
,PSF.UNIT_DIVISION B
WHERE
DATE(VALUE(ADDDATE(B.D_END,0),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
AND DATE(B.D_START)<=DATE(TIMESTAMP)
AND B.C_TREE_PATH LIKE :PATH
AND
DATE(VALUE(ADDDATE(A.D_END,0),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<2;
END;
RETURN 1;
CATCH
IF $RC <> 100 THEN
STOP($RC,$ERRMSG)
ELSE RETURN 0;
END;
//
CREATE FUNCTION ISEMPINCOMPANY (
COMPANYID FIXED(9,0),
ROLETYPEID FIXED(9,0),
EMPCOMPANYID FIXED(9,0),
EMPHUMANRESOURCEID FIXED(9,0),
EMPDSTARTCHR TIMESTAMP)
RETURNS NUMBER AS
VAR RES INT; MSG VARCHAR(1000); L_EMPDSTARTCHR TIMESTAMP;
BEGIN
IF COMPANYID IS NULL OR EMPCOMPANYID IS NULL OR EMPHUMANRESOURCEID IS
NULL OR
EMPDSTARTCHR IS NULL OR COMPANYID!=EMPCOMPANYID THEN
RETURN 0;
SET L_EMPDSTARTCHR = EMPDSTARTCHR;
TRY
SELECT 1 INTO :RES
FROM PSF.COMPANY_HR_ROLE A
WHERE
DATE(VALUE(ADDDATE(A.D_END,0),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<2;
RETURN 1;
CATCH
IF $RC <> 100 THEN
STOP($RC,$ERRMSG)
ELSE RETURN 0;
END;
//
I can't understand what's worng with it.
The error seems telling me that some number value is out of range, but I
can't find wich one.
Thanks,
Matteo
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
