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
 

 

Compound Query

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

New User


Joined: 24 Jun 2008
Posts: 4
Location: Rochester

PostPosted: Sat Jan 21, 2012 12:58 am    Post subject: Compound Query
Reply with quote

Appreciate if someone could look at this & provide an UPDATE query

Table A - IPMA

BUSSINESS_ENTITY (pk)
PRICE_TYPE (pk)
PRICE_ID (pk)
DATE_TIME
USERID
BUSN_ENT
ITEM_TYPE
ITEM_ID
TYPE
SUPPLIER
STATUS
STATUS_DATETIME

Table B - IPDT

BUSINESS_ENTITY (pk)
PRICE_TYPE (pk)
PRICE_ID (pk)
BUS_ENT (pk)
TYPE (pk)
CODE
EFF_START
DATETIME
USERID
EFF_STOP
ENTER_DATE
PRICE
STATUS
STATUS_DATETIME
REFERENCE


The ITEM_ID is ONE-to-MANY, with respect to EFF_START & EFF_STOP (from table: IPDT)

The total rows on table: IPDT = 980,000
With the following query, I got: 2500 rows,

Now, I need a SINGLE query, to update EFF_DATE of 2500 rows, on table: IPDT. (I tried with using EXISTS, IN on UPDATE statement but could not yet achieve desired result).

SELECT A.ITEM_ID,
B.EFF_START,
B.EFF_STOP
FROM IPMA A,
IPDT B
WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY
AND A.PRICE_TYPE = B.PRICE_TYPE
AND A.PRICE_ID = B.PRICE_ID
AND B.EFF_START =
(SELECT MAX(C.EFF_START)
FROM IPDT C
WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY
AND C.PRICE_TYPE = B.PRICE_TYPE
AND C.PRICE_ID = B.PRICE_ID
)
AND B.EFF_STOP BETWEEN '2011-06-30'
AND '9999-06-30'
AND B.EFF_STOP <> '9999-06-30');
Code:
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1771
Location: Bloomington, IL

PostPosted: Sat Jan 21, 2012 1:02 am    Post subject:
Reply with quote

You should probably begin by defining a column named EFF_DATE on IPDT.
Back to top
View user's profile Send private message
Cruise

New User


Joined: 24 Jun 2008
Posts: 4
Location: Rochester

PostPosted: Sat Jan 21, 2012 1:16 am    Post subject:
Reply with quote

Akatsukami - Thanks for the quick reply.

Can you you elaborate what you mean - if you are saying to ADD another physical column to the table IPDT, that is not possible.

This query is updating, around 89000 rows - but expected to update 2500 rows only.

UPDATE IPDT
SET EFF_STOP = '9999-06-30'
WHERE EFF_STOP IN
(SELECT B.EFF_STOP
FROM IPMA A,
IPDT B
WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY
AND A.PRICE_TYPE = B.PRICE_TYPE
AND A.PRICE_ID = B.PRICE_ID
AND B.EFF_START =
(SELECT MAX(C.EFF_START)
FROM IPDT C
WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY
AND C.PRICE_TYPE = B.PRICE_TYPE
AND C.PRICE_ID = B.PRICE_ID
)
AND B.EFF_STOP BETWEEN '2011-06-30'
AND '9999-06-30'
AND B.EFF_STOP <> '9999-06-30');
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1771
Location: Bloomington, IL

PostPosted: Sat Jan 21, 2012 1:58 am    Post subject:
Reply with quote

Note that as there is no column named EFF_DATE, you can't update it. Is EFF_DATE a typo for EFF_START, EFF_STOP, or ENTER_DATE, or do you mean "I want to set a certain range of effective dates, i.e., set EFF_START to this value, and EFF_STOP to that value"?
Back to top
View user's profile Send private message
Cruise

New User


Joined: 24 Jun 2008
Posts: 4
Location: Rochester

PostPosted: Sat Jan 21, 2012 2:23 am    Post subject:
Reply with quote

A certain range - MAX(EFF_START) and EFF_STOP (BETWEEN '2011-06-30' AND '9999-06-30')

And, SET EFF_STOP to: '9999-06-30, which are not equal to '9999-06-30'.
Back to top
View user's profile Send private message
Cruise

New User


Joined: 24 Jun 2008
Posts: 4
Location: Rochester

PostPosted: Sat Jan 21, 2012 2:25 am    Post subject:
Reply with quote

Yes, EFF_DATE is a TYPO - Please read it as, EFF_STOP of 2500 rows...
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
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
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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