Hello,

In the given SP, I've concatenated country, state & county and
fetched it as location. Whether I need to write INTO for each
individual select in SP (i.e. SELECT .. UNION SELECT .. UNION
SELECT .. ) or only one INTO at the end of last SELECT ?
If I write INTO for individual SELECT, it gave error - "Token
Unknown - UNION" & if INTO is written at the end of last SELECT, then
it gives error "Invalid command. Data type unknown."
Any help appreciated.

Part of SP :

SELECT tblcountry.country || '/' || tblstate.state || '/' ||
tblcounty.county AS location ,tblcounty.countyid AS locid ,'1' AS
name FROM tblcounty,tblstate,tblcountry WHERE tblcountry.countryid =
tblstate.countryid AND tblcounty.stateid = tblstate.stateid AND
countyid IN (SELECT tblcounty.countyid FROM tblcounty,tbltaxcounty
WHERE tblcounty.countyid = tbltaxcounty.countyid AND
tbltaxcounty.taxid=:taxno )

UNION
SELECT tblcountry.country || '/' || tblstate.state AS
location ,tblstate.stateid AS locid ,'2' AS state
FROM tblstate,tblcountry WHERE tblcountry.countryid =
tblstate.countryid AND tblstate.stateid IN ( SELECT tblstate.stateid
FROM tblstate,tbltaxstate WHERE tblstate.stateid =
tbltaxstate.stateid AND tbltaxstate.taxid=:taxno)
UNION
SELECT country,countryid AS locid ,'3' AS country FROM tblcountry
WHERE countryid IN ( SELECT tblcountry.countryid FROM
tblcountry,tbltaxcountry WHERE tblcountry.countryid =
tbltaxcountry.countryid AND tbltaxcountry.taxid=:taxno)


Regards,
Ghanshyam.


--- In firebird-support@yahoogroups.com, "Fabrice Aeschbacher"
<fabrice.aeschbacher@s...> wrote:

> Ghanshyam,
>
> Here is an example:
>
> create procedure MyProc ( InParam1 integer )
> returns ( OutParam1 integer, OutParam2 as varchar(10))
> as
> begin
> for select Field1, Field2 from MyTable
> where TheKey = :InParam1
> into :OutParam1, :OutParam2
>
> do suspend;
> end
>
> --- In firebird-support@yahoogroups.com, "Ghanshyam"
> <ghanshyam_patil@m...> wrote:
> > Hello,
> > When I'm trying to create a SP (Having union of 3 nested select
> > statements) by executing the following code (The entire code is

given

> > below), it gives me an error -
> >
> > "Token unknown - SUSPEND"
> >
> > iF SUSPEND is removed, it gives error "Token unknown - END"
> > i.e anything written after last SELECT is an error. May be last
> > SELECT is having some problem. Or is there any other problem

related

> > to UNION?
> >
> >
> > CREATE PROCEDURE showlocation(taxno INTEGER)
> > AS
> > BEGIN
> > SELECT tblcountry.country || '/' || tblstate.state || '/' ||
> > tblcounty.county AS location ,tblcounty.countyid AS locid ,'1'

AS

> > name
> > FROM tblcounty,tblstate,tblcountry WHERE tblcountry.countryid

=

> > tblstate.countryid AND
> > tblcounty.stateid = tblstate.stateid AND countyid IN
> > (SELECT tblcounty.countyid FROM tblcounty,tbltaxcounty WHERE
> > tblcounty.countyid = tbltaxcounty.countyid AND
> > tbltaxcounty.taxid=:taxno)
> > UNION
> > SELECT tblcountry.country || '/' || tblstate.state AS
> > location ,tblstate.stateid AS locid ,'2' AS state
> > FROM tblstate,tblcountry WHERE tblcountry.countryid =
> > tblstate.countryid AND
> > tblstate.stateid IN ( SELECT tblstate.stateid FROM
> > tblstate,tbltaxstate WHERE tblstate.stateid = tbltaxstate.stateid

AND

> > tbltaxstate.taxid=:taxno)
> > UNION
> > SELECT country,countryid AS locid ,'3' AS country FROM

tblcountry

> > WHERE countryid IN ( SELECT tblcountry.countryid FROM
> > tblcountry,tbltaxcountry WHERE tblcountry.countryid =
> > tbltaxcountry.countryid AND tbltaxcountry.taxid=:taxno)
> > SUSPEND;
> > END
> >
> >
> > Thanx in advance for any help.
> >
> > Regards,
> > Ghanshyam.