[Reply]
Hi Jeremy et alia .....
Notes:
1. Debugging Stored Procedures is easier if you lay out the query in a more neatly
formatted manner - it helps the eye see the problem.
2. Not having the database to test, all said here is pure conjecture!!
3. Your query will return a number of records, so you need to use the "for select ..."
construct. See below.
4. The data returned by the select statement needs to be "stored" INTO the return
parameters.
5. Use the SET TERM command to tell the query procesor that the end of statement
terminator needs to be changed. This is because of the conflict between the SP
compiler and the query processor using the same character (";").
6. SUSPEND causes the data in the return parameters to be "returned" as a record of
data, when you run the sp in a query. eg "select * from myStoredProc()myParams)". So
it must be in the construct:
for select abc
from xyz
into :rABC
begin
suspend;
end
HTH.
Gary
My take (untested of course .....) of what it should look like.
set term !!;
CREATE PROCEDURE ExtensionSummary_ssp(
iExtensionFrom integer,
iExtensionTo integer,
dDateFrom date,
dDateTo date,
tTimeFrom time,
tTimeTo time
)
RETURNS
(
total_calls integer, call_date date , call_time time,
call_duration time, call_extension integer, call_trunk integer,
call_type char(1), call_dialed_number Char(50), call_destination char
(50),
total_cost decimal(12,2), cost decimal(12,2), Account char(50),
CallerID char(50), extension_name char(5)
)
AS
Begin
for select count(*) as total_calls,ch.call_date,ch.call_time,
ch.call_duration,ch.call_extension,ch.call_trunk,call_type,
ch.call_dialed_number, ch.map_desc as call_destination,
SUM(Cost) as total_cost, ch.cost , ch.acc_desc as Account,
ch.CallerID, ch.call_extension as extension_name,
'Date From 01/01/2004 to 20/02/2004 and Time From 00:00:00 to 23:59:59' as
date_from,
'Extension From 200 to 211' as extn_from, 'Extension Report' as Report_title,
'Brophy Knight and Partners' as companyname
from callhistory ch
left join extension ex on ex.number = ch.call_extension
WHERE ch.call_extension >= 210
and ch.call_extension <=299
AND ch.call_date between '01 December 2003' and '20 December 2003'
AND cast(ch.call_time as time) between cast('12:00:00' as time) and cast ('23:00:00'
as time)
AND (call_type_id in (1,2,3,4,5,7,8,6,9,0))
group by
ch.call_extension,ch.call_date,ch.call_time,ch.call_duration,ch.call_trunk
,ch.call_type,ch.call_dialed_number,Call_destination,Account,ch.CallerID,c
h.map_desc,ch.call_destination, ch.acc_desc, ch.Account, ch.cost,
ch.call_extension,ex.owner
into
:total_calls, :call_date, :call_time, :call_duration, :call_extension, :call_trunk,
:call_type,
:call_dialed_number, :call_destination, :total_cost, :cost, :Account, :CallerID,
:extension_name
begin
suspend;
end
end
========================================================
Gary Benner - e-Engineer and Software Developer - [EMAIL PROTECTED]
123 Internet Ltd
Lecturer in Information Technology - Waiariki Institute of Technology
_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
