[Gustav]

Цитата:

Сообщение от Deep Dreamer

Имеется в виду нехватает вложенных select'ов, особенно с аггрегатными поименованными полями

Не хватает явно! Увы, много чего не хватает...

Хлебнув чуть больше года назад в Oracle 9.2 SQL-счастье под названием "фраза WITH с подзапросом" (subquery factoring clause), я уже с трудом понимаю, как можно возвращаться в такой каменный век SQL, где этого нет. Чесслово, говорю это не пижонства или красного словца ради. Несмотря на то, что синтаксис ANSI-шный, Microsoft, к сожалению, вроде не торопится прикручивать эту фичу к SQL Server (или может сделали уже?!) А вот обладатели Oracle 9.2 и выше, кто еще не в курсе, рекомендую обратить на этот WITH пристальное внимание.

В качестве примера привожу один свой рабочий запрос. Этот запрос выбирает неамортизируемые карточки основных средств в некоторой модели учета ОС ('GAAP') на определенную дату. Вникать в его бизнес-суть абсолютно необязательно. Это просто иллюстрация того, как подзапросы могут взаимодействовать между собой и как можно эффективно структурировать сложные запросы (практически в этаком "последовательно-процедурном" стиле, невиданном доселе для SELECT'ов):

SELECT * FROM
(
WITH    -- ЗДЕСЬ Я ОПРЕДЕЛЯЮ НЕСКОЛЬКО ИМЕНОВАННЫХ ПОДЗАПРОСОВ (имена "подчеркнуты")
 ------------------ 
 RATrans AS
 ------------------ 
  (SELECT 
   accountnum,
   MIN(CASE WHEN assettranstype = 3 THEN transdate END) acq_transdate,
   SUM(CASE WHEN assettranstype = 0 THEN amountmst ELSE 0 END) amt_0,
   SUM(CASE WHEN assettranstype = 2 THEN amountmst ELSE 0 END) amt_2,
   SUM(CASE WHEN assettranstype = 3 THEN amountmst ELSE 0 END) amt_3,
   SUM(CASE WHEN assettranstype = 4 THEN amountmst ELSE 0 END) amt_4,
   SUM(CASE WHEN assettranstype = 5 THEN amountmst ELSE 0 END) amt_5,
   SUM(CASE WHEN assettranstype = 7 THEN amountmst ELSE 0 END) amt_7,
   SUM(CASE WHEN assettranstype = 15 THEN amountmst ELSE 0 END) amt_15,
   SUM(CASE WHEN assettranstype = 16 THEN amountmst ELSE 0 END) amt_16,
   SUM(CASE WHEN assettranstype NOT IN (0,2,3,4,5,7,15,16) THEN amountmst ELSE 0 END) amt_other
  FROM RASSETTRANS 
  WHERE assetstandardid = 'GAAP'
   AND transdate  <= TO_DATE('30.09.2006', 'DD.MM.YYYY') 
  GROUP BY accountnum
  ),
 ------------------------
 HavingAmort AS
 ------------------------
  (SELECT accountnum 
  FROM RASSETTRANS 
  WHERE assettranstype = 0 
   AND assetstandardid = 'GAAP' 
   AND transdate <= TO_DATE('30.09.2006', 'DD.MM.YYYY')
  ),
 ----------------------------
 NoHavingAmort AS
 ----------------------------
  (SELECT ratable.accountnum AS accountnum
  FROM 
  RASSETTABLE ratable,
  HavingAmort ratrans -- <-- ИСПОЛЬЗУЕМ РАНЕЕ (выше) ОПРЕДЕЛЕННЫЙ ПОДЗАПРОС HavingAmort "как простую таблицу" - неплохо, правда? :-)
  WHERE ratable.accountnum = ratrans.accountnum(+)
   AND ratrans.accountnum IS NULL
  ),
 -------------------------------
 NecessaryAssets AS
 -------------------------------
  -- карточки, не имеющие амортизации в западном учете  (когда либо до даты)
  (SELECT accountnum FROM NoHavingAmort -- -- ОПЯТЬ ИСПОЛЬЗУЕМ РАНЕЕ (выше) ОПРЕДЕЛЕННЫЙ ПОДЗАПРОС NoHavingAmort "как простую таблицу", который в свою очередь - см. выше тоже включает подзапрос HavingAmort, т.е. уже пошла вложенность, сносящая башню при неиспользовании фразы WITH :-)
  UNION
  -- карточки, у которых нет амортизации (отключена)
  SELECT assetid FROM RASSETSTANDARDS WHERE depreciation = 0 AND assetstandardid = 'GAAP'
  UNION
  -- карточки, имеющие ост. стоимость <= 0, но не выбывшие ----------------------------------------------
  SELECT accountnum FROM
  (
   SELECT
   accountnum,
   (amt_2 + amt_3 + amt_4 + amt_16) AS PS,
   -(amt_0) AS Amort
   FROM RATrans  
   WHERE ROUND(amt_5,2) = 0 AND ROUND(amt_7,2) = 0
  ) 
  WHERE ROUND(PS,2) <= ROUND(Amort,2)
  )
