Tanz,

Tanz Anthrox schrieb:

> I wrote a SP for this
> and I used your Query

it's still a curious task you have.
But you can do it with a SP.

I don't know how good the performance will be if you got a lots of data,
but have a look at the following:

CREATE TABLE INOUT (
INOUT_ID INTEGER NOT NULL,
INOUT_NAME VARCHAR(30),
INOUT_TYPE VARCHAR(10),
INOUT_AMOUNT DECIMAL(15,2)
);

INSERT INTO INOUT ( 1,'FOOD','IN',300);
INSERT INTO INOUT ( 2,'ANOTHER ONE','IN',100);
INSERT INTO INOUT ( 3,'YET ANOTHER ONE','IN',150);
INSERT INTO INOUT ( 4,'DUNNO','OUT',100);



Now use a StoredProc like:

CREATE PROCEUDRE SEL_INOUT
RETURNS (
NAME_IN VARCHAR(30),
AMOUNT_IN DECIMAL(15,2),
NAME_OUT VARCHAR(30),
AMOUNT_OUT DECIMAL(15,2)
)
AS
DECLARE VARIABLE MAX_IN INTEGER;
DECLARE VARIABLE MAX_OUT INTEGER;
DECLARE VARIABLE MAX_USE INTEGER;
DECLARE VARIABLE I INTEGER;
BEGIN

SELECT COUNT (*) FROM INOUT
WHERE INOUT_TYP = 'IN'
INTO :MAX_IN;

SELECT COUNT (*) FROM INOUT
WHERE INOUT_TYP = 'OUT'
INTO :MAX_OUT;

IF ( MAX_IN > MAX_OUT ) THEN MAX_USE = MAX_IN;
ELSE MAX_USE = MAX_OUT;

I = 0;
WHILE ( I < MAX_USE ) DO
BEGIN
NAME_IN = NULL;
NAME_OUT = NULL;
AMOUNT_IN = NULL;
AMOUNT_OUT = NULL;

SELECT FIRST 1 SKIP (:I)
INOUT_NAME, INOUT_AMOUNT
FROM INOUT
WHERE INOUT_TYP = 'IN'
INTO :NAME_IN, :AMOUNT_IN;

SELECT FIRST 1 SKIP (:I)
INOUT_NAME, INOUT_AMOUNT
FROM INOUT
WHERE INOUT_TYP = 'OUT'
INTO :NAME_OUT, :AMOUNT_OUT;

SUSPEND;
I = I + 1;

END
END

For the data supplied above a SLECT * FROM SEL_INOUT it should return :

NAME_IN AMOUNT_IN NAME_OUT AMOUNT_OUT
--------------------------------------------------
FOOD 300,00 DUNNO 100,00
ANOTHER ONE 100,00 <NULL> <NULL>
YET ANOTHER ONE 150,00 <NULL> <NULL>

But, as said, this is not the fastest solution (a lot of operations!).

It'd be better to use the example you've been given to do a regular
select and show the data on the cleint side in sth. like a ListView,
where you can populate the columns on your own.

Luc.