i have a Table by name ABROBT the fields in the table are
Name,Timestamp, currency
Example
Name timestamp currency
AB001 2006.06.05.18.01.000000 80
AB001 2006.06.05.18.08.000000 10
AB001 2006.06.05.20.08.000000 10
AB001 2006.06.05.19.01.000000 90
AB001 2006.06.05.19.06.000000 85
BB001 2006.06.05.22.06.000000 50
AB001 2006.06.05.22.01.000000 25
BB001 2006.06.05.19.01.000000 90
AB001 2006.06.05.23.01.000000 22
AB001 2006.06.05.23.09.000000 32
I need to get only Name with 'AB001'
and dates between '18,19' and '22,23'
Name Date curncy should present dates 18,19
N2 D2 c fields should present dates 22,23
Joined: 31 May 2006 Posts: 200 Location: Netherlands
TRY THE FOLLOWING QUERY:
SELECT A.Name,B.Timestamp, C.currency, B.Name AS N2,B.Timestamp AS D2, B.currency AS C2
FROM ABROBT A,
ABROBT B
WHERE SUBSTR(CHAR(A.timestamp),12,2) ='18' OR
SUBSTR(CHAR(A.timestamp),12,2) ='19' OR
SUBSTR(CHAR(B.timestamp),12,2) = '22' OR
SUBSTR(CHAR(B.timestamp),12,2) = '23');
If you can optimize this query , please let me know how you did.
I deduce from the example that you want to have the result on two columns, like this (where X is a set of name/time/currency):
Example 1:
X1 X5
X2 X6
X3 X7
X4
Example 2:
X1 X6
X2 X7
X3 X8
X4 X9
X5
If this is the case, I don't think it is possible.
Also, I think that the SELECT proposed by prasadvrk returns more data than you would like...
Joined: 31 May 2006 Posts: 200 Location: Netherlands
Yes Indeed I forgot to add the name in where clause.So following query should get the output in required format:
SELECT A.Name,A.Timestamp, A.currency, B.Name AS N2,B.Timestamp AS D2, B.currency AS C2
FROM ABROBT A,
ABROBT B
WHERE SUBSTR(CHAR(A.timestamp),12,2) ='18' OR
SUBSTR(CHAR(A.timestamp),12,2) ='19' OR
SUBSTR(CHAR(B.timestamp),12,2) = '22' OR
SUBSTR(CHAR(B.timestamp),12,2) = '23') AND
A.Name ='AB001';
Joined: 31 May 2006 Posts: 200 Location: Netherlands
Yes my query will return many more rows than what you need. As I could not create the table in DB2 , I gave hypothetical solution.
Can you try the following query and let me know if this meets your requirement.
SELECT A.Name,SUBSTR(A.Timestamp,12,2), A.currency, B.Name AS N2,SUBSTR(B.Timestamp,12,2) AS D2, B.currency AS C2
FROM ABROBT A,
ABROBT B
WHERE (SUBSTR(CHAR(A.timestamp),12,2) ='18' OR
SUBSTR(CHAR(A.timestamp),12,2) ='19') AND
(SUBSTR(CHAR(B.timestamp),12,2) = '22' OR
SUBSTR(CHAR(B.timestamp),12,2) = '23') AND
A.Name ='AB001';
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
This is a possible solution if the temp_tables are allowed. Your timestamps were not valid, so I changed the format to a valid one.
Code:
--
-- SET UP INITIAL TABLE CONDITION WITH DATA
--
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL
(
NAME CHAR(10),
TS CHAR(26),
CURRENCY DEC(2)
)
;
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-18-01.01.01.000000', 80);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-18-08.01.01.000000', 10);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-20-08.01.01.000000', 10);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-19-01.01.01.000000', 90);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-19-06.01.01.000000', 85);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('BB001', '2006-06-22-06.01.01.000000', 50);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-22-01.01.01.000000', 25);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('BB001', '2006-06-19-01.01.01.000000', 90);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-23-01.01.01.000000', 22);
INSERT INTO SESSION.TEMP_TBL (NAME, TS, CURRENCY)
VALUES ('AB001', '2006-06-23-09.01.01.000000', 32);
--
-- DECLARE WORK TEMP TABLES ONE FOR THE RIGHT AND ONE FOR THE LEFT
-- SIDE OF THE SELECT
--
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL_A
(
ROW_NUM DEC(5) GENERATED ALWAYS AS IDENTITY,
NAME CHAR(10),
DAY CHAR(2),
CURRENCY DEC(2)
)
;
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL_B
(
ROW_NUM DEC(5) GENERATED ALWAYS AS IDENTITY,
NAME CHAR(10),
DAY CHAR(2),
CURRENCY DEC(2)
)
;
--
-- POPULATE WITH LEFT SIDE VALUES, NOTE THAT ROW_NUM WILL BE
-- GENERATED WITH SEQUENTIAL NUMBERS STARTING WITH 1
--
INSERT
INTO SESSION.TEMP_TBL_A
(
NAME,
DAY,
CURRENCY
)
SELECT NAME,
SUBSTR(TS,9,2),
CURRENCY
FROM SESSION.TEMP_TBL
WHERE NAME = 'AB001'
AND SUBSTR(TS,9,2) IN ('18', '19')
;
--
-- POPULATE WITH RIGHT SIDE VALUES, NOTE THAT ROW_NUM WILL BE
-- GENERATED WITH SEQUENTIAL NUMBERS STARTING WITH 1
--
INSERT
INTO SESSION.TEMP_TBL_B
(
NAME,
DAY,
CURRENCY
)
SELECT NAME,
SUBSTR(TS,9,2),
CURRENCY
FROM SESSION.TEMP_TBL
WHERE NAME = 'AB001'
AND SUBSTR(TS,9,2) IN ('22', '23')
;
--
-- FYI, SELECT ALL VALUES FROM WORK TABLES
--
SELECT *
FROM SESSION.TEMP_TBL_A
;
SELECT *
FROM SESSION.TEMP_TBL_B
;
--
-- PUT THE SELECT TOGETHER WITH LEFT AND RIGHT SIDES
--
SELECT COALESCE(A.NAME,' ') AS NAME,
COALESCE(A.DAY,' ') AS DATE,
COALESCE(CAST(DIGITS(A.CURRENCY) AS CHAR(2)),' ') AS CURNCY,
COALESCE(B.NAME,' ') AS N2,
COALESCE(B.DAY,' ') AS D2,
COALESCE(CAST(DIGITS(B.CURRENCY) AS CHAR(2)),' ') AS C2
FROM SESSION.TEMP_TBL_A A
FULL JOIN SESSION.TEMP_TBL_B B
ON A.ROW_NUM = B.ROW_NUM
;
