Могут ли вложенные подзапросы sql быть доступны другим подзапросам в том же операторе select ..?

Это для сервера IBM AS400 DB2 SQL vr61m0, доступ к которому осуществляется через клиент SQL DBeaver. У меня есть самый сложный запрос, который я когда-либо создавал, вручную написав любой SQL, и некоторые его части имеют проблему с дублированием. В частности, есть подзапрос, который используется несколько раз по-разному, и я надеюсь уменьшить это дублирование, чтобы оно появлялось только один раз.

Я не уверен, какую терминологию использовать для этого, поэтому поиск в Google был затруднен. Я пробовал такие биты, как «подзапрос», «производная таблица» и т. д., Но не повезло. Я получаю слишком много несвязанных результатов поиска. Мне кажется очевидным, что эта проблема связана с областью действия: различные среды подзапросов не могут видеть друг друга.

Кстати ... Если SQL-запрос имеет подзапросы в предложении FROM, и один из них используется несколько раз, но в некоторых случаях он вложен в более глубокие подзапросы для применения агрегатных функций, могут ли более глубокие подзапросы относиться к более мелким чтобы избежать дублирования .. ??

Вот созданный мной рабочий пример с рабочими данными. Это очень сжатый пример, основанный на реальном запросе и данных, с которыми я работаю. Дублированные подзапросы отмечаются в SQL с комментариями:

Таблица данных ORDERS:

ORDDATE  ORDNUM ORDACCT  ORDLOAD 
20180901 1      ABC99    101     
20180901 2      XYZ00    102     
20180901 3      ZZZ12    103     

Таблица данных LOADS:

LOADDATE LOADNUM LOADDRIV LOADHLP1 LOADHLP2 RATEDRIV RATEHLP1 RATEHLP2 
20180901 101     57                         1                          
20180901 102     60       71                1        2                 
20180901 103     58       81       85       1        3        3        

Этот SQL работает, но указанный подзапрос дублируется дважды:

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

                                      -- THE SUBQUERY BELOW IS THE FIRST DUPLICATION
INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                                      -- THE SUBQUERY BELOW IS THE SECOND DUPLICATION
                FROM        (         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
                            ) AS LOADEMPS
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Здесь делается попытка обратиться к первому подзапросу позже в основном запросе, но это не работает. Выдает ошибку: «Ошибка SQL [42704]: [SQL0204] LOADEMPS в типе CERTODB * ФАЙЛ не найден». Как указано выше, похоже, это проблема области действия: среды подзапросов не могут видеть друг друга.

Есть ли способ заставить эту работу или что-то подобное, все в одном SQL-запросе, чтобы не было дублирования ..? Например, никаких дополнительных представлений, хранимых процедур или других объектов, но все это делается одним большим оператором SQL ..?

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Никогда не используйте повторно псевдонимы таблиц, это слишком сбивает с толку. Возможно, вы ищете cte (общее табличное выражение), то есть предложение WITH.

jarlh 13.09.2018 19:22

Я все время использую DB2 9.5 и 10.5, и у них есть общие табличные выражения (CTE). Они позволяют «предварительно вычислить» один или несколько запросов, а затем использовать их / их в следующих в том же операторе SELECT. Их гораздо проще написать и отладить то, что вы пытаетесь написать.

The Impaler 13.09.2018 19:24

Используйте CTE, чтобы решить эту проблему

Amazigh.Ca 13.09.2018 19:24

Бинго .. !! Именно так. Спасибо всем, я только что вшил это в свой SQL, и он уже работает. ibm.com/support/knowledgecenter/ssw_ibm_i_61/sqlp/…

spinjector 13.09.2018 19:46

Кроме того, пункт, который опущен в приведенной выше ссылке IBM, но упоминается здесь ... modern-sql.com/feature/with ..., предложение WITH может содержать несколько CTE, разделенных запятыми. Итак, я переместил все подзапросы (их было больше) из предложения FROM в предложение WITH. Все работает, и пункт FROM теперь намного удобнее читать. <3

spinjector 13.09.2018 20:06
1
5
587
3

Ответы 3

Основываясь на комментариях в ответ на мой OP, кажется, мне нужно общее табличное выражение (CTE), также известное как предложение WITH: https://modern-sql.com/feature/with

Ниже представлен переработанный SQL с желаемым удалением дублирования:

WITH LOADEMPS AS    (         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                        UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                        UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
                    )

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS  AS ORDERS
INNER JOIN  LOADEMPS        ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Я считаю, что ключевое слово LATERAL поможет

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  LATERAL (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Но, как упоминалось в комментариях, я бы использовал пару CTE

with loademps as (SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            )
, loadtypes as (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            )
SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE
FROM        CERTODB.ORDERS AS ORDERS
INNER JOIN  LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM
ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Да, я просто отправлял следующий комментарий в ответ другим на моем OP об этом до того, как появился ваш ответ. Я уже переместил все подзапросы из предложения FROM в предложение WITH. Теперь все работает, стало компактнее и удобнее для глаз.

spinjector 13.09.2018 20:10

Для процветания вы также можете загрузить их во временные глобальные таблицы.

Преимущество использования глобальной временной таблицы по сравнению с CTE или традиционной временной таблицей состоит в том, что глобальная таблица может быть инициализирована в хранимой процедуре, а затем на нее можно ссылаться в другой хранимой процедуре, вложенной во внешнюю.

Он также позволяет использовать сверхбыстрый и свободный синтаксис SELECT ... INTO без необходимости заранее определять таблицу.

SELECT * 
INTO #Global_Temp 
FROM (
SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
);

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

                                      -- THE SUBQUERY BELOW IS THE FIRST DUPLICATION
INNER JOIN  (                       SELECT LOADDATE, LOADROLE, EMPID, RATE FROM #Global_Temp 
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                                      -- THE SUBQUERY BELOW IS THE SECOND DUPLICATION
                FROM        (       SELECT LOADDATE, LOADROLE, EMPID, RATE FROM #Global_Temp 
                            ) AS LOADEMPS
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM
ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE;

DROP TABLE #Global_Temp;

Единственный раз, когда следует рассматривать глобальные временные таблицы вместо CTE, - это если у вас есть несколько операторов, которым потребуются одни и те же данные. А CTE намного меньше накладных расходов. Кроме того, если CTE материализуется системой, он может быть разделен между заданиями. Глобальная временная таблица не может использоваться совместно, она глобальна только для задания.

Charles 13.09.2018 22:18

Другие вопросы по теме