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