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

Best Regards,


Note: Also I added the Procedures... It seems so ugly but I need the solve
the problem first and improve later.

THIS IS MY BIRD

CREATE PROCEDURE HESAPOZETI_RAPOR_PROCEDURE
RETURNS (
GELIR_MUH_HAREKETKODU VARCHAR (50),
GELIR_MUH_GELIRMIKTAR DOUBLE PRECISION,
GIDER_MUH_HAREKETKODU VARCHAR (50),
GIDER_MUH_GELIRMIKTAR DOUBLE PRECISION)
AS
BEGIN
/* EXECUTE PROCEDURE HESAPOZETI; ********** Do I need this ********* */
FOR
SELECT
T1.GELIR_MUH_HAREKETKODU,
T1.GELIR_MUH_GELIRMIKTAR,
T2.GELIR_MUH_HAREKETKODU,
T2.GELIR_MUH_GELIRMIKTAR
FROM HESAPOZETI T1
JOIN HESAPOZETI T2
ON T2.SATIRLAR = T1.SATIRLAR
WHERE (
(T1.GELIR_HAREKETKODU_TIPI = 'GELIR') OR
(T1.GELIR_HAREKETKODU_TIPI = 'AIDAT')
)
AND (T2.GELIR_HAREKETKODU_TIPI = 'GIDER')
INTO
:GELIR_MUH_HAREKETKODU,
:GELIR_MUH_GELIRMIKTAR,
:GIDER_MUH_HAREKETKODU,
:GIDER_MUH_GELIRMIKTAR

do
BEGIN
SUSPEND;
END
END


***** THIS IS THE VIEW WITH COUNTER *****


CREATE PROCEDURE HESAPOZETI
RETURNS (
SATIRLAR INTEGER,
GELIR_MUH_HAREKETKODU VARCHAR (50),
GELIR_HAREKETKODU_TIPI VARCHAR (10),
GELIR_MUH_GELIRMIKTAR DOUBLE PRECISION)
AS
declare variable SAYAC INTEGER;
BEGIN
SAYAC = 1;

FOR SELECT
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi,
(SUM(Muh.MUH_MIKTAR)) AS MUH_GELIRMIKTAR
FROM MUHASEBE Muh, HAREKETKODLARI HK
WHERE (HK.HAREKETKODU = Muh.MUH_HAREKETKODU)
and (Hareketkodlari.hareketkodu_tipi = 'GELIR')
GROUP BY
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi
INTO
:GELIR_MUH_HAREKETKODU,
:GELIR_HAREKETKODU_TIPI,
:GELIR_MUH_GELIRMIKTAR

DO
BEGIN
SATIRLAR = SAYAC;
SAYAC = SAYAC+1;
SUSPEND;
END

FOR
SELECT
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi,
(SUM(Muh.MUH_MIKTAR)) AS MUH_GELIRMIKTAR
FROM MUHASEBE Muh, HAREKETKODLARI HK
WHERE (HK.HAREKETKODU = Muh.MUH_HAREKETKODU)
and (Hareketkodlari.hareketkodu_tipi = 'AIDAT')
GROUP BY
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi
INTO
:GELIR_MUH_HAREKETKODU,
:GELIR_HAREKETKODU_TIPI,
:GELIR_MUH_GELIRMIKTAR

DO
BEGIN
SATIRLAR = SAYAC;
SAYAC = SAYAC+1;
SUSPEND;
END

SAYAC=1;

FOR
SELECT
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi,
(SUM(Muh.MUH_MIKTAR)*(-1)) AS MUH_GELIRMIKTAR
FROM MUHASEBE Muh, HAREKETKODLARI HK
WHERE (HK.HAREKETKODU = Muh.MUH_HAREKETKODU)
and (Hareketkodlari.hareketkodu_tipi = 'GIDER')
GROUP BY
Muh.MUH_HAREKETKODU,
Hareketkodlari.hareketkodu_tipi

INTO
:GELIR_MUH_HAREKETKODU,
:GELIR_HAREKETKODU_TIPI,
:GELIR_MUH_GELIRMIKTAR

DO
BEGIN
SATIRLAR = SAYAC;
SAYAC = SAYAC+1;
SUSPEND;
END
END

----- Original Message -----
From: ""Svein Erling"" <svein.erling.tysvaer@...>
Newsgroups: egroups.ib-support
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, November 11, 2003 2:41 PM
Subject: [firebird-support] Re: Query Question... but, hard one


> --- In firebird-support@yahoogroups.com, "Tanz Anthrox" wrote:
> > Hi,
> >
> > I have an example and I could not find a solution for this
> >
> > Table
> >
> > Name Type Amount
> > Food Outcome 500
> > Salary Income 2500
> > Clothes Outcome 50
> > Rental Income 300
> > Car Outcome 80
> > Oil Outcome 50
> > ...
> >
> > and I need a result set for this like,
> >
> > Name Amount Name Amount
> > Food 500 Salary 2500
> > Clothes 50 Rental 300
> > Car 80 null null
> > Oil 50 null null
> >
> >
> > 2 column for Outcome and 2 column for income,
> >
> > Pls, Becareful outcome count and income count may be different.
>
> I think you have to write a stored procedure. Either that, or add a
> new column to your table, e.g.
>
> Name Type Amount IONo
> Food Outcome 500 1
> Salary Income 2500 1
> Clothes Outcome 50 2
> Rental Income 300 2
> Car Outcome 80 3
> Oil Outcome 50 4
>
> and then do
>
> 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'
>
> Note that it will be difficult to make this "multiusersafe", since
> IONo must be a consecutive number without duplicates within each Type.
>
> HTH,
> 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/
>
>