Creating table view in mysql and oracle database server

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