Oracle SQL to create  table view

To fetch distinct row based on the max version of the article from liferay database.

CREATE VIEW LATEST_JOURNALARTICLE
AS
(SELECT JA.GROUPID,
JA.ARTICLEID,
JA.VERSION AS MAX_VERSION,
JA.TITLE,
JA.DISPLAYDATE,
JA.EXPIRATIONDATE
FROM JOURNALARTICLE JA,
(  SELECT GROUPID, ARTICLEID, MAX (VERSION) AS MAX_VERSION
FROM JOURNALARTICLE J
GROUP BY GROUPID, ARTICLEID) JD
WHERE     JA.GROUPID = JD.GROUPID
AND JA.ARTICLEID = JD.ARTICLEID
AND JA.VERSION = JD.MAX_VERSION);

MySQL Query and Rules

A view definition is subject to the following restrictions:

The SELECT statement cannot contain a subquery in the FROM clause.

The SELECT statement cannot refer to system or user variables.

The SELECT statement cannot refer to prepared statement parameters .

CREATE VIEW MAX_VERSION_JOURNALARTICLE
AS
(SELECT GROUPID, ARTICLEID, MAX(VERSION) AS MAX_VERSION
FROM JOURNALARTICLE J
GROUP BY GROUPID, ARTICLEID);
CREATE VIEW LATEST_JOURNALARTICLE
AS
(SELECT JA.GROUPID,
JA.ARTICLEID,
JA.VERSION AS MAX_VERSION,
JA.TITLE,
JA.DISPLAYDATE,
JA.EXPIRATIONDATE
FROM JOURNALARTICLE JA, MAX_VERSION_JOURNALARTICLE JD
WHERE JA.GROUPID = JD.GROUPID AND
JA.ARTICLEID = JD.ARTICLEID AND
JA.VERSION = JD.MAX_VERSION);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: