Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query needed

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
EnjoyMF

New User


Joined: 27 May 2005
Posts: 88

PostPosted: Fri Jun 02, 2006 1:12 pm    Post subject: Query needed
Reply with quote

Hi friends

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

Name Date curncy N2 D2 c2 AB001 18 80 AB001 22 25
AB001 18 10 AB001 23 22
AB001 19 90 AB001 23 32
AB001 19 85

Cheers..prasad
Back to top
View user's profile Send private message

prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jun 05, 2006 1:41 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jun 05, 2006 1:43 pm    Post subject:
Reply with quote

Slight correction here 'SELECT A.Name,B.Timestamp, C.currency '
should have been 'SELECT A.Name,A.Timestamp, A.currency'
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1232
Location: Israel

PostPosted: Mon Jun 05, 2006 7:12 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jun 05, 2006 9:10 pm    Post subject:
Reply with quote

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';
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Tue Jun 06, 2006 3:27 pm    Post subject:
Reply with quote

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';
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Jun 06, 2006 7:31 pm    Post subject: Re: Query needed
Reply with quote

prasad,

Does the solution need to be a single 'SELECT', or can you use Temporary tables to store intermediate results?

With temporary table, I think the solution may be not difficult.

Temporary Table solution to follow as I have time.

Dave
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Jun 07, 2006 4:19 am    Post subject: Re: Query needed
Reply with quote

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                                       
  ;                                                                   


results:

Code:


    +---------------------------------------------------+   
    |    NAME    | DATE | CURNCY |     N2     | D2 | C2 |   
    +---------------------------------------------------+   
  1_| AB001      | 18   | 80     | AB001      | 22 | 25 |   
  2_| AB001      | 18   | 10     | AB001      | 23 | 22 |   
  3_| AB001      | 19   | 90     | AB001      | 23 | 32 |   
  4_| AB001      | 19   | 85     |            |    |    |   
    +---------------------------------------------------+   
                                                           


Dave
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us