Try:

...
AS
DECLARE VARIABLE MY_PATIENT SMALLINT;
BEGIN
SELECT
CFG_PERSONALIZE.PERSONALIZE_ISMYPATIENTONLY
FROM
CFG_PERSONALIZE
WHERE LOGIN_ID = :LOGIN_ID_IN
INTO
MY_PATIENT;

(Note the drop of the second BEGIN/END block is not required)

IF (MY_PATIENT = 1) THEN
Begin
FOR
SELECT
...
UNION
SELECT
...
INTO
...
DO
Begin
SUSPEND;
End
End
ELSE
Begin
FOR
SELECT
...
UNION
SELECT
...
INTO
...
DO
Begin
SUSPEND;
End
END

Note the new Begin/End statements, as well as the SUSPEND in the ELSE
branch (which was missing)


Sean

> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of sasidhardoc
> Sent: Tuesday, April 24, 2007 6:53 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Help with IF/ELSE in SP
>
> I am at loss as to how to make the ELSE condition work in this SP. I
> have commented the ELSE section. Thanks in advance
> ----------------------
> DECLARE VARIABLE MY_PATIENT SMALLINT;
> BEGIN
> BEGIN
> SELECT CFG_PERSONALIZE.PERSONALIZE_ISMYPATIENTONLY FROM
> CFG_PERSONALIZE
> WHERE LOGIN_ID = :LOGIN_ID_IN
> INTO
> MY_PATIENT;
>
> END
>
>
> BEGIN
> IF (MY_PATIENT = 1) THEN
> FOR SELECT
> PIM_APPOINTMENTS.APPOINTMENT_ID,
> PIM_APPOINTMENTS.APPOINTMENT_SUBJECT,
> PIM_APPOINTMENTS.APPOINTMENT_LOCATION,
> PIM_APPOINTMENTS.APPOINTMENT_STARTTIME,
> PIM_APPOINTMENTS.APPOINTMENT_ENDTIME,
> PIM_APPOINTMENTS.APPOINTMENT_AUTHOR_ID,
> PIM_APPOINTMENTS.APPOINTMENT_DESCRIPTION,
> PIM_APPOINTMENTS.PATIENT_ID,
> PIM_APPOINTMENTS.VISITTYPE_ID,
> PIM_REMINDERS.REMINDERS_ID,
> PIM_REMINDERS.REMINDERS_INTERVAL,
> PIM_REMINDERS.REMINDERS_HASREMINDER,
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> FROM
> PIM_APPOINTMENTS JOIN
> LINK_APPOINTMENTS_ENTITY ON
> PIM_APPOINTMENTS.APPOINTMENT_ID =
> LINK_APPOINTMENTS_ENTITY.APPOINTMENT_ID
> JOIN PIM_OWNERS ON PIM_OWNERS.ENTITY_ID =
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> LEFT OUTER JOIN
> PIM_REMINDERS ON PIM_APPOINTMENTS.APPOINTMENT_ID =
> PIM_REMINDERS.APPOINTMENT_ID AND
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID = PIM_REMINDERS.ENTITY_ID
> WHERE PIM_OWNERS.LOGIN_ID = :LOGIN_ID_IN AND
> PIM_APPOINTMENTS.APPOINTMENT_ID = :APPOINTMENT_ID_IN
>
> UNION
>
> SELECT
> PIM_APPOINTMENTS.APPOINTMENT_ID,
> PIM_APPOINTMENTS.APPOINTMENT_SUBJECT,
> PIM_APPOINTMENTS.APPOINTMENT_LOCATION,
> PIM_APPOINTMENTS.APPOINTMENT_STARTTIME,
> PIM_APPOINTMENTS.APPOINTMENT_ENDTIME,
> PIM_APPOINTMENTS.APPOINTMENT_AUTHOR_ID,
> PIM_APPOINTMENTS.APPOINTMENT_DESCRIPTION,
> PIM_APPOINTMENTS.PATIENT_ID,
> PIM_APPOINTMENTS.VISITTYPE_ID,
> PIM_REMINDERS.REMINDERS_ID,
> PIM_REMINDERS.REMINDERS_INTERVAL,
> PIM_REMINDERS.REMINDERS_HASREMINDER,
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> FROM
> PIM_APPOINTMENTS JOIN
> LINK_APPOINTMENTS_ENTITY ON
> PIM_APPOINTMENTS.APPOINTMENT_ID =
> LINK_APPOINTMENTS_ENTITY.APPOINTMENT_ID
> LEFT OUTER JOIN
> PIM_REMINDERS ON PIM_APPOINTMENTS.APPOINTMENT_ID =
> PIM_REMINDERS.APPOINTMENT_ID AND
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID = PIM_REMINDERS.ENTITY_ID
> WHERE LINK_APPOINTMENTS_ENTITY.ENTITY_ID = :MEDFACENTITY_ID_IN

