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: 1746
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: 1746
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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