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]
>