--
-- СОБСТВЕННО НАЧИНАЕТСЯ ОСНОВНОЙ СЕЛЕКТ (самый внешний "SELECT * FROM ( WITH ...)" основным не считаем - это обертка для TOAD )
SELECT
 ratable.accountnum AS "Asset Code",
 ratable.dataareaid AS "Company",
 ratable.status AS "Status Value", 
 en.en_label AS "Status Label", 
 stand.depreciation AS "Depr Turn ON",
 neverb4.never_b4 AS "Never Before",
-- 
 CASE WHEN ratable.status <> 7 
  THEN NVL((trans.amt_2 + trans.amt_3 + trans.amt_4 + trans.amt_16), 0) 
  ELSE NVL((trans.amt_2 + trans.amt_3 + trans.amt_4 + trans.amt_16), 0) + NVL(stand.acquisitionprice, 0) 
  END AS "IC",
--
 -NVL(trans.amt_0, 0) AS "Depr",
--
 CASE WHEN ratable.status <> 7 
  THEN NVL((trans.amt_2 + trans.amt_3 + trans.amt_4 + trans.amt_16)+ (trans.amt_0), 0) 
  ELSE NVL((trans.amt_2 + trans.amt_3 + trans.amt_4 + trans.amt_16)+ (trans.amt_0), 0) + NVL(stand.acquisitionprice, 0) 
  END AS "Net",
--
 trans. acq_transdate AS "Acq.Date (GAAP, RATrans)",
 ratable.acquisitiondate AS "Acq.Date (RATable)",
 ratable.acquisitionprice AS "Acq.Price (RATable)",
 stand.acquisitionprice AS "Acq.Price (RAStand)",
 trans.amt_0 AS "Amt_0_Depreciation", 
 trans.amt_2 AS "Amt_2_Revaluation", 
 trans.amt_3 AS "Amt_3_Acquisition", 
 trans.amt_4 AS "Amt_4_AcquisitionAdj", 
 trans.amt_5 AS "Amt_5_DisposalSale", 
 trans.amt_7 AS "Amt_7_Disposal", 
 trans.amt_15 AS "Amt_15_Investment", 
 trans.amt_16 AS "Amt_16_InvestmentOpen", 
 amt_other AS "Amt_Other_AssetTransTypes"
-- 
FROM
-- 
 RATrans trans, -- <-- ИСПОЛЬЗУЕМ ИМЕННОВАННЫЙ ПОДЗАПРОС из зоны WITH
-- 
 (SELECT assetid, acquisitionprice, depreciation 
 FROM RASSETSTANDARDS 
 WHERE assetstandardid = 'GAAP'
 ) stand,
-- 
 -- ДЛЯ ИНДИКАЦИИ: карточки, не имеющие амортизации в западном учете  (когда либо до даты)
 (SELECT accountnum, 'Never' AS never_b4 
 FROM NoHavingAmort -- <-- ИСПОЛЬЗУЕМ ИМЕННОВАННЫЙ ПОДЗАПРОС из зоны WITH
 ) neverb4, 
-- 
 -- расшифровка значений статуса 
 (SELECT * FROM AX_BASE_ENUMS WHERE en_type = 'RAssetStatus') en, 
-- 
 (SELECT accountnum, dataareaid, status, acquisitiondate, acquisitionprice 
 FROM RASSETTABLE 
 ) ratable,
-- 
 NecessaryAssets nassets -- <-- ИСПОЛЬЗУЕМ ИМЕННОВАННЫЙ ПОДЗАПРОС из зоны WITH
-- 
WHERE nassets.accountnum = ratable.accountnum -- здесь без (+) !!!
 AND ratable.status = en.en_value(+)
 AND ratable.accountnum = neverb4.accountnum(+)
 AND ratable.accountnum = stand.assetid(+)  
 AND ratable.accountnum = trans.accountnum(+)
)
ORDER BY 1

Запрос можно попробовать запустить в вашей системе, заменив модель учета 'GAAP' на какую-нибудь модель учета ОС из вашей системы. Также необходимо закомментировать фрагменты, ссылающиеся на мою специфическую таблицу AX_BASE_ENUMS. Ну, или предварительно создать ее в схеме вашей Аксапты (наполнять данными не обязательно - RassetTable вяжется на нее левым джойном):

CREATE TABLE AX_BASE_ENUMS
(
  EN_TYPE     VARCHAR2(100 BYTE)                NOT NULL,
  EN_VALUE    NUMBER(10)                        NOT NULL,
  EN_LABEL    VARCHAR2(200 BYTE)                NOT NULL,
  EN_ELEMENT  VARCHAR2(100 BYTE)
)