I'm not sure, but have you recognized that your script doesn't have 37
lines? This number is the line AFTER the last line of your script. And
I'm not talking about your SP, but your script. The SP itself only
consits of 27 lines.
How do you put your script into the DB? IBexpert? ISql? I think the
terminating character (^) is not recognized by the application you use
to put your script. If you use IBExpert, try to enter the script into
the SQL editor, and not into SQL executive, and without any SET TERM and
COMMIT (beginning with CREATE PROCEDURE and end it with the last END of
your SP), so you can see if your SP is okay or not.


SanTa

2011.03.22. 12:07 keltezéssel, grip_2ls írta:

> Can't work out how to make Yahoo groups show the code with formatting so I have pasted here:
>
> http://pastebin.com/63xxsbMK
>
> Thanks
>
> Neil
> --- In firebird-support@yahoogroups.com, "grip_2ls"<lists@...> wrote:
>> Sure, it is below:
>> SET AUTODDL OFF;SET TERM ^ ;
>> CREATE PROCEDURE SP_KPI( STAFFID INTEGER, FROM_DATE DATE, TO_DATE
>> DATE)RETURNS( FORENAME VARCHAR(50), SURNAME VARCHAR(50), STATUSNAME
>> VARCHAR(50), STATUS_COUNT INTEGER)ASBEGIN FOR SELECT s.forename,
>> s.surname, v2.statusname, count(v2.statusname) as STATUS_COUNT
>> FROM Vacconthist v JOIN Staff s ON v.staffid=s.staffid
>> JOIN Vacstatus v2 ON v2.statusid=v.status WHERE (s.staffid = 867)
>> AND (v.LASTUPDATE BETWEEN '01-Jan-2011' AND '08-Mar-2011') AND (v.status
>>> 9 AND v.status< 99999) GROUP BY s.forename, s.surname,
>> v2.statusname INTO :FORENAME, :SURNAME, :STATUSNAME,
>> :STATUS_COUNT DO BEGIN SUSPEND; ENDEND^
>> SET TERM ; ^COMMIT WORK;
>> --- In firebird-support@yahoogroups.com, Sándor Tamás
>> <sandortamas@> wrote:
>>> Hi,
>>>
>>>
>>> Could you please, send the modified SP to the list?
>>>
>>> SanTa
>>>
>>> 2011.03.21. 16:24 keltezéssel, grip_2ls írta:
>>>> Thanks Woody but the SQL works fine and gives me what I want - I
>> already tested it and validated it before tyring to put it into a stored
>> procedure. The issue is the syntax of the stored procedure.
>>>> Neil
>>>>
>>>> --- In firebird-support@yahoogroups.com, "Woody"woody-tmw@ wrote:
>>>>> From: "grip_2ls"lists@
>>>>>> Hi Thomas
>>>>>>
>>>>>> Thanks for the quick response.
>>>>>>
>>>>>> Unfortunately this doesn't work. I now get "Token unknown - line
>> 37,
>>>>>> column 1" which is the INTO line.
>>>>>>
>>>>> Not sure but you should use HAVING clause, not WHERE clause when
>> doing
>>>>> aggregate queries. The best way, IMO, to test these things is to
>> use a
>>>>> visual query builder like IBO Admin or something like it to get the
>> query
>>>>> right first. Once you have it right, then just wrap it with FOR and
>> INTO and
>>>>> put it in the procedure and it should work fine, in most cases.
>>>>>
>>>>> HTH
>>>>> Woody (TMW)
>>>>>
>>>>
>>>>
>>>> ------------------------------------
>>>>
>>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>>
>>>> Visit http://www.firebirdsql.org and click the Resources item
>>>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>>>
>>>> Also search the knowledgebases at http://www.ibphoenix.com
>>>>
>>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>> Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]