Re: Stored Procedure does not show column names.
Thanks for your answer, but the problem is that my stored procedure returns
several expressions from Select statement using Return with RESUME. Here is
a code:
CREATE PROCEDURE PW_TimeSummary (matter char(12))
RETURNING Char(8), Char(42), Char(15), Date, Date, Float, Money,Char(48), char(48), Char(6), Char(5) ;
DEFINE ttk Char(8);
DEFINE tklast Char(42);
DEFINE tkloc Char(15);
DEFINE oldest, latest DATE;
DEFINE Hours FLOAT;
DEFINE Amount MONEY;
DEFINE mdesc1 CHAR(48);
DEFINE clname1 CHAR(48);
DEFINE client_id CHAR(6);
DEFINE matter_id CHAR(5);
FOREACH
SELECT TimeCard.ttk, TRIM(TimeKeep.tklast) || ', ' ||
TRIM(Timekeep.tkfirst) As tklast,
TRIM(TimeKeep.tkloc) || '/' || TRIM(TimeKeep.tkdept) As tkloc,
min(TimeCard.tworkdt) As oldest, max(TimeCard.tworkdt) As latest,
sum(tworkhrs) as Hours, sum(tbilldol) As Amount,
matter.mdesc1, client.clname1,mmatter[1,6] As Client_id, mmatter[8,12]
As Matter_id
INTO ttk, tklast,tkloc,oldest,latest,hours,amount, mdesc1, clname1,
client_id, matter_id
FROM TimeCard, TimeKeep, Matter, Client
WHERE tmatter=matter
AND tbilldt is null
AND TimeCard.ttk = TimeKeep.tkinit
AND matter.mclient = client.clnum
AND matter.mmatter=matter
GROUP BY TimeCard.ttk, TimeKeep.tklast, Timekeep.tkfirst,TimeKeep.tkloc ,
TimeKeep.tkdept,
matter.mdesc1, client.clname1,mmatter[1,6], mmatter[8,12]
ORDER BY TimeCard.ttk
RETURN ttk, tklast, tkloc,oldest,latest,hours, Amount, mdesc1, clname1,
client_id, matter_id
WITH RESUME;END FOREACH;
END PROCEDURE
Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> on 09/25/98 12:59:21 PM
Please respond to Peter.Lancashire.PL1@bayer.co.uk
To: informix-list@iiug.org
cc: (bcc: Rafal Furdzik/PaulWeiss)
Subject: Re: Stored Procedure does not show column names.
Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.
rfurdzik@paulweiss.com wrote:
>
> ---------------------- Forwarded by Rafal Furdzik/PaulWeiss on 09/25/98
> 10:04 AM ---------------------------
>
> Rafal Furdzik
> 09/23/98 10:47 AM
>
> To: informix-list@iiug.org
> cc:
> Subject: Stored Procedure does not show column names.
>
> When I run a stored procedure id does not show column names, but
word
> "(expression)". Does any one know how to assign aliases for columns in
> stored procedures? Please help.
>
> Rafal Furdzik
>
SELECT myproc(myarg) mynewname FROM mytable WHERE ...
The output in DB-Access will be headed "mynewname" instead of
"(expression)".
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281
---
If all else fails, read the instructions AND the release notes.
All opinions are my own and not those of Bayer plc.
My Internet plumbing does not allow me to mail and post news together.
Sorry.
---
Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/