At 04:55 PM 19/01/2004 +0000, you wrote:

>Why the same select from the console and stored procedure gives
>different results? Initial params are the same:
>PAVADINIMAS_IN = '' & MIESTAS_IN = ''
>
>CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
>PAVADINIMAS_IN VARCHAR (50), MIESTAS_IN VARCHAR (24))
>RETURNS (...)
>AS
>BEGIN
> for select k.* , ( select z."Vardas" || ' ' || z."Pavarde" from
>ZMONES z where z."Zmogaus_ID" = k."Vadovo1_ID" ) as "Vadovas1" ,
>( select z."Vardas" || ' ' || z."Pavarde" from ZMONES z where
>z."Zmogaus_ID" = k."Vadovo2_ID" ) as "Vadovas2" , ( select z."Vardas"
>|| ' ' || z."Pavarde" from ZMONES z where z."Zmogaus_ID" =
>k."Vadovo3_ID" ) as "Vadovas3" from KOLEKTYVAI k
> where "Pavadinimas" starting with :Pavadinimas_in and "Miestas"
>starting with :Miestas_in
> into .....
> do
> suspend;
>END
>
>With the '' & '' params I got an empty dataset, but if I make select
>query from the console, I got all matching records. Why is the difference?
>Using FB 1.5 rc8

CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50), MIESTAS_IN VARCHAR (24))
RETURNS (...)
AS
BEGIN
for select
k.* ,
( select z."Vardas" || ' ' || z."Pavarde" from ZMONES z
where z."Zmogaus_ID" = k."Vadovo1_ID" ) as "Vadovas1" , /* A */
( select z."Vardas" || ' ' || z."Pavarde" from ZMONES z
where z."Zmogaus_ID" = k."Vadovo2_ID" ) as "Vadovas2" , /* B */
( select z."Vardas" || ' ' || z."Pavarde" from ZMONES z
where z."Zmogaus_ID" = k."Vadovo3_ID" ) as "Vadovas3"
from KOLEKTYVAI k

where ??."Pavadinimas" starting with :Pavadinimas_in /* C */
and ??."Miestas" starting with :Miestas_in
into .....
do
suspend;
END

Problem A: Don't name the run-time output fields, since they are being
input to the output parameters (not really a problem, stylistically wrong)

Problem B: You're trying to use multiple subqueries on the same cursor to
derive multiple run-time fields - it doesn't work this way. Either use a
join between KOLEKTYVAI and ZMONES or define separate aliases for each
subquery

Problem C: You have omitted the table specifiers from the search arguments.

Oh, and problem D: SQL is made much easier if you lay out queries in a way
that lets you see what you're doing.

If you don't need null records for non-matches I suggest:

CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50), MIESTAS_IN VARCHAR (24))
RETURNS (...)
AS
BEGIN
for select
k.* ,
z1."Vardas" || ' ' || z1."Pavarde",
z2."Vardas" || ' ' || z2."Pavarde",
z3."Vardas" || ' ' || z3."Pavarde"

from KOLEKTYVAI k

join ZMONES z1
on z1."Zmogaus_ID" = k."Vadovo1_ID"
join ZMONES z2 on
on z2."Zmogaus_ID" = k."Vadovo2_ID"
join ZMONES z3
on z3."Zmogaus_ID" = k."Vadovo3_ID"

where ??."Pavadinimas" starting with :Pavadinimas_in
and ??."Miestas" starting with :Miestas_in
/* insert correct table identifier into these search specifiers */
into .....
do
suspend;
END

If in fact you *do* need nulls where zmones."Zmogaus_ID" doesn't find a
match, then three separate subquery cursors might get you there more easily
than using left outer joins:

CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50), MIESTAS_IN VARCHAR (24))
RETURNS (...)
AS
BEGIN
for select
k.* ,
( select z1."Vardas" || ' ' || z1."Pavarde" from ZMONES z1
where z1."Zmogaus_ID" = k."Vadovo1_ID"),
(select z2."Vardas" || ' ' || z2."Pavarde" from ZMONES z2
where z2."Zmogaus_ID" = k."Vadovo2_ID"),
(select z3."Vardas" || ' ' || z3."Pavarde" from ZMONES z3
where z3."Zmogaus_ID" = k."Vadovo3_ID"),

from KOLEKTYVAI k
where ??."Pavadinimas" starting with :Pavadinimas_in
and ??."Miestas" starting with :Miestas_in
/* insert correct table identifier into these search specifiers */
into .....
do
suspend;
END

What is the unicode problem that the subject refers to? Are these
unicode_fss columns? If so, you need to do this:

CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50) CHARACTER SET UNICODE_FSS,
MIESTAS_IN VARCHAR (24) CHARACTER SET UNICODE_FSS)
RETURNS (
X1 VARCHAR(..) CHARACTER SET UNICODE_FSS,
and so on...)

/heLen