IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Require a Query to extract data from two tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Fri Jun 24, 2011 12:53 am
Reply with quote

Hi,
I have two tables SELLTAB and HISTSELL. HISTSELL table is the history table for SELLTAB ie when we we update any row of SELLTAB, before updating that row, we insert that row into HISTSELL.

The columns of table SELLTAB are
SELL_NO, SELL_TYPE, SELL_PRICE, LAST_ROW_INSERT_DATE, ROW_INSERT_ID.

And columns of table HISTSELL are
SELL_NO, SELL_TYPE, SELL_PRICE, LAST_ROW_UPDATE_DATE, ROW_UPDATE_ID.

Now I need a query to extract all rows with SELL_TYPE = M from table SELLTAB and table HISTSELL. Criteria for selecting rows from table HISTSELL is; if SELL_NO is available in both the tables then for that SELL_NO (with SELL_TYPE = M),we extract that row from table HISTSELL with maximum LAST_ROW_UPDATE_DATE.
We want result of the above query based on GROUP BY SELL_NO.

Some sample data of both the tables are as below

(1) Rows in SELLTAB table,

SELL_NO SELL_TYPE SELL_PRICE LAST_ROW_INSERT_DATE ROW_INSERT_ID

123 M 170.50 25-04-2011 A333
133 P 50.50 30-04-2011 S111
134 M 200.50 01-05-2011 P222
137 M 300.75 05-05-2011 W333
140 S 111.60 10-05-2011 R555
145 M 345.50 15-05-2011 A333
150 M 110.50 25-05-2011 X678

(2) Rows in HISTSELL table,

SELL_NO SELL_TYPE SELL_PRICE LAST_ROW_UPDATE_DATE ROW_UPDATE_ID

123 M 100.50 26-04-2011 Y333
123 M 120.50 28-04-2011 A333
123 M 150.50 30-04-2011 X333
133 P 40.50 01-05-2011 T111
137 M 250.75 15-05-2011 P333
137 M 270.75 20-05-2011 R333


So, query result should be as below

123 25-04-2011 A333
123 30-04-2011 X333
134 01-05-2011 P222
137 05-05-2011 W333
137 20-05-2011 R333
145 15-05-2011 A333
150 25-05-2011 X678

Row 123 30-04-2011 X333 is selected from HISTSELL table because highest LAST_ROW_UPDATE_DATE is 30-04-2011 among all the three rows with SELL_NO = 123 and SELL_TYPE = M.
Simillarly row 137 20-05-2011 R333 is selected from HISTSELL table among the two rows with SELL_NO = 137 and SELL_TYPE = M.
So, from HISTSELL table, we are selecting only that row with maximum
LAST_ROW_UPDATE_DATE and whose SELL_NO is present in table SELLTAB and with SELL_TYPE = M.

Can anybody please provide me such a query?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jun 24, 2011 1:05 am
Reply with quote

What have you done on your own? We may provide help but we won't do it for you!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 2:20 pm
Reply with quote

This doesn't max sense :
* for each sell_no there is only 1 row in SELLTAB
* the requirements states : only the MAX(LAST_ROW_UPDATE_DATE) out of SELLHIST => 1 row

then why would you need to GROUP BY SELL_NO ?
===============
1) Are you able to write a query to get the row with MAX(LAST_ROW_UPDATE_DATE) from SELLHIST for 1 SELLNO ? (forget about SELLTAB for this exercise)
Many examples are found in this or other forums/fora.
==========
2) use the same technique but add joining to SELLTAB
Back to top
View user's profile Send private message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Fri Jun 24, 2011 10:32 pm
Reply with quote

Hi,
I am using the following query which gives the correct result

SELECT SELL.SELL_NO
,MAX(COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID)
FROM SELLTAB SELL
LEFT OUTER JOIN
HISTSELL HIST
ON SELL.SELL_NO = HIST.SELL_NO
WHERE SELL.SELL_TYPE = 'M';

But, I need the result of above query Group By SELL_NO. So in the above query, if I use GROUP BY SELL.SELL_NO, it gives error.

So can any one please provide a query which gives rows similar to the above query but results should be grouped by SELL_NO.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Jun 25, 2011 12:16 am
Reply with quote

Hello,

Quote:
So in the above query, if I use GROUP BY SELL.SELL_NO, it gives error.

You have been part of the forum for more than 3 years - and apparently have not paid the slightest bit of attention. . .

Do Not Post "It didn't work"

Why have you not posted the error you received?
Back to top
View user's profile Send private message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Sat Jun 25, 2011 9:00 am
Reply with quote

Hi,
I am really sorry as I didnot not explain about the error I got.
When I am using the query in the following way by using GROUP BY, getting error as below

SELECT SELL.SELL_NO
,MAX(COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID) AS A
FROM SELLTAB SELL
LEFT OUTER JOIN
HISTSELL HIST
ON SELL.SELL_NO = HIST.SELL_NO
WHERE SELL.SELL_TYPE = 'M'
GROUP BY SELL.SELL_NO, A;

The error I am getting is,
SQLCODE = -206, ERROR: A IS NOT VALID IN THE CONTEXT WHERE IT IS USED
SQLSTATE = 42703 SQLSTATE RETURN CODE
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Jun 27, 2011 12:10 pm
Reply with quote

Concerning the error :
It says : 'A IS NOT VALID IN THE CONTEXT WHERE IT IS USED '
you use A on 2 places :
- "as A"
- in "group by"
one of those two isn't allowed: guess !

I'm just gonna give a solution because this thread is going nowhere :

Code:
SELECT SELL.SELL_NO
  ,COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
  ,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID)
from selltab sell
left join (select * from histsell hist1
  where hist1.LAST_ROW_UPDATE_DATE =
      (select max(hist2.LAST_ROW_UPDATE_DATE) from histsell hist2
       where hist1.sellno = hist2.sellno)
          ) hist  on sell.sellno = hist.sellno

(there are other solution depending on performance)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts RC query -Time column CA Products 3
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top