If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I want to find a way to display a column from LifeCycleHistory, that matches the the column WT.Document.ida2a2. The catch here is... ObjectHistory.ida3a5 has a many to 1 relationship with WTDocument.ida2a2.

So, when I try to do joins, LifeCycleHistory.ida2a2 comes up with several values, depending on how many matches WTDocument.ida2a2==ObjectHistory.ida3a5 results.What I want to do, is obtain one-to-one relationship between WTDocument.ida2a2 and LifeCycleHistory.ida2a2.

What I was thinking was to obtain the maximum value of ObjectHistory.ida3b5 for each ObjectHistory.ida3a5. Then matching that value with LifeCycleHistory.ida2a2. But, I'm not really sure how...

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.

Business Spec

The business wants to know the most recent "release date" of a document in the database.

So, there are three tables that is being used...
LifeCycleHistory (which has the date at which documents are created),
WTDocument (which has the name and location of documents),
ObjectHistory (which is used as a lookup table to match documents to their respective release dates).

This is how the application was set-up and there are alot more to it, but all I am concerned with is the doc name and its release date.

I want to create a view that lists the name of the document along with its release date. The problem is... ObjectHistory has a one to many relationship to WTDocument and a one to one relationship to LifeCycleHistory.

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.

Tried MAX function

I've reviewed over the mac function and looking to try to use this in combination with an inline view.

Basically, I want to be able to pull just a single date for each unique WT.ida2a2. There are several tables that I need to combine and apply filters for. I want to show the document title, name, and latest release date. Each time a document is released, an entry is created in the LIFECYCLEHISTORY table, only way of know what the release date of the document is, is to match the LIFECYCLEHISTORY.ida2a2 with OBJECTHISTORY.ida3b5.. then match the OBJECTHISTORY.ida3a5 with the WT.ida2a2.

Here is what I have, I know there is something wrong... just can't figure it out:

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 am still pulling multiple results for a single document title. I think there should be a "group by" somewhere in the inner select statement. I've tried "group by x.column-a, but results were the same, still multiple documents with multiple release dates.

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.

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.