CODEWITHSUNDEEP
sqlmariadb
r34627673
r34627673

Reputation: 313

Why can't I create view table in SQL?

I have query like this:

SELECT 
       generatedTime AS generatedTime,
       tempTable AS tempTable,
       CASE
           WHEN ((generatedTime < maxPelamarmhsdoktor) or (generatedTime < maxAkdmstmayor)
                    or (generatedTime < maxIpbmstdepartemen) or (generatedTime < maxIpbmstfakultas)
                        or (generatedTime < maxIpbrefjeniskelamin) or (generatedTime < maxJenisinstansi)
                            or (generatedTime < maxJenisinstitusi)) THEN
               'Yes'
           ELSE
               'No'
       END AS NeedsToUpdateorNot
FROM generatedtime
JOIN ( SELECT 
              MAX( last_update ) AS maxPelamarmhsdoktor
       FROM pelamarmhsdoktor

     ) AS maxPelamarmhsdoktorFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxAkdmstmayor
       FROM akdmst_mayor

     ) AS maxAkdmstmayorFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxIpbmstdepartemen
       FROM ipbmst_departemen

     ) AS maxIpbmstdepartemenFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxIpbmstfakultas
       FROM ipbmst_fakultas

     ) AS maxIpbmstfakultasFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxIpbrefjeniskelamin
       FROM ipbref_jeniskelamin

     ) AS maxIpbrefjeniskelaminFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxJenisinstansi
       FROM jenisinstansi

     ) AS maxJenisinstansiFinder
JOIN ( SELECT 
              MAX( last_update ) AS maxJenisinstitusi
       FROM jenisinstitusi

     ) AS maxJenisinstitusiFinder

Then I want to make that query to be a view table. So I added 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `updateforpelamarmagisterrinci`  AS

at the beginning.

But When I tried to create view, the error comes, and the error message is like this

#1349 - View's SELECT contains a subquery in the FROM clause

What may I do to solve that? Thanks in advance

Upvotes: 1

Views: 1832

Answers (4)

henry
henry

Reputation: 27

Move your sub query to view:

CREATE VIEW v_max_pelamarmhsdoktor AS 
    SELECT MAX( last_update ) AS maxPelamarmhsdoktor FROM pelamarmhsdoktor
...

...

your query become:

SELECT 
       generatedTime AS generatedTime,
       tempTable AS tempTable,
       CASE
           WHEN ((generatedTime < maxPelamarmhsdoktor) or (generatedTime < maxAkdmstmayor)
                    or (generatedTime < maxIpbmstdepartemen) or (generatedTime < maxIpbmstfakultas)
                        or (generatedTime < maxIpbrefjeniskelamin) or (generatedTime < maxJenisinstansi)
                            or (generatedTime < maxJenisinstitusi)) THEN
               'Yes'
           ELSE
               'No'
       END AS NeedsToUpdateorNot
FROM generatedtime
JOIN v_max_pelamarmhsdoktor AS maxPelamarmhsdoktorFinder
...

...

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30829

As per MySQL's documentation, you can't have sub queries in FROM clause while creating a VIEW, hence the error. However, in your case, you can change the query and try selecting those values in SELECT clause, e.g.

SELECT 
       (SELECT MAX( last_update ) FROM pelamarmhsdoktor) AS maxPelamarmhsdoktorFinder,
       (SELECT MAX( last_update ) FROM akdmst_mayor) AS maxAkdmstmayorFinder,
       (SELECT MAX( last_update ) FROM ipbmst_departemen) AS maxIpbmstdepartemenFinder,
       (SELECT MAX( last_update ) FROM ipbmst_fakultas) AS maxIpbmstfakultasFinder,
       (SELECT MAX( last_update ) FROM ipbref_jeniskelamin) AS maxJeniskelamin,
       generatedTime AS generatedTime,
       tempTable AS tempTable,
       CASE
           WHEN ((generatedTime < maxPelamarmhsdoktor) or (generatedTime < maxAkdmstmayor)
                    or (generatedTime < maxIpbmstdepartemen) or (generatedTime < maxIpbmstfakultas)
                        or (generatedTime < maxIpbrefjeniskelamin) or (generatedTime < maxJenisinstansi)
                            or (generatedTime < maxJenisinstitusi)) THEN
               'Yes'
           ELSE
               'No'
       END AS NeedsToUpdateorNot
FROM generatedtime;

Upvotes: 1

elenst
elenst

Reputation: 3987

Since you are using MariaDB 10.1, you might want to consider upgrading to MariaDB 10.2. The limitation on using subqueries in views was removed in 10.2.1. As of now (May 13th, 2017), the latest available 10.2 version is 10.2.5-rc.

The next 10.2.6 is going to be GA (a.k.a. stable release), it is coming soon. Meanwhile, you will have time to test whether 10.2 functionality works well for you, that's what RC is for.

Note: If you decide to try out the upgrade, it's highly recommended to create a backup of your database before doing so. In case you don't like the version on whatever reason and want to downgrade, using the backup might save you the effort.

Upvotes: 2

maraca
maraca

Reputation: 8743

Here is a solution, well structured, but some work:

  1. Create a view for each table where you need time columns. All you do in this view is select the MAX(last_update).

  2. Create a view where you UNION all the views created in step 1 and select the MAX(last_update) again, I call the view vAll for simplicity.

  3. Now you can easily create the view you want to:

    SELECT
   generatedTime AS generatedTime,
   tempTable AS tempTable,
   CASE WHEN generated_time < last_update THEN 'Yes' ELSE 'No' END AS NeedsToUpdateorNot
FROM generatedtime, vAll;

Alternatively try to do all in the select clause:

SELECT
   generatedTime AS generatedTime,
   tempTable AS tempTable,
   CASE WHEN generated_time < (
      SELECT MAX(last_update) AS last_update FROM (
          (SELECT MAX(last_update) AS last_update FROM a) UNION
          (SELECT MAX(last_update) AS last_update FROM b) UNION
          (SELECT MAX(last_update) AS last_update FROM c) UNION
          ...
      )
   ) THEN 'Yes' ELSE 'No' END AS NeedsToUpdateorNot
FROM generatedtime;

Upvotes: 1

Related Questions