AND

> PIM_APPOINTMENTS.APPOINTMENT_ID = :APPOINTMENT_ID_IN
> INTO
> :APPOINTMENT_ID,
> :APPOINTMENT_SUBJECT,
> :APPOINTMENT_LOCATION,
> :APPOINTMENT_STARTTIME,
> :APPOINTMENT_ENDTIME,
> :APPOINTMENT_AUTHOR_ID,
> :APPOINTMENT_DESCRIPTION,
> :PATIENT_ID,:VISITTYPE_ID,
> :REMINDERS_ID,
> :REMINDERS_INTERVAL,
> :REMINDERS_HASREMINDER,
> :ENTITY_ID
> DO
> SUSPEND;
> END
> /*ELSE
> PIM_APPOINTMENTS.APPOINTMENT_ID,
> PIM_APPOINTMENTS.APPOINTMENT_SUBJECT,
> PIM_APPOINTMENTS.APPOINTMENT_LOCATION,
> PIM_APPOINTMENTS.APPOINTMENT_STARTTIME,
> PIM_APPOINTMENTS.APPOINTMENT_ENDTIME,
> PIM_APPOINTMENTS.APPOINTMENT_AUTHOR_ID,
> PIM_APPOINTMENTS.APPOINTMENT_DESCRIPTION,
> PIM_APPOINTMENTS.PATIENT_ID,
> PIM_APPOINTMENTS.VISITTYPE_ID,
> PIM_REMINDERS.REMINDERS_ID,
> PIM_REMINDERS.REMINDERS_INTERVAL,
> PIM_REMINDERS.REMINDERS_HASREMINDER,
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> FROM
> PIM_APPOINTMENTS JOIN
> LINK_APPOINTMENTS_ENTITY ON
> PIM_APPOINTMENTS.APPOINTMENT_ID =
> LINK_APPOINTMENTS_ENTITY.APPOINTMENT_ID
> JOIN PIM_OWNERS ON PIM_OWNERS.ENTITY_ID =
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> LEFT OUTER JOIN
> PIM_REMINDERS ON PIM_APPOINTMENTS.APPOINTMENT_ID =
> PIM_REMINDERS.APPOINTMENT_ID AND
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID = PIM_REMINDERS.ENTITY_ID
> WHERE PIM_OWNERS.LOGIN_ID = :LOGIN_ID_IN AND
> PIM_APPOINTMENTS.APPOINTMENT_ID = :APPOINTMENT_ID_IN
>
> UNION
>
> SELECT
> PIM_APPOINTMENTS.APPOINTMENT_ID,
> PIM_APPOINTMENTS.APPOINTMENT_SUBJECT,
> PIM_APPOINTMENTS.APPOINTMENT_LOCATION,
> PIM_APPOINTMENTS.APPOINTMENT_STARTTIME,
> PIM_APPOINTMENTS.APPOINTMENT_ENDTIME,
> PIM_APPOINTMENTS.APPOINTMENT_AUTHOR_ID,
> PIM_APPOINTMENTS.APPOINTMENT_DESCRIPTION,
> PIM_APPOINTMENTS.PATIENT_ID,
> PIM_APPOINTMENTS.VISITTYPE_ID,
> PIM_REMINDERS.REMINDERS_ID,
> PIM_REMINDERS.REMINDERS_INTERVAL,
> PIM_REMINDERS.REMINDERS_HASREMINDER,
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID
> FROM
> PIM_APPOINTMENTS JOIN
> LINK_APPOINTMENTS_ENTITY ON
> PIM_APPOINTMENTS.APPOINTMENT_ID =
> LINK_APPOINTMENTS_ENTITY.APPOINTMENT_ID
> LEFT OUTER JOIN
> PIM_REMINDERS ON PIM_APPOINTMENTS.APPOINTMENT_ID =
> PIM_REMINDERS.APPOINTMENT_ID AND
> LINK_APPOINTMENTS_ENTITY.ENTITY_ID = PIM_REMINDERS.ENTITY_ID
> WHERE LINK_APPOINTMENTS_ENTITY.ENTITY_ID = :MEDFACENTITY_ID_IN

AND

> PIM_APPOINTMENTS.APPOINTMENT_ID = :APPOINTMENT_ID_IN
>
> INTO
> :APPOINTMENT_ID,
> :APPOINTMENT_SUBJECT,
> :APPOINTMENT_LOCATION,
> :APPOINTMENT_STARTTIME,
> :APPOINTMENT_ENDTIME,
> :APPOINTMENT_AUTHOR_ID,
> :APPOINTMENT_DESCRIPTION,
> :PATIENT_ID,:VISITTYPE_ID,
> :REMINDERS_ID,
> :REMINDERS_INTERVAL,
> :REMINDERS_HASREMINDER,
> :ENTITY_ID*/
> END
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>