Luca,
You need temp tables and a dbproc :
Note: - parm1 is not used but you may use for futher filter in select.
- In the final select use the table which has more rows as table A.
DROP DBPROC MY_PROC
//
Create dbproc MY_PROC(in parm1 varchar(3))
returns cursor as
Var
sqlstmt varchar(200);
a_rowno int;
a_date date;
a_value int;
b_rowno int;
b_date date;
b_value int;
$CURSOR = 'THIS_CURSOR';
BEGIN
create table TEMP.TABLE1 ( row_num int ,D_ate date,V_alue int );
create table TEMP.TABLE2 ( row_num int ,D_ate date,V_alue int );
DECLARE A1_CURSOR CURSOR FOR
Select rowno,D_ate,V_alue from MYSCHEMA.A1 order by D_ate ;
FETCH A1_CURSOR INTO :b_rowno,:b_date,:b_value;
WHILE $RC = 0 DO
BEGIN
insert into TEMP.TABLE1 values(:b_rowno,:b_date,:b_value);
FETCH NEXT A1_CURSOR INTO :b_rowno,:b_date,:b_value;
END;
DECLARE A2_CURSOR CURSOR FOR
Select rowno,D_ate,V_alue from MYSCHEMA.A2 order by D_ate ;
FETCH A2_CURSOR INTO :b_rowno,:b_date,:b_value;
WHILE $RC = 0 DO
BEGIN
insert into TEMP.TABLE2 values(:b_rowno,:b_date,:b_value);
FETCH NEXT A2_CURSOR INTO :b_rowno,:b_date,:b_value;
END;
DECLARE :$CURSOR CURSOR FOR
Select A.row_num,A.D_ate,A.V_alue,B.D_ate,B.V_alue FROM TEMP.TABLE1 A,
TEMP.TABLE2 B WHERE A.row_num = B.row_num (+) ;
FETCH INTO :a_rowno,:a_date,:a_value,:b_date,:b_value;
DROP TABLE TEMP.TABLE1;
DROP TABLE TEMP.TABLE2;
END;
//
call MY_PROC('x')
result:
1 2006-07-01 50 2006-07-25 50
2 2006-09-01 100 2006-09-13 100
3 2006-10-01 200 NULL NULL
Regards..Jey
-----Original Message-----
From: Schroeder, Alexander [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 14, 2006 7:28 AM
To: Luca Calderano; maxdb@lists.mysql.com
Subject: RE: sql help needed
Hello Luca,
sorry, but I cannot see what will be the common field between your tables. It
looked as you
wanted to group the values by a common 'value' column, as Elke also deduced.
However, as this
is not the case we cannot deduce what kind of result you want to produce, and
which relation
between the data in A and B you want to express or use as qualifier.
Regards
Alexander Schröder
-----Original Message-----
From: Luca Calderano [mailto:[EMAIL PROTECTED]
Sent: Donnerstag, 14. September 2006 13:19
To: maxdb@lists.mysql.com
Subject: Re: sql help needed
I cannot use the query you proposed as the tables A and B never have the
same values for the field "Value" that is
Table A (the table is unordered by Date)
-----------
Date | Value
2006-09-01 | 100
2006-10-01 | 200
2006-07-01 | 50
Table B (the table is unordered by Date)
-----------
Date | Value
2006-09-13 | 200
2006-07-25 | 500
RESULT (the result table is ordered by the field "A.Date" and by the field
"B.Date")
--------------
PROGR | A.Date | A.VALUE | B.Date | B.Value
1 | 2006-07-01 | 50 | 2006-07-25 | 500
2 | 2006-09-01 | 100 | 2006-09-13 | 200
3 | 2006-10-01 | 200 | ? | ?
----- Original Message -----
From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
To: "Luca Calderano" <[EMAIL PROTECTED]>; <maxdb@lists.mysql.com>
Sent: Thursday, September 14, 2006 11:39 AM
Subject: RE: sql help needed
> Hello Luca,
>
> do you mean something like:
>
> select rowno, a."DATE" AS a_date, a."VALUE", b."DATE" as b_date, b."VALUE"
> from a, b where a."VALUE" = b."VALUE" (+) ORDER BY a_date, b_date
>
> Regards
> Alexander Schröder
> SAP Labs Berlin
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
