Question
error in result of storeproc
I have a problem with cfstoredproc and Informix database,
when I invoke a stored procedure by cfstoredproc, it returns me
several columns with the same name (EXPRESSION) and repeat the
value of first column.
I am running CFMX 8.0.1 Enterprise, with Informix 9.40 and built-in driver, wich works great, but when i try to use CFMX 8.0.1 with JDBC 3.50 for IBM Informix cfstoredproc error occurs. I need this version of JDBC because I will upgrade to informix 11.50 and CFMX 8.0.1.
This is the stored procedure...
CREATE PROCEDURE admgcn.stpbacklog (unidad1 char(1)) returning char(10), char(12), char(12), char(30), char(7), char(1), char(40), char(30), char(8), int, varchar(255,0), varchar(255,0), varchar(255,0), char(1), char(40), char(12), char(8);
define r_fecemision char(10);
define r_numaviso like r3avisos.numaviso;
define r_numorden like r3avisos.numorden;
define r_ubicacion like r3avisos.ubicacion;
define r_inop like r3otinopcd.noinop;
define r_prior like r3avisos.prior;
define r_descaviso like r3avisos.descaviso;
define r_resp like r3avisosop.responsable;
define r_numsem like r3avisosop.numsem;
define r_porc like r3activ.porcentaje;
define r_coment like r3avisosop.comentop;
define r_solpeds like r3avisosop.solpeds;
define r_comentab like r3avisosop.comentab;
define r_repercusion like r3avisos.repercusion;
define r_status like r3avisos.status;
define r_pn like r3avisos.numaviso;
define r_ptotrbres like r3avisos.ptotrbres;
define valor int;
let valor = 1;
if (weekday(today) = 1) then let valor = 3;
end if;
begin
set isolation to dirty read;
foreach
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, ao.inop, a.prior prioridad, a.descaviso descripcion, ao.responsable, ao.numsem, 0 porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, a.status,trim((select a.numaviso from r3avisos avs where unaviso =
unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, ptotrbres
INTO
r_fecemision, r_numaviso, r_numorden, r_ubicacion, r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment, r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres
FROM
r3avisos a, outer r3avisosop ao
WHERE
a.status[1,4] <> ('MECE') and
a.prior in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
a.claseaviso = 'AN' and
ao.numaviso = a.numaviso and
a.unaviso = unidad1 and
a.numorden is null {and
(DATE(TO_DATE(creado,'%d/%m/%Y')) >= today - valor and
DATE(TO_DATE(creado,'%d/%m/%Y')) <= today)}
UNION
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem, r3a.porcentaje porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
FROM
r3avisos a, outer r3avisosop ao, r3orden r3o, r3activ r3a
WHERE
r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
r3o.prioridad in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
r3o.claseorden = 'LV01' and
r3o.claseact = 'N00' and
a.unaviso = unidad1 and
ao.numaviso = a.numaviso and
a.numorden = r3o.numorden and
r3a.numorden = a.numorden and
r3a.porcentaje < 100 and
r3a.numope is not null
UNION
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem,0 porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
FROM
r3avisos a, outer r3avisosop ao, r3orden r3o
WHERE
r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
r3o.prioridad in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
r3o.claseorden = 'LV01' and
r3o.claseact = 'N00' and
a.unaviso = unidad1 and
ao.numaviso = a.numaviso and
a.numorden = r3o.numorden and
r3o.numorden not in (select numorden from r3activ where numorden is not null and numorden <> 'N/A')
ORDER BY
ptotrbres, prioridad, numaviso
return
r_fecemision, r_numaviso, r_numorden, r_ubicacion,
r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment,
r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres with resume;
end foreach;
end;
end procedure
as you can see I return several columns with different names
This is the way how I invoke the stored
<cfstoredproc datasource="prueba" procedure="stpbacklog">
<cfprocparam cfsqltype="CF_SQL_CHAR" value="1">
<cfprocresult name="RS">
</cfstoredproc>
<cfdump var="#RS#"/>
This is the result
query
(EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION)
1 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
2 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009
3 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
4 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
5 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
6 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008
7 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008
8 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008
9 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008
10 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008
You see how repeat first column? This is the problem I have.
I hope you can support me.
In advance,
Thanks a lot.
I am running CFMX 8.0.1 Enterprise, with Informix 9.40 and built-in driver, wich works great, but when i try to use CFMX 8.0.1 with JDBC 3.50 for IBM Informix cfstoredproc error occurs. I need this version of JDBC because I will upgrade to informix 11.50 and CFMX 8.0.1.
This is the stored procedure...
CREATE PROCEDURE admgcn.stpbacklog (unidad1 char(1)) returning char(10), char(12), char(12), char(30), char(7), char(1), char(40), char(30), char(8), int, varchar(255,0), varchar(255,0), varchar(255,0), char(1), char(40), char(12), char(8);
define r_fecemision char(10);
define r_numaviso like r3avisos.numaviso;
define r_numorden like r3avisos.numorden;
define r_ubicacion like r3avisos.ubicacion;
define r_inop like r3otinopcd.noinop;
define r_prior like r3avisos.prior;
define r_descaviso like r3avisos.descaviso;
define r_resp like r3avisosop.responsable;
define r_numsem like r3avisosop.numsem;
define r_porc like r3activ.porcentaje;
define r_coment like r3avisosop.comentop;
define r_solpeds like r3avisosop.solpeds;
define r_comentab like r3avisosop.comentab;
define r_repercusion like r3avisos.repercusion;
define r_status like r3avisos.status;
define r_pn like r3avisos.numaviso;
define r_ptotrbres like r3avisos.ptotrbres;
define valor int;
let valor = 1;
if (weekday(today) = 1) then let valor = 3;
end if;
begin
set isolation to dirty read;
foreach
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, ao.inop, a.prior prioridad, a.descaviso descripcion, ao.responsable, ao.numsem, 0 porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, a.status,trim((select a.numaviso from r3avisos avs where unaviso =
unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, ptotrbres
INTO
r_fecemision, r_numaviso, r_numorden, r_ubicacion, r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment, r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres
FROM
r3avisos a, outer r3avisosop ao
WHERE
a.status[1,4] <> ('MECE') and
a.prior in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
a.claseaviso = 'AN' and
ao.numaviso = a.numaviso and
a.unaviso = unidad1 and
a.numorden is null {and
(DATE(TO_DATE(creado,'%d/%m/%Y')) >= today - valor and
DATE(TO_DATE(creado,'%d/%m/%Y')) <= today)}
UNION
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem, r3a.porcentaje porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
FROM
r3avisos a, outer r3avisosop ao, r3orden r3o, r3activ r3a
WHERE
r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
r3o.prioridad in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
r3o.claseorden = 'LV01' and
r3o.claseact = 'N00' and
a.unaviso = unidad1 and
ao.numaviso = a.numaviso and
a.numorden = r3o.numorden and
r3a.numorden = a.numorden and
r3a.porcentaje < 100 and
r3a.numope is not null
UNION
SELECT
a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem,0 porcentaje, nvl(trim(ao.comentop),'') comentop,
nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
FROM
r3avisos a, outer r3avisosop ao, r3orden r3o
WHERE
r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
r3o.prioridad in ('1', '2', '3', '4', '5') and
(a.repercusion <> 'A' or a.repercusion is null) and
r3o.claseorden = 'LV01' and
r3o.claseact = 'N00' and
a.unaviso = unidad1 and
ao.numaviso = a.numaviso and
a.numorden = r3o.numorden and
r3o.numorden not in (select numorden from r3activ where numorden is not null and numorden <> 'N/A')
ORDER BY
ptotrbres, prioridad, numaviso
return
r_fecemision, r_numaviso, r_numorden, r_ubicacion,
r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment,
r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres with resume;
end foreach;
end;
end procedure
as you can see I return several columns with different names
This is the way how I invoke the stored
<cfstoredproc datasource="prueba" procedure="stpbacklog">
<cfprocparam cfsqltype="CF_SQL_CHAR" value="1">
<cfprocresult name="RS">
</cfstoredproc>
<cfdump var="#RS#"/>
This is the result
query
(EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION)
1 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
2 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009
3 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
4 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
5 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
6 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008
7 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008
8 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008
9 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008
10 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008
You see how repeat first column? This is the problem I have.
I hope you can support me.
In advance,
Thanks a lot.
AdChoices