Просмотр исходного текста страницы SEARCH TREE4.sql

/* Вывод полного дерева категорий всех запчастей */

/* (см. также файл SEARCH_TREE4.xlsx - результат выполнения этого запроса для текущей версии TecDoc) */

/*	STR_LEVEL - Уроверь категории */

/*	STR_TEXT  - Название категории 1 уровня */

/*	STR_ID    - ID категории 1 уровня */

/*	STR_TEXT2 - Название категории 2 уровня */

/*	STR_ID2   - ID категории 2 уровня */

/*	STR_TEXT3 - Название категории 3 уровня */

/*	STR_ID3   - ID категории 3 уровня */

/*	STR_TEXT4 - Название категории 4 уровня */

/*	STR_ID4   - ID категории 4 уровня */

/*	STR_TEXT5 - Название категории 5 уровня */

/*	STR_ID5   - ID категории 5 уровня */

 SET @LNG_ID = 16; /* 1 - Немецкий язык; 16 - Русский язык */

 /*DROP TABLE IF EXISTS tecdoc_2012_4q_temp.SEARCH_TREE4;
  CREATE TABLE tecdoc_2012_4q_temp.SEARCH_TREE4*/
 SELECT      SEARCH_TREE.STR_LEVEL,
 ELT(SEARCH_TREE.STR_LEVEL, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT,
 ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID,
 ELT(SEARCH_TREE.STR_LEVEL-1, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT2,
 ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID2,
 ELT(SEARCH_TREE.STR_LEVEL-2, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT3,
 ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID3,
 ELT(SEARCH_TREE.STR_LEVEL-3, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT4,
 ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID4,
 ELT(SEARCH_TREE.STR_LEVEL-4, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT5,
 ELT(SEARCH_TREE.STR_LEVEL-4, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID5
 FROM                  SEARCH_TREE
 LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = SEARCH_TREE.STR_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNG_ID
 LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
 LEFT JOIN SEARCH_TREE AS SEARCH_TREE2 ON SEARCH_TREE2.STR_ID = SEARCH_TREE.STR_ID_PARENT
 LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = SEARCH_TREE2.STR_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID
 LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
 LEFT JOIN SEARCH_TREE AS SEARCH_TREE3 ON SEARCH_TREE3.STR_ID = SEARCH_TREE2.STR_ID_PARENT
 LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = SEARCH_TREE3.STR_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNG_ID
 LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID
 LEFT JOIN SEARCH_TREE AS SEARCH_TREE4 ON SEARCH_TREE4.STR_ID = SEARCH_TREE3.STR_ID_PARENT
 LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = SEARCH_TREE4.STR_DES_ID AND DESIGNATIONS4.DES_LNG_ID = @LNG_ID
 LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID
 LEFT JOIN SEARCH_TREE AS SEARCH_TREE5 ON SEARCH_TREE5.STR_ID = SEARCH_TREE4.STR_ID_PARENT
 LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = SEARCH_TREE5.STR_DES_ID AND DESIGNATIONS5.DES_LNG_ID = @LNG_ID
 LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS5.DES_TEX_ID
 ORDER BY	STR_TEXT,	STR_TEXT2,	STR_TEXT3,	STR_TEXT4,	STR_TEXT5	;



* Назад к оглавлению: [[Документация_по_запросам_и_структуре_Текдок_-_версия_2]]

* Подробнее о ценах ПО Ирбис на базе текдок от нашей компании: http://www.mstarproject.com/?action=tecdoc_mysql_site