-
Ok, after changes were made, I am still getting multiple release dates for 1 document title.
How does the system know that we want only the max date in LCH for unique ID's only?
Here's a sample of the tables:
WT table:
IDA2A2: TITLE
5455135 TEST1
1546413 TEST2
6754313 TEST3
3575313 TEST4
2484613 TEST5
4681843 TEST6
OBJECTHISTORY table:
IDA2A2: IDA3A5: IDA3B5:
8761649 5455135 6876468
3516463 1546413 6761393
3516464 1546413 6761394
3516465 1546413 6761395
3516466 1546413 6761396
4346796 6754313 3213946
4346797 6754313 3213947
49/8761 3575313 6484313
6846946 2484613 1318433
4646846 4681843 6313873
LIFECYCLEHISTORY table:
IDA2A2: UPDATESTAMPA2
6876468 MAY 8 2009
6761393 SEP 20 2006
6761394 JUN 1 2007
6761395 DEC 2 2005
6761396 DEC 15 2005
3213946 DEC 9 2004
3213947 NOV 2 2003
6484313 OCT 24 2006
1318433 JAN 26 2007
6313873 JAN 17 2005
Code:
select
A4.TITLE "DOC_TITLE" ,
A4.DESCRIPTION "DOC_DESC" ,
OBH.UPDATESTAMPA2 "RELEASE_DATE"
from
wc.WT A4 ,
wc.OBJECTHISTORY OBH
where
A4.ida2a2 = OBH.ida3a5 and
OBH.UPDATESTAMPA2 = (
select max(LCH.UPDATESTAMPA2)
from wc.LIFECYCLEHISTORY LCH
where LCH.ida2a2 = OBH.ida3b5
)
order by
1,3
-
Don't think posted query even parses.
Column UPDATESTAMPA2 selected from ObjectHistory doesn't even exist in that table -that column belongs to LifeCycleHistory table.
If query has to return UPDATSTAMPA2 then LifeCycleHistory table has to be part of the main query.
There is no direct relationship in between WT and LifeCycleHistory tables but over the ObjectHistory bridge then query has to include ObjectHistory table in the main query alongside WT and LifeCycleHistory tables.
Since ObjectHistory is the table that would give you the last incarnation id of the document e.g. max(IDA3B5) then inline view has to go against ObjectHistory table -inline view would single out a unique row to be returned.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I'm a bit confused on this. How would the inline view look then? Shouldn't the MAX() function be used on the OBJECTHISTORY.ida3a5, since it is the column with multiple values?
I want to be able to cross reference that column with OBJECTHISTORY.ida3b5, since ida3b5 is the value that matches with LifecycleHistory. I want to just match 1 OBJECTHISTORY.ida3b5 with LifeCycleHistory.ida2a2...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|