When the table is not big:

With stored procedure you can do it this way.
It wil work only if second select returns rows always in the same order.


create procedure TT_TEST
returns (
NAME1 VARCHAR(20),
AMOUNT1 INTEGER,
NAME2 VARCHAR(20),
AMOUNT2 INTEGER
)
as
declare variable SKIPP INTEGER;
declare variable i INTEGER;
declare variable X1 INTEGER;
declare variable X2 INTEGER;
declare variable itis INTEGER;
declare variable BIGGER varchar(20);
declare variable SMALLER varchar(20);
begin
select count(*) from tt where name = 'OUTCOME'
into x1;
select count(*) from tt where name = 'INCOME'
into x2;

if (X1 >= X2) then
begin
BIGGER = 'OUTCOME';
SMALLER = 'INCOME';

end
ELSE
begin
VECJI = 'INCOME';
MANJSI = 'OUTCOME';
end

SKIPP = 0;
FOR SELECT TT1.name, TT1.amount FROM TT TT1
WHERE TT1.typ = :BIGGERi
INTO :NAME1, :AMOUNT1
DO begin
i = 0; itis = 0;
for select TT2.name, TT2.amount FROM TT TT2
WHERE TT2.typ = :SMALLER
INTO :NAME2, :AMOUNT2
do
begin
if (i = skipp) then
begin
SUSPEND;
itis = 1;
skipp = skipp + 1;
i = smaller + 1000; /* Better if you put second select in
another procedure */
end
else begin
i = i + 1;
end

end
if (itis = 0) then
begin
name2 = null; amount2 = Null;
suspend;

end
END
end


Regards
Bogdan

-----Original Message-----
From: Svein Erling [mailto:svein.erling.tysvaer@...]
Sent: Wednesday, November 12, 2003 1:24 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Query Question... but, hard one

--- In firebird-support@yahoogroups.com, "Tanz Anthrox" wrote:
>
> I wrote a SP for this
> and I used your Query
>
> > SELECT T1.Name, T1.Amount, T2.Name, T2.Amount
> > FROM Table T1
> > FULL JOIN Table T2 on T2.IONo = T1.IONo
> > WHERE T1."Type" = 'Outcome' AND
> > T2."Type" = 'Income'
>
> BUT.. I have 7 rows in T1 (Outcome) and 10 rows in T2 (Income)
>
> The Result set of this Query consists of 7 rows.
>
> How can I achieve this....

Sorry, I never use FULL JOIN and didn't know they were this demanding.

Try

SELECT T1.Name, T1.Amount, T2.Name, T2.Amount
FROM Table T1
FULL JOIN Table T2 on T2.IONo = T1.IONo AND
T1."Type" = 'Outcome' AND
T2."Type" = 'Income'
WHERE T1."Type" = 'Outcome' OR
T2."Type" = 'Income'

I think this should work, but haven't quite figured out why.

Set



To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/