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
 

 

DB2 sql query to fetch from two tables
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sandiptcs

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Mon Jan 15, 2007 7:17 pm    Post subject: DB2 sql query to fetch from two tables
Reply with quote

I have 2 tablea TAB1 and TAB2

TAB1 has following attributes

ACC-NUM(key)
VALUE-DATE
AMOUNT-1

TAB2 has the following attributes

ACC-NUM(key)
VAL-DATE
AMOUNT-2

How can I write a SQL QUERY that will fetch the amounts for a particular a/c from both these table sorted by the the date attribute in both these tables

I want the amounts retrieved from either TAB1 or TAB2 at a time and in the ascending order of value date(i.e VALUE-DATE or VAL-DATE)

I tried using ORDER BY MAX(VALUE-DATE,VAL-DATE), but it is not sorting correctly.
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Mon Jan 15, 2007 7:38 pm    Post subject:
Reply with quote

Wouldn't something like select column-name as new-name to both tables allow you to be able to max just a single name
Back to top
View user's profile Send private message
sandiptcs

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Mon Jan 15, 2007 7:54 pm    Post subject: Re: DB2 sql statement
Reply with quote

I could not understand your explanation. Could you please clarify a little bit
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Mon Jan 15, 2007 7:56 pm    Post subject:
Reply with quote

What is your select statement? Please post it wrapped in "code".
Back to top
View user's profile Send private message
sandiptcs

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Mon Jan 15, 2007 8:27 pm    Post subject: Re: DB2 sql statement
Reply with quote

Code:
The sql query is
SELECT   A.BEDRAG,                                 
               B.VALUE_DATE,                             
               A.VALUTADATUM,                             
               B.SUBTOTAL_AMOUNT,                         
                                                           
FROM      TMUTATION A, TSUBTOTAL B     
WHERE    B.SUB_ACCOUNT =  423708724      AND             
              A.BANKRKGNR   =  423708724                       
ORDER BY      MAX(A.VALUTADATUM, B.VALUE_DATE ),   


I want the output in SPUFI to fetch all records from both TMUTATION and TSUBTOTAL , sorted in ascending order on value date
The value date is present in both the tables TMUTATION and TSUBTOTAL

But this query is not working as I want it to
[/code]
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Mon Jan 15, 2007 8:58 pm    Post subject: Re: DB2 sql statement
Reply with quote

Code:
SELECT   A.BEDRAG,                                 
               A.VALUTADATUM as vdate,                             
               A.SUBTOTAL_AMOUNT as samt,                         
FROM      TMUTATION A
WHERE     A.BANKRKGNR   =  423708724                       
union
SELECT   B.BEDRAG,                                 
               B.VALUE_DATE as vdate,                             
               B.SUBTOTAL_AMOUNT as samt,                         
FROM      TSUBTOTAL B     
WHERE    B.SUB_ACCOUNT =  423708724
 
ORDER BY      vdate


Trying to manuver thru bookmanager on an underpowered PC is a pain....
The manual says only one will be returned, I don't understand which, maybe a where vdate = max(vdate)???
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jan 15, 2007 10:27 pm    Post subject:
Reply with quote

Hello,

Please clarify what you need by
Quote:
from both these table sorted by the the date attribute in both these tables


If you sort by the date in table a you'll quite likely get different results than sorting by the date in table b icon_confused.gif

Please give examples of both tables input data and how you'd like the output to be returned.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Tue Jan 16, 2007 12:57 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

Sandip...

This is your problem area... You should not use MAX clause like that...
Code:
ORDER BY      MAX(A.VALUTADATUM, B.VALUE_DATE ),

Instead you can put ORDER BY to have ascending or descending preference rather than giving MAX or MIN.

Try below query... It should work, if I understood what you meant to do... Default for ORDER BY is Ascending...
Code:
SELECT A.ACC_NUM, A.VALUE_DATE, B.VAL_DATE, A.AMT_1, B.AMT_1
FROM    TABLE_1 A,
    TABLE_2 B
WHERE A.ACC_NUM = B.ACC_NUM
ORDER BY A.VALUE_DATE, B.VAL_DATE;
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 16, 2007 2:01 am    Post subject:
Reply with quote

Hi Priyesh,

Ordering by date-a and date-b will execute but i'm not sure if the results will be what Sandip is looking for. The modified query will result in a major/minor sort on the date fields which may be exactly what is desired.

I may be reading a bit too much in the original post, but i had this feeling that the desired output would use one date or the other to order-by depending on which was more/less recent (i.e. the max of the 2).

If Sandip will post example data and the desired results, we can be more helpful.
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 Jan 16, 2007 3:26 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

William,

Sounds like what you have is what sandip wants, to ?concatenate? the two tables together.

William wrote:

The manual says only one will be returned, I don't understand which, maybe a where vdate = max(vdate)???


without knowing exactly what you are looking at, I?ll add this addendum.

?UNION? will eliminate duplicate rows, so only one will be returned,

?UNION ALL? will return ALL rows, even if duplicate.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Jan 16, 2007 3:36 am    Post subject:
Reply with quote

Thanks David, I caught that
Quote:
?UNION? will eliminate duplicate rows, so only one will be returned,
?UNION ALL? will return ALL rows, even if duplicate.
but I still haven't figured out how to guarantee the larger date value.... icon_question.gif
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 Jan 16, 2007 3:50 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

William,

Maybe I'm not reading enough into this. If there are two dates, both rows will be returned and you are 'ORDER'ing by date.

One of the problems ?sandip? has with the code he posted is that he?s getting a Cartesian Product of the two tables. I also don?t understand what he is trying to do with the ORDER BY MAX(? but says he want to ORDER by vdate ascending??
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Jan 16, 2007 4:10 am    Post subject:
Reply with quote

I understoud that he was looking for the amount entry that had the largest/latest date.
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 Jan 16, 2007 4:39 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

This should not be difficult, but wife and dinner are calling. Continue tomorrow?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 16, 2007 7:20 am    Post subject:
Reply with quote

Hello,

Again, i may be way off the mark, but my initial guess was/is that the desired output is the "right date" and both amounts one "result".

If some good sample data data and expected output were posted, we'd not have to guess. . . .
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 Jan 16, 2007 8:56 pm    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

sandip,

Can you give use some examples of date for both tables and expexted results? This would help us a great deal. As you can see there is some variation in the way different people read the requirements.

thanks,
Back to top
View user's profile Send private message
sandiptcs

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Wed Jan 17, 2007 3:01 pm    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

Hi,
Many thanks for the discussion so far.

To clarify my requirement
I have the amount field (BEDRAG) and the date field (VALUEDATUM/VALUE_DATE) in both the the tables

I want to have an SQL query so that it gives me a output of all amounts from both the tables sorted by value date(from both the tables)

The output from the query below is what I desire.

Code:
SELECT   A.BEDRAG as samt,                               
               A.VALUTADATUM as vdate,                             
FROM      TMUTATION A
WHERE     A.BANKRKGNR   =  423708724                       
union all
SELECT   B.BEDRAG as samt,                               
               B.VALUE_DATE as vdate,                             
FROM      TSUBTOTAL B     
WHERE    B.SUB_ACCOUNT =  423708724
ORDER BY      vdate


I guess this query will work if I declare a cursor and embed in a COBOL program as shown below

Code:
DECLARE CURSOR ABC ON
SELECT   A.BEDRAG as samt,                                 
               A.VALUTADATUM as vdate,                             
FROM      TMUTATION A
WHERE     A.BANKRKGNR   =  :var1                       
union all
SELECT   B.BEDRAG as samt,                                 
              B.VALUE_DATE as vdate,                             
FROM      TSUBTOTAL B     
WHERE    B.SUB_ACCOUNT =  :var2
ORDER BY      vdate


But there is one problem. The number columns in both the tables joined by UNION has to match and their datatype must be the same . I wanted to SELECT another additional field called TIMESTAMP from TMUTATION table only but , with a mismatch in the number of colums the query doesnot work.

I dont know how ORDER BY MAX(VALUE_DATE, VALUEDATUM) will work
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 17, 2007 8:14 pm    Post subject:
Reply with quote

Hello,

Please also post what you want the output data to look like.
Back to top
View user's profile Send private message
sandiptcs

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Thu Jan 18, 2007 12:53 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

ok here is what I would like my data to be

TMUTATION table

BEDRAG VALUEDATUM
300 27-9-2006
500 27-12-2006
100 01-01-2007

TSUBTOT table
BEDRAG VALUE_DATE
1000 12-12-2006
200 10-11-2006
150 02-01-2007

desired output

Value date amt
27-9-2006 300
10-11-2006 200
12-12-2006 1000
27-12-2006 500
01-01-2007 100
02-01-2007 150

Please also note my concern in my last post

Quote:
But there is one problem. The number columns in both the tables joined by UNION has to match and their datatype must be the same . I wanted to SELECT another additional field called TIMESTAMP from TMUTATION table only but , with a mismatch in the number of colums the query doesnot work.
Back to top
View user's profile Send private message
DavidatK

Active Member


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

PostPosted: Thu Jan 18, 2007 1:28 am    Post subject: Re: DB2 sql query to fetch from two tables
Reply with quote

sandip,

for the rows that come from the table without the timestamp, what do you want in the timestamp for thoughs columns? spaces, null?
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am


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