All geek questions in one place

MySQL - creating a view using a subquery in a FROM clause

I want to create a view in MySQL using a subquery

CREATE OR REPLACE VIEW `V_TASK_TRANSFER` (`REF_ID`, `DATE_CREATE`, `DATE_TRX`, `ACCOUNT_NO`, `TO_ACCOUNT_NO`, `TO_NAME`, `CURRENCY_CODE`, `AMOUNT`, `TASK_TYPE`, `NAME_E`, `NAME_I`, `REF_NO`, `EXECUTION_TYPE`, `REVIEW_COUNT`, `REVIEW_NEED`, `APPROVE_COUNT`, `APPROVE_NEED`, `TRX_COUNT_SUCCESS`, `TRX_COUNT_FAIL`, `TRX_COUNT_ALL`, `STATUS_TF`, `USER_ID` ) AS SELECT REF_ID, DATE_CREATE, DATE_TRX, ACCOUNT_NO, TO_ACCOUNT_NO, TO_NAME, CURRENCY_CODE, AMOUNT, TASK_TYPE, NAME_E, NAME_I, REF_NO, EXECUTION_TYPE, REVIEW_COUNT, REVIEW_NEED, APPROVE_COUNT, APPROVE_NEED, TRX_COUNT_SUCCESS, TRX_COUNT_FAIL, TRX_COUNT_ALL, STATUS_TF, USER_ID FROM (SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE UNION SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS_DOM TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE UNION SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS_FGN TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE );

but he gave me

Mistake # 1349 - SELECT View contains subquery in FROM clause

How can I get around this limitation?

+4
source share
2 answers

Try to do this without a subquery -

 CREATE OR REPLACE VIEW V_TASK_TRANSFER AS SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE UNION SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS_DOM TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE UNION SELECT T.REF_ID, T.DATE_CREATE, T.DATE_TRX, TTF.ACCOUNT_NO, TTF.TO_ACCOUNT_NO, TTF.TO_NAME, TTF.CURRENCY_CODE, TTF.AMOUNT, T.TASK_TYPE, TT.NAME_E, TT.NAME_I, T.REF_NO, T.EXECUTION_TYPE, T.REVIEW_COUNT, T.REVIEW_NEED, T.APPROVE_COUNT, T.APPROVE_NEED, T.TRX_COUNT_SUCCESS, T.TRX_COUNT_FAIL, T.TRX_COUNT_ALL, T.STATUS_TF, T.USER_ID FROM IB_TASKS T JOIN IB_TASK_TRANSFERS_FGN TTF ON T.REF_ID = TTF.REF_ID JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE;
+3
source

According to the documentation:

MySQL Docs

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

A workaround is to create a view for each of your subqueries.

Then access these subquery views in your final view.

See the solution for it

+5
source

Source: https://habr.com/ru/post/1438614/

More articles:


All Articles