Hi all,
I ran into this problem that look like a bug if someone can confirm
and hopefully provide a workaround.
I have a storedProcedure that use a refcursor + temp table to create a
report. The problem is that the NUMERIC field 13 I get in my
application is always NULL. When I run the stored procedure from
PGADMINIII I can see that field 13 has value 5.5400000000000000.
I don't know it that may help look into the debugger and the array
this_row[12] is NULL. So it seem that he has problem fetching the value
from postgresql...
I'm running 8.2.4 with jdbc postgresql-8.2-506.jdbc3.jar
Thanks for your help and for any clue!
Best regards
David
P.S.:I include several debug info:
OUTPUT PGADMINIII
-------------------------
icnum;icdesca;icdescf;iccoutda;icfprix;icprixu;bxmontu;cus;unite_sec;unite_pri;iqqstock;iqqcomm;cms;icstatut;iciknum;ikdesc_pri;ikdesc_sec;icimnum;imdesc_pri;imdesc_sec
"ZEB71150";"SURLIGNEUR ZAZZLE BRIGHTS (5)";"SURLIGNEUR ZAZZLE BRIGHTS
(5)";"5.80";"1.540000";"8.93";"5.801600";"5.8016000000000000000000";"Unité";"Unit";"3";"0";"5.5400000000000000";"0";"8";"Écriture
et Dessin";"Écriture et Dessin";"ZEB";"Zebra";"Zebra"
LOG TO CREATE THE DATA FROM PGADMINIII
------------------------------------------------------
-- Executing query:
CREATE TABLE TMP_IC (
ICNUM VARCHAR(20) primary key
)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"tmp_ic_pkey" for table "tmp_ic"
Query returned successfully with no result in 94 ms.
-- Executing query:
CREATE TABLE TMP_IQ (
IQICNUM VARCHAR(20) primary key,
IQQSTOCK INT,
IQQCOMM INT
)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"tmp_iq_pkey" for table "tmp_iq"
Query returned successfully with no result in 78 ms.
-- Executing query:
CREATE TABLE TMP_CMS (
ICNUM VARCHAR(20) primary key,
CMS NUMERIC
)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"tmp_cms_pkey" for table "tmp_cms"
Query returned successfully with no result in 125 ms.
-- Executing query:
INSERT INTO TMP_IC
SELECT DISTINCT ICNUM
FROM IC LEFT OUTER JOIN IK ON IC.ICIKNUM = IK.IKNUM
WHERE (1 = 1) AND ICNUM ~* '***=ZEB71150' AND (( 1 = 0) OR
(ICSTATUT = 0) )
Query returned successfully: 1 rows affected, 63 ms execution time.
-- Executing query:
INSERT INTO TMP_IQ
SELECT IQICNUM, SUM(IQQSTOCK), SUM(IQQCOMM)
FROM IQ INNER JOIN TMP_IC AS TIC ON IQ.IQICNUM = TIC.ICNUM
GROUP BY IQICNUM
Query returned successfully: 1 rows affected, 31 ms execution time.
-- Executing query:
INSERT INTO TMP_CMS
SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) *
COALESCE(IRCOUT, 0)) / SUM(COALESCE(ITQFFO, 0)) as CMS
FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM
INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM
WHERE ITQFFO > 0
ERROR: column "ir.iricnum" must appear in the GROUP BY clause or be
used in an aggregate function
-- Executing query:
INSERT INTO TMP_CMS
SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) *
COALESCE(IRCOUT, 0)) / SUM(COALESCE(ITQFFO, 0)) as CMS
FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM
INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM
WHERE ITQFFO > 0
AND IRSENS = 'E'
AND IRSTATUT in (2,3)
GROUP BY IRICNUM
Query returned successfully: 1 rows affected, 406 ms execution time.
-- Executing query:
select * from tmp_cms
Total query runtime: 0 ms.
Data retrieval runtime: 0 ms.
1 rows retrieved.
-- Executing query:
SELECT IC.ICNUM,
IC.ICDESCA, IC.ICDESCF,
IC.ICCOUTDA, IC.ICFPRIX,
ROUND(COALESCE(udf_InvItem_PrixUnitairePourFournisseur_Obtenir('M',
IC.ICNUM, BX.BXRRNUM, '3', '2', '0' , '', 1, FALSE, CURRENT_DATE), 0),
2) AS ICPRIXU ,
udf_InvItem_CoutAchatPourFournisseur_Obtenir('M',
IC.ICNUM, BX.BXRRNUM, '4', '2', '0' , '', 1, FALSE) AS BXMONTU,
COALESCE(udf_InvItem_CoutUnitairePourFournisseur_Obtenir('M' , IC.ICNUM,
BX.BXRRNUM, '4' , '2', '0' , '' , 1, FALSE), 0) AS CUS,
AUDCTIFR AS UNITE_SEC, AUDCTIAN AS UNITE_PRI,
IQQSTOCK, IQQCOMM,
CMS, ICSTATUT, ICIKNUM, IKDESC_PRI, IKDESC_SEC,
ICIMNUM, IMDESC_PRI, IMDESC_SEC
FROM TMP_IC AS TIC INNER JOIN IC ON TIC.ICNUM =
IC.ICNUM
LEFT OUTER JOIN BX ON IC.ICNUM =
BX.BXICNUM AND BXTYPE = 1 AND BXREFER = true
LEFT OUTER JOIN RR ON BX.BXRRNUM =
RR.RRNUM
LEFT OUTER JOIN AD ON RR.RRADNUM =
AD.ADNUM
LEFT OUTER JOIN AU ON IC.ICAUNUM =
AU.AUNUM
LEFT OUTER JOIN TMP_IQ AS TIQ ON
TIC.ICNUM = TIQ.IQICNUM
LEFT OUTER JOIN TMP_CMS AS TCMS ON
TIC.ICNUM = TCMS.ICNUM
LEFT OUTER JOIN IK ON IC.ICIKNUM =
IK.IKNUM
LEFT OUTER JOIN IM ON IC.ICIMNUM =
IM.IMNUM ORDER BY IC.ICNUM
Total query runtime: 62 ms.
Data retrieval runtime: 47 ms.
1 rows retrieved.
THE STORED PROCEDURE
--------------------------------
CREATE OR REPLACE FUNCTION usp_Inventaire_Catalogue(VARCHAR, BOOLEAN,
BOOLEAN, BOOLEAN, BOOLEAN, INTEGER, VARCHAR, VARCHAR, BOOLEAN, BOOLEAN,
BOOLEAN, VARCHAR, INTEGER, BOOLEAN) RETURNS refcursor AS '
DECLARE
itemId ALIAS FOR $1;
statusActive ALIAS FOR $2;
statusInactive ALIAS FOR $3;
statusDiscontinued ALIAS FOR $4;
statusVirtual ALIAS FOR $5;
orderBy ALIAS FOR $6;
itemCategoryId ALIAS FOR $7;
manufacturerId ALIAS FOR $8;
displayQtyOrderedToSupplier ALIAS FOR $9;
displayPrice ALIAS FOR $10;
displayCost ALIAS FOR $11;
companyId ALIAS FOR $12;
priority ALIAS FOR $13;
statusSpecified ALIAS FOR $14;
ref refcursor;
statement varchar(4000);
FacteurCoutAjoute float;
DecimalesAchat INTEGER;
DecimalesVente INTEGER;
DecimalesProd INTEGER;
PARAM_FOURNPROD VARCHAR(10);
temp RECORD;
BEGIN
-- Create temp table
-- Table des produits à inclure
EXECUTE ''
CREATE TEMP TABLE TMP_IC (
ICNUM VARCHAR(20) primary key
) ON COMMIT DROP'';
-- Table des stock
EXECUTE ''
CREATE TEMP TABLE TMP_IQ (
IQICNUM VARCHAR(20) primary key,
IQQSTOCK INT,
IQQCOMM INT
) ON COMMIT DROP'';
-- Table des coûts moyens en stock
EXECUTE ''
CREATE TEMP TABLE TMP_CMS (
ICNUM VARCHAR(20) primary key,
CMS NUMERIC
) ON COMMIT DROP'';
select udf_Parametre_get(''Cie'', ''Item.FacteurCoutAjoute'',
companyId) as v into temp;
FacteurCoutAjoute := to_number(temp.v, ''999.9999'');
select udf_Parametre_get(''Cie'', ''General.DecimalesAchat'',
companyId) as v into temp;
DecimalesAchat := to_number(temp.v, ''9'');
select udf_Parametre_get(''Cie'', ''General.DecimalesVente'',
companyId) as v into temp;
DecimalesVente := to_number(temp.v, ''9'');
select udf_Parametre_get(''Cie'', ''General.DecimalesProduction'',
companyId) as v into temp;
DecimalesProd := to_number(temp.v, ''9'');
select COALESCE(udf_Parametre_get(''License'',
''Production.Fournisseur'', '''')) as v into temp;
PARAM_FOURNPROD := temp.v;
-- Tous les produits qui correspondent aux critères
statement := ''
INSERT INTO TMP_IC
SELECT DISTINCT ICNUM
FROM IC LEFT OUTER JOIN IK ON IC.ICIKNUM = IK.IKNUM
WHERE (1 = 1) '';
IF ( itemId IS NOT NULL AND itemId <> '''' )
THEN
statement := statement || '' AND ICNUM ~* '' ||
quote_literal(''***='' || itemId) ;
END IF;
IF ( statusSpecified = true )
THEN
statement := statement || '' AND (( 1 = 0) '';
IF ( statusActive = true )
THEN
statement := statement || ''OR (ICSTATUT = 0) '';
END IF;
IF ( statusInactive = true )
THEN
statement := statement || ''OR (ICSTATUT = 1) '';
END IF;
IF ( statusDiscontinued = true )
THEN
statement := statement || ''OR (ICSTATUT = 2) '';
END IF;
IF ( statusVirtual = true )
THEN
statement := statement || ''OR (ICSTATUT = 3) '';
END IF;
statement := statement || '' ) '';
END IF;
IF ( itemCategoryId IS NOT NULL AND itemCategoryId <> '''' )
THEN
statement := statement || '' AND ICIKNUM = '' ||
quote_literal(itemCategoryId) ;
END IF;
IF ( manufacturerId IS NOT NULL AND manufacturerId <> '''' )
THEN
statement := statement || '' AND ICIMNUM = '' ||
quote_literal(manufacturerId) ;
END IF;
IF ( priority IS NOT NULL )
THEN
statement := statement || '' AND IKPRIORITE <= '' || priority ;
END IF;
RAISE NOTICE ''Statement here is %'', statement;
EXECUTE statement;
-- Les stock pour ces produits
statement := ''
INSERT INTO TMP_IQ
SELECT IQICNUM, SUM(IQQSTOCK), SUM(IQQCOMM)
FROM IQ INNER JOIN TMP_IC AS TIC ON IQ.IQICNUM = TIC.ICNUM
GROUP BY IQICNUM '';
RAISE NOTICE ''Statement here is %'', statement;
EXECUTE statement;
-- Les coûts moyens en inventaire pour ces produits
statement := ''
INSERT INTO TMP_CMS
SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) * COALESCE(IRCOUT,
0)) / SUM(COALESCE(ITQFFO, 0)) as CMS
FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM
INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM
WHERE ITQFFO > 0
AND IRSENS = '' || quote_literal(''E'') || ''
AND IRSTATUT in (2,3)
GROUP BY IRICNUM'';
RAISE NOTICE ''Statement here is %'', statement;
--EXECUTE statement;
-- Informations à retourner
statement := ''
SELECT IC.ICNUM,
IC.ICDESCA, IC.ICDESCF,
IC.ICCOUTDA, IC.ICFPRIX,
ROUND(COALESCE(udf_InvItem_PrixUnitairePourFournisseur_Obtenir('' ||
quote_literal(companyId) || '', IC.ICNUM, BX.BXRRNUM, '' ||
quote_literal(DecimalesVente) || '', '' || quote_literal(DecimalesProd)
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' ||
quote_literal(PARAM_FOURNPROD) || '', 1, FALSE, CURRENT_DATE), 0), 2) AS
ICPRIXU ,
udf_InvItem_CoutAchatPourFournisseur_Obtenir('' ||
quote_literal(companyId) || '', IC.ICNUM, BX.BXRRNUM, '' ||
quote_literal(DecimalesAchat) || '', '' || quote_literal(DecimalesProd)
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' ||
quote_literal(PARAM_FOURNPROD) || '', 1, FALSE) AS BXMONTU,
COALESCE(udf_InvItem_CoutUnitairePourFournisseur_Obtenir('' ||
quote_literal(companyId) || '' , IC.ICNUM, BX.BXRRNUM, '' ||
quote_literal(DecimalesAchat) || '' , '' || quote_literal(DecimalesProd)
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' ||
quote_literal(PARAM_FOURNPROD) || '' , 1, FALSE), 0) AS CUS,
AUDCTIFR AS UNITE_SEC, AUDCTIAN AS UNITE_PRI,
IQQSTOCK, IQQCOMM,
CMS, ICSTATUT, ICIKNUM, IKDESC_PRI, IKDESC_SEC,
ICIMNUM, IMDESC_PRI, IMDESC_SEC
FROM TMP_IC AS TIC INNER JOIN IC ON TIC.ICNUM = IC.ICNUM
LEFT OUTER JOIN BX ON IC.ICNUM =
BX.BXICNUM AND BXTYPE = 1 AND BXREFER = true
LEFT OUTER JOIN RR ON BX.BXRRNUM =
RR.RRNUM
LEFT OUTER JOIN AD ON RR.RRADNUM =
AD.ADNUM
LEFT OUTER JOIN AU ON IC.ICAUNUM =
AU.AUNUM
LEFT OUTER JOIN TMP_IQ AS TIQ ON
TIC.ICNUM = TIQ.IQICNUM
LEFT OUTER JOIN TMP_CMS AS TCMS ON
TIC.ICNUM = TCMS.ICNUM
LEFT OUTER JOIN IK ON IC.ICIKNUM =
IK.IKNUM
LEFT OUTER JOIN IM ON IC.ICIMNUM =
IM.IMNUM'';
-- By id
IF ( orderBy = 0 )
THEN
statement := statement || '' ORDER BY IC.ICNUM'';
ELSIF ( orderBy = 1 )
THEN
statement := statement || '' ORDER BY IC.ICSTATUT'';
ELSIF ( orderBy = 2 )
THEN
statement := statement || '' ORDER BY IC.ICIKNUM'';
ELSIF ( orderBy = 3 )
THEN
statement := statement || '' ORDER BY IC.ICIMNUM'';
END IF;
RAISE NOTICE ''Statement here is %'', statement;
OPEN ref FOR EXECUTE statement;
RETURN ref;
END;
' LANGUAGE 'plpgsql';