Tecdoc 2018 q1 multi

Материал из AutoDevel
Перейти к: навигация, поиск

Для фильтрации текстовой информации на различных языках, можно использовать индекс из таблицы LANGUAGES.LNG_ID.

Для фильтрации объектов по странам, можно использовать индекс из таблицы COUNTRIES.COU_ID.

Во всех примерах представленных здесь, значения переменных @COUNTRY_FILTER и @LNGID имеет следующие значения: 

SET @COUNTRY_FILTER = 54; -- Германия
SET @LNGID=16; -- русский

Содержание

Выбор списка производителей для пассажирских авто

   SELECT 
         MFA_BRAND
   FROM 
         MANUFACTURERS
         INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = MANUFACTURERS.MFA_PC_CTM
   WHERE 
         MANUFACTURERS.MFA_PC = 1 AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
   ORDER BY MFA_BRAND;

Выбор списка модельных рядов для производителя пассажирских авто

   SET @MFAID = 35; -- FIAT
   SELECT DISTINCT
         TEXT_DESIGNATIONS.DES_TEXT
   FROM 
         MODELS_SERIES
         INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = MODELS_SERIES.MS_PC_CTM
 
         INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
             AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
             AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
         INNER JOIN TEXT_DESIGNATIONS ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = TEXT_DESIGNATIONS.DES_ID 
             AND TEXT_DESIGNATIONS.DES_LNG_ID = @LNGID
   WHERE 
         MODELS_SERIES.MS_MFA_ID = @MFAID  
             AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
             AND MODELS_SERIES.MS_PC = 1

Выбор списка моделей пассажирских авто для модельного ряда

   SET @MSID = 348; -- FIAT ARGENTA (132_)
 
   SELECT
         CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
         PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
         PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
         PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TAX,
         PC_BODY_TYPE.DES_TEXT
   FROM 
         PASSENGER_CARS
         INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = PASSENGER_CARS.PC_CTM
 
         INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
               AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
 
         INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = @MSID
 
         INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
 
         INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
               AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
               AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
               AND MS_NAME.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
               AND PC_MODEL.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
               AND PC_BODY_TYPE.DES_LNG_ID = @LNGID   
   WHERE 
         PASSENGER_CARS.PC_MS_ID = @MSID  
         AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
         AND MODELS_SERIES.MS_PC = 1

Выбор подробной информации о пассажирском авто

   SET @PCID = 1014; -- FORD TRANSIT Bus (V_ _) 2.0 (VAS, VIL, VLS, VUL, VZS)
 
   SELECT
         CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
         PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
         PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
         PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TAX,
         PC_COUNTRY_SPECIFICS.PCS_CAPACITY_LT,
         PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TECH,
         PC_COUNTRY_SPECIFICS.PCS_ABS,
         PC_COUNTRY_SPECIFICS.PCS_ASR,
         PC_COUNTRY_SPECIFICS.PCS_NUMBER_OF_CYLINDERS,
 
         PC_BODY_TYPE.DES_TEXT AS PC_BODY_TYPE,
         PC_ENGINE_TYPE.DES_TEXT AS PC_ENGINE_TYPE,
         PC_GEAR_TYPE.DES_TEXT AS PC_GEAR_TYPE,
         PC_DRIVE_TYPE.DES_TEXT AS PC_DRIVE_TYPE,
         PC_BRAKE_SYSTEM.DES_TEXT AS PC_BRAKE_SYSTEM,
         PC_BRAKE_TYPE.DES_TEXT AS PC_BRAKE_TYPE,
         PC_FUEL_TYPE.DES_TEXT AS PC_FUEL_TYPE,
         PC_CATALYSATOR_TYPE.DES_TEXT AS PC_CATALYSATOR_TYPE,
         PC_FUEL_MIXTURE.DES_TEXT AS PC_FUEL_MIXTURE,
 
         (SELECT
                GROUP_CONCAT(ENGINES.ENG_CODE)
          FROM
                ENGINES
                JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
          WHERE
                ENG_DESIGNATIONS.PC_ID = PASSENGER_CARS.PC_ID) AS PC_ENG_CODES
 
   FROM 
         PASSENGER_CARS
 
         INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
              AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
 
         INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = PASSENGER_CARS.PC_MS_ID
 
         INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
 
         INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
              AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
              AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
              AND MS_NAME.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
              AND PC_MODEL.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
              AND PC_BODY_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_ENGINE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_ENGINE_TYPE = PC_ENGINE_TYPE.DES_ID 
              AND PC_ENGINE_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_GEAR_TYPE ON PC_COUNTRY_SPECIFICS.PCS_GEAR_TYPE = PC_GEAR_TYPE.DES_ID 
              AND PC_GEAR_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_DRIVE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_DRIVE_TYPE = PC_DRIVE_TYPE.DES_ID 
              AND PC_DRIVE_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BRAKE_SYSTEM ON PC_COUNTRY_SPECIFICS.PCS_BRAKE_SYSTEM = PC_BRAKE_SYSTEM.DES_ID 
              AND PC_BRAKE_SYSTEM.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BRAKE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BRAKE_TYPE = PC_BRAKE_TYPE.DES_ID 
              AND PC_BRAKE_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_FUEL_TYPE ON PC_COUNTRY_SPECIFICS.PCS_FUEL_TYPE = PC_FUEL_TYPE.DES_ID 
              AND PC_FUEL_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_CATALYSATOR_TYPE 
              ON PC_COUNTRY_SPECIFICS.PCS_CATALYSATOR_TYPE = PC_CATALYSATOR_TYPE.DES_ID 
              AND PC_CATALYSATOR_TYPE.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_FUEL_MIXTURE ON PC_COUNTRY_SPECIFICS.PCS_FUEL_MIXTURE = PC_FUEL_MIXTURE.DES_ID 
              AND PC_FUEL_MIXTURE.DES_LNG_ID = @LNGID
 
   WHERE 
         PASSENGER_CARS.PC_ID = @PCID

Выбор подробной информации о коммерческом авто

    SET @CV_ID=17582; -- DAF CF FA 290
 
    SELECT DISTINCT
           CV_ID AS TECDOC_TYPE_NO,
           CONCAT( MFA_BRAND, ' ', TEXT_DESIGNATIONS2.DES_TEXT, ' ', TEXT_DESIGNATIONS1.DES_TEXT) AS TYPEL,
           CV_COUNTRY_SPECIFICS.CCS_CONSTRUCTION_INTERVAL_START,
           CV_COUNTRY_SPECIFICS.CCS_CONSTRUCTION_INTERVAL_END,
           CV_COUNTRY_SPECIFICS.CCS_POWER_PS_START,
           CV_COUNTRY_SPECIFICS.CCS_POWER_PS_UPTO,
           CV_COUNTRY_SPECIFICS.CCS_POWER_KW_START,
           CV_COUNTRY_SPECIFICS.CCS_POWER_KW_UPTO,
           CV_COUNTRY_SPECIFICS.CCS_CAPACITY_TECH,
           CV_COUNTRY_SPECIFICS.CCS_TONNAGE,
 
           CV_PLATFORM_TYPE.DES_TEXT AS CV_PLATFORM_TYPE,
           CV_ENGINE_TYPE.DES_TEXT AS CV_ENGINE_TYPE,
           CV_AXLE_CONFIGURATION.DES_TEXT AS CV_AXLE_CONFIGURATION,
-- ---------------------------------------------------------------    
           (SELECT
                  GROUP_CONCAT( CONCAT(  WHEELBASES.AXLE_GROUP, '/',  WHEELBASES.WHELL_BASE, ' mm' ) )
            FROM
                  WHEELBASES
            WHERE
                  WHEELBASES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_WHEELBASES,
-- ---------------------------------------------------------------
            (SELECT
                  GROUP_CONCAT( CONCAT( AXLE_POS_TEXT.DES_TEXT, '/',  SUSPENSION_TEXT.DES_TEXT) )
             FROM
                  CV_SUSPENSIONS
                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS AXLE_POS_TEXT 
			       ON CV_SUSPENSIONS.AXLE_POS_DES = AXLE_POS_TEXT.DES_ID AND AXLE_POS_TEXT.DES_LNG_ID = @LNGID
 
                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS SUSPENSION_TEXT 
			       ON  CV_SUSPENSIONS.SUSPENSION_DES = SUSPENSION_TEXT.DES_ID AND SUSPENSION_TEXT.DES_LNG_ID = @LNGID
             WHERE
                  CV_SUSPENSIONS.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_SUSPENSIONS,
-- ---------------------------------------------------------------
            (SELECT
                  GROUP_CONCAT( DC_MODEL_TEXT.DES_TEXT )
             FROM
                  DRIVERS_CABS
                  LEFT OUTER JOIN DRIVERS_CABS_DES ON DRIVERS_CABS_DES.DC_ID = DRIVERS_CABS.DC_ID
 
                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS DC_MODEL_TEXT ON 
                  DRIVERS_CABS.DC_MODEL_DES = DC_MODEL_TEXT.DES_ID AND DC_MODEL_TEXT.DES_LNG_ID = @LNGID
             WHERE
                  DRIVERS_CABS_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as DRIVERS_CABS,
-- ---------------------------------------------------------------
            (SELECT
                  GROUP_CONCAT( AXLES.AXL_DESCRIPTION )
             FROM
                  AXLES
                  LEFT OUTER JOIN CV_AXLES_DES ON CV_AXLES_DES.AXL_ID = AXLES.AXL_ID
             WHERE
                  CV_AXLES_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as AXLES,
-- ---------------------------------------------------------------
            (SELECT
                  GROUP_CONCAT( CV_MANUFACTURER_IDS.CV_MANUF_NUM )
             FROM
                  CV_MANUFACTURER_IDS
                  LEFT OUTER JOIN CV_MANUF_IDS_DES ON CV_MANUF_IDS_DES.CV_MANUF_ID = CV_MANUFACTURER_IDS.CV_MANUF_ID
             WHERE
                  CV_MANUF_IDS_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_MANUF_CODES,
-- ---------------------------------------------------------------
            (SELECT
                  GROUP_CONCAT(ENGINES.ENG_CODE)
             FROM
                  ENGINES
                  JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
             WHERE
                  ENG_DESIGNATIONS.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_ENG_CODES
    FROM
        COMMERCIAL_VEHICLES
 
        INNER JOIN CV_COUNTRY_SPECIFICS ON CV_COUNTRY_SPECIFICS.CCS_CV_ID = COMMERCIAL_VEHICLES.CV_ID
             AND (CV_COUNTRY_SPECIFICS.CCS_COU_ID = @COUNTRY_FILTER OR CV_COUNTRY_SPECIFICS.CCS_COU_ID = 255)
 
        INNER JOIN MODELS_SERIES ON COMMERCIAL_VEHICLES.CV_MS_ID = MODELS_SERIES.MS_ID
 
        INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
             AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
             AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
        INNER JOIN MANUFACTURERS ON COMMERCIAL_VEHICLES.CV_MFA_ID = MANUFACTURERS.MFA_ID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS TEXT_DESIGNATIONS1 ON 
             COMMERCIAL_VEHICLES.CV_MODEL_DES = TEXT_DESIGNATIONS1.DES_ID AND TEXT_DESIGNATIONS1.DES_LNG_ID = @LNGID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS TEXT_DESIGNATIONS2 ON 
             MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = TEXT_DESIGNATIONS2.DES_ID AND TEXT_DESIGNATIONS2.DES_LNG_ID = @LNGID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_PLATFORM_TYPE ON 
             CV_COUNTRY_SPECIFICS.CCS_PLATFORM_TYPE = CV_PLATFORM_TYPE.DES_ID AND CV_PLATFORM_TYPE.DES_LNG_ID = @LNGID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_ENGINE_TYPE 
             ON CV_COUNTRY_SPECIFICS.CCS_ENGINE_TYPE = CV_ENGINE_TYPE.DES_ID 
             AND CV_ENGINE_TYPE.DES_LNG_ID = @LNGID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_AXLE_CONFIGURATION 
             ON CV_COUNTRY_SPECIFICS.CCS_AXLE_CONFIGURATION = CV_AXLE_CONFIGURATION.DES_ID 
             AND CV_AXLE_CONFIGURATION.DES_LNG_ID = @LNGID
    WHERE
        COMMERCIAL_VEHICLES.CV_ID = @CV_ID

Выбор информации об артикуле

    SET @ART_ID = 319888830; -- REMY 19025100
 
    SELECT
          ARTICLES.ART_ID,
          ARTICLES.ART_ARTICLE_NR,
          ART_COUNTRY_SPECIFICS.ACS_PACK_UNIT,
          ART_COUNTRY_SPECIFICS.ACS_QUANTITY_PER_UNIT,
          ART_COUNTRY_SPECIFICS.ACS_STATUS_DATE,
          ART_STATUS_TEXT.DES_TEXT AS ART_STATUS_TEXT,
          SUPPLIERS.SUP_BRAND,
-- ---------------------------------------------------------------        
          CONCAT_WS(' ',
                    (SELECT
                           GROUP_CONCAT( PT_DES_TEXT.DES_TEXT SEPARATOR ',' )
                     FROM
                           PRODUCTS
                           INNER JOIN ART_PRODUCTS_DES ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
 
                           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT 
                                ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID 
                                AND PT_DES_TEXT.DES_LNG_ID = @LNGID
                     WHERE
                           ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID), ART_NAME.DES_TEXT) AS ART_PRODUCT_NAME,
-- ---------------------------------------------------------------                  
          (SELECT 
                 CONCAT_WS(0x0a0d2d, ART_INFO_TITLE.DES_TEXT, GROUP_CONCAT(TEXT_MODULE_TEXTS.TMT_TEXT SEPARATOR 0x0a0d2d ) ) 
           FROM
                 ART_INFO
                 LEFT OUTER JOIN TEXT_MODULES ON ART_INFO.AIN_TMO_ID = TEXT_MODULES.TMO_ID
 
                 LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KV = TEXT_MODULES.TMO_KV_KV 
                      AND KEY_VALUES.KV_KT_ID = TEXT_MODULES.TMO_KV_KT_ID
 
                 LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_INFO_TITLE ON ART_INFO_TITLE.DES_ID = KEY_VALUES.KV_DES_ID
                      AND ART_INFO_TITLE.DES_LNG_ID = @LNGID
 
                 LEFT OUTER JOIN TEXT_MODULE_TEXTS ON TEXT_MODULE_TEXTS.TMT_ID = TEXT_MODULES.TMO_TMT_ID
                      AND (TEXT_MODULE_TEXTS.TMT_LNG_ID = 255 OR TEXT_MODULE_TEXTS.TMT_LNG_ID = @LNGID)
           WHERE
                 ART_INFO.AIN_ART_ID = ARTICLES.ART_ID) AS ART_INFO,
-- ---------------------------------------------------------------            
          (SELECT
                 GROUP_CONCAT( ART_NEW_LOOKUP.ART_ARTICLE_NR )
           FROM
                 ART_NEW_NBR
                 LEFT OUTER JOIN ARTICLES AS ART_NEW_LOOKUP ON 
                   ART_NEW_NBR.ART_NEW_ID = ART_NEW_LOOKUP.ART_ID
           WHERE
                 ART_NEW_NBR.ART_ID = ARTICLES.ART_ID) AS ART_NEW_NBR,
-- ---------------------------------------------------------------                 
          (SELECT
                 GROUP_CONCAT( ART_REP_LOOKUP.ART_ARTICLE_NR )
           FROM
                 ART_REP_NUM
                 LEFT OUTER JOIN ARTICLES AS ART_REP_LOOKUP ON 
                      ART_REP_NUM.ART_NEW_ID = ART_REP_LOOKUP.ART_ID
           WHERE
                 ART_REP_NUM.ART_ID = ARTICLES.ART_ID) AS ART_REPLACE_NBR,
-- ---------------------------------------------------------------              
          (SELECT
                 GROUP_CONCAT( 
                              CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
                                (CASE
                                     WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
                                          THEN (SELECT CRIT_TEXT.DES_TEXT 
                                                FROM  KEY_VALUES
													  INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
                                                            ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
                                                            AND CRIT_TEXT.DES_LNG_ID = @LNGID
                                                WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
                                                      AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
 
                                     ELSE
                                        ARTICLE_CRITERIA.ACR_VALUE
                                END) )
                 SEPARATOR '; ')
          FROM
                 ARTICLE_CRITERIA
                 INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
 
                 INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
 
                 LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
                      ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
                      AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
 
          WHERE
                 ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
                      AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
 
                 LIMIT 4 ) AS ARTICLE_CRITERIA,
-- --------------------------------------------------------------- 
          (SELECT
                 GROUP_CONCAT( CONCAT_WS( ': ', MANUFACTURERS.MFA_BRAND,ART_OEM_NUMBERS.OEM_NUM) SEPARATOR 0x0a )
          FROM
                 ART_OEM_NUMBERS
                 INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ART_OEM_NUMBERS.MFA_ID
                 INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ART_OEM_NUMBERS.OEM_CTM
          WHERE
                 ART_OEM_NUMBERS.ART_ID = ARTICLES.ART_ID
                    AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER) AS OEM_NUMBERS,
-- --------------------------------------------------------------- 
          (SELECT
                 GROUP_CONCAT( ART_EAN_NUMBERS.EAN_NUM SEPARATOR 0x0a )
          FROM
                 ART_EAN_NUMBERS
          WHERE
                 ART_EAN_NUMBERS.ART_ID = ARTICLES.ART_ID) AS EAN_NUMBERS
-- ---------------------------------------------------------------       
    FROM
        ARTICLES
        INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
             AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
 
        LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
             AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
             ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
             AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
 
        INNER JOIN SUPPLIERS ON ARTICLES.ART_SUP_ID = SUP_ID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
             ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
             AND ART_NAME.DES_LNG_ID = @LNGID
    WHERE
        ARTICLES.ART_ID = @ART_ID

Выбор применимости артикля к пассажирским авто

    SET @ART_ID = 319888830; -- REMY 19025100 
 
    SELECT DISTINCT
           PASSENGER_CARS.PC_ID,
           MODELS_SERIES.MS_ID,
           CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
           PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
           PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
           PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
           PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
           PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TECH,
           PC_BODY_TYPE.DES_TEXT,
 
           (SELECT
                  GROUP_CONCAT(ENGINES.ENG_CODE)
            FROM
                  ENGINES
                  JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
            WHERE
                  ENG_DESIGNATIONS.PC_ID = PASSENGER_CARS.PC_ID) AS PC_ENG_CODES
    FROM
           ART_MOD_LINKS
 
           INNER JOIN MODELS_LINKS ON MODELS_LINKS.MOD_LNK_ID = ART_MOD_LINKS.MOD_LNK_ID
               AND MODELS_LINKS.MOD_LNK_TYPE = 1 /* PASSENGER_CARS */
 
           INNER JOIN PASSENGER_CARS ON PASSENGER_CARS.PC_ID = MODELS_LINKS.MOD_LNK_VICH_ID
 
           INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = PASSENGER_CARS.PC_CTM
 
           INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
               AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
 
           INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = PASSENGER_CARS.PC_MS_ID       
 
           INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
 
           INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
               AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
               AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
           LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
               AND MS_NAME.DES_LNG_ID = @LNGID
 
           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
               AND PC_MODEL.DES_LNG_ID = @LNGID
 
           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE 
               ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
               AND PC_BODY_TYPE.DES_LNG_ID = @LNGID 
    WHERE
           ART_MOD_LINKS.ART_ID = @ART_ID

Поиск аналогов запчастей по номерам

    SET @SEARCH_NUMBER = '1900';
 
    SELECT  
          ARTICLES.ART_ID, 
          ARTICLES.ART_ARTICLE_NR,
          SUPPLIERS.SUP_BRAND,
          PT_DES_TEXT.DES_TEXT AS DESCRIPTIONS,
          ART_LOOKUP.ARL_KIND AS FOUND_VIA,
          (SELECT ART_MEDIA_INFO.ART_MEDIA_FILE_NAME 
           FROM   ART_MEDIA_INFO 
           WHERE  ART_MEDIA_INFO.ART_ID = ART_LOOKUP_LINKS.ALL_ART_ID
                   AND ART_MEDIA_INFO.ART_MEDIA_TYPE = 1 LIMIT 1) AS ART_IMAGES
    FROM 
          ART_LOOKUP
          INNER JOIN ART_LOOKUP_LINKS ON ART_LOOKUP_LINKS.ALL_ARL_ID = ART_LOOKUP.ARL_ID
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP_LINKS.ALL_ART_ID
          INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
          INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID
          INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
 
          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID 
            AND PT_DES_TEXT.DES_LNG_ID = @LNGID
    WHERE 
          MATCH (ART_LOOKUP.ARL_SEARCH_NUMBER) AGAINST (@SEARCH_NUMBER)
            AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
 
    ORDER BY SUP_BRAND

Получения всех кроссов для артикула(оригинальных и не оригинальных брендов заменителей)

    SET @ART_ID = 24684; -- SPIDAN 20430
 
    SELECT DISTINCT
           ARTICLES.ART_ID, 
           SUPPLIERS.SUP_BRAND, 
           ARTICLES.ART_ARTICLE_NR,
           PT_DES_TEXT.DES_TEXT AS DESCRIPTIONS
    FROM
           ART_LOOKUP_LINKS
           INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.ART_ID = @ART_ID
 
           INNER JOIN ART_LOOKUP_LINKS AS CROSS_REFERENCE ON CROSS_REFERENCE.ALL_ARL_ID = ART_LOOKUP_LINKS.ALL_ARL_ID
                AND CROSS_REFERENCE.ALL_ART_ID != @ART_ID
           INNER JOIN ART_PRODUCTS_DES AS CROSS_PRODUCTS ON CROSS_PRODUCTS.ART_ID = CROSS_REFERENCE.ALL_ART_ID 
                AND CROSS_PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
 
           INNER JOIN ARTICLES ON ARTICLES.ART_ID = CROSS_PRODUCTS.ART_ID
           INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
           INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
 
           INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = CROSS_PRODUCTS.PT_ID
 
           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID 
                AND PT_DES_TEXT.DES_LNG_ID = @LNGID
 
    WHERE
           ART_LOOKUP_LINKS.ALL_ART_ID = @ART_ID
                AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER 
-- ---------------------------------------------------------------
    UNION (
      SELECT 
             ART_OEM_NUMBERS.MFA_ID,
             MANUFACTURERS.MFA_BRAND AS SUP_BRAND,
             ART_OEM_NUMBERS.OEM_NUM,
             NULL
      FROM       
             ART_OEM_NUMBERS
             LEFT OUTER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ART_OEM_NUMBERS.MFA_ID
      WHERE 
             ART_OEM_NUMBERS.ART_ID = @ART_ID
    )
-- ---------------------------------------------------------------
    ORDER BY SUP_BRAND

Список деталей для артикула

    SET @ART_ID = 2249742; -- HELLA 1EJ 004 440-121
 
    SELECT 
          ARTICLES.ART_ARTICLE_NR,
          ART_STATUS_TEXT.DES_TEXT AS ARTICLE_STATE,
-- ---------------------------------------------------------------
          (SELECT
                 PT_DES_TEXT.DES_TEXT
           FROM
                 PRODUCTS
                 INNER JOIN ART_PRODUCTS_DES ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
 
                 LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT ON 
                      PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID AND PT_DES_TEXT.DES_LNG_ID = @LNGID
           WHERE
                 ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID) AS ART_PRODUCT_NAME,
-- ---------------------------------------------------------------              
          (SELECT
                 GROUP_CONCAT( 
                              CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
                                (CASE
                                     WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
                                          THEN (SELECT CRIT_TEXT.DES_TEXT 
                                                FROM  KEY_VALUES
                                                      INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
                                                            ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
                                                            AND CRIT_TEXT.DES_LNG_ID = @LNGID
                                                WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
                                                      AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
 
                                     ELSE
                                        ARTICLE_CRITERIA.ACR_VALUE
                                END) )
                 SEPARATOR '; ')
          FROM
                 ARTICLE_CRITERIA
                 INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
 
                 INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
 
                 LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
                      ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
                      AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
 
    WHERE
                 ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
                      AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
 
                 LIMIT 4 ) AS ARTICLE_CRITERIA,
-- --------------------------------------------------------------- 
 
		ARTICLES_PART_LIST.APL_QUANTITY AS QUANTITY,
        ARTICLES_PART_LIST.APL_SORT AS ORDER_IN_LIST
 
	FROM
             ARTICLES_PART_LIST
             INNER JOIN ARTICLES ON ARTICLES.ART_ID = ARTICLES_PART_LIST.APL_ART_ID_COMPONENT
 
             INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
                   AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
 
             LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
                   AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
 
             LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
                   ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
                   AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
 
             LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
                   ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
                   AND ART_NAME.DES_LNG_ID = @LNGID
    WHERE
        ARTICLES_PART_LIST.APL_ART_ID = @ART_ID

Выбор списка аксессуаров для артикула

    SET @ART_ID = 1932196; -- HELLA 1A3 005 760-501
 
    SELECT 
          ARTICLES.ART_ARTICLE_NR,
          ART_STATUS_TEXT.DES_TEXT AS ARTICLE_STATE,
-- ---------------------------------------------------------------              
          (SELECT
                 GROUP_CONCAT( 
                              CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
                                (CASE
                                     WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
                                          THEN (SELECT CRIT_TEXT.DES_TEXT 
                                                FROM  KEY_VALUES
                                                      INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
                                                            ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
                                                            AND CRIT_TEXT.DES_LNG_ID = @LNGID
                                                WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
                                                      AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
 
                                     ELSE
                                        ARTICLE_CRITERIA.ACR_VALUE
                                END) )
                  SEPARATOR '; ')
          FROM
                  ARTICLE_CRITERIA
                  INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
 
                  INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
 
                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
                      ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
                      AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
 
          WHERE
                  ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
                      AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
 
          LIMIT 4 ) AS ARTICLE_CRITERIA
-- ---------------------------------------------------------------
 
    FROM
        ART_ACCS_LIST
        INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_ACCS_LIST.ART_ACCS_ID
 
        INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
            AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
 
        LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
            AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
            ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
            AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
            ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
            AND ART_NAME.DES_LNG_ID = @LNGID
    WHERE
        ART_ACCS_LIST.ART_ID = @ART_ID

Выбор списка изображений для артикула

    SET @ART_ID = 6214; -- SPIDAN 20064
    SET @TECDOC_Q = 42017; -- TecDoc 2017Q4
 
    SELECT 
          ART_MEDIA_INFO.ART_MEDIA_TYPE,
-- ---------------------------------------------------------------
          (CASE
               WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 1
                  THEN CONCAT_WS('/',  ART_MEDIA_INFO.ART_MEDIA_SUP_ID, ART_MEDIA_INFO.ART_MEDIA_FILE_NAME)
 
               WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 2
                  THEN CONCAT_WS('/', 'http://dvsegmbh.info/pdf/einbau', @TECDOC_Q, ART_MEDIA_INFO.ART_MEDIA_SUP_ID, ART_MEDIA_INFO.ART_MEDIA_FILE_NAME )
 
               WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 3
                  THEN ART_MEDIA_INFO.ART_MEDIA_HIPPERLINK
          END) AS ART_MEDIA_SOURCE,
-- ---------------------------------------------------------------
          ART_MEDIA_INFO.ART_MEDIA_SUP_ID,
          ART_MEDIA_INFO.ART_MEDIA_IS_TECDRWG,
          ART_MEDIA_INFO.ART_MEDIA_CTM
    FROM
          ART_MEDIA_INFO
          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ART_MEDIA_INFO.ART_MEDIA_CTM
    WHERE
          ART_MEDIA_INFO.ART_ID = @ART_ID
              AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER

Построения дерева товарных групп для пассажирского авто

    SET @PC_ID = 1394;
    SET @STR_TYPE=1; -- passenger cars
 
    SELECT DISTINCT
          SEARCH_TREE.STR_LEVEL,
-- ---------------------------------------------------------------        
          ELT(SEARCH_TREE.STR_LEVEL, NODE_NAME.DES_TEXT,
              NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
              NODE_NAME3.DES_TEXT) AS ROOT_NODE_TEXT,
 
          ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, 
              PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
              PARENT_NODE3.STR_ID) AS ROOT_NODE_STR_ID,
-- ---------------------------------------------------------------            
          ELT(SEARCH_TREE.STR_LEVEL-1, NODE_NAME.DES_TEXT,
              NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
              NODE_NAME3.DES_TEXT) AS NODE_1_TEXT,
 
          ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, 
              PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
              PARENT_NODE3.STR_ID) AS NODE_1_STR_ID, 
-- ---------------------------------------------------------------            
          ELT(SEARCH_TREE.STR_LEVEL-2, NODE_NAME.DES_TEXT,
              NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
              NODE_NAME3.DES_TEXT) AS NODE_2_TEXT,
 
          ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, 
              PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
              PARENT_NODE3.STR_ID) AS NODE_2_STR_ID, 
-- ---------------------------------------------------------------            
          ELT(SEARCH_TREE.STR_LEVEL-3, NODE_NAME.DES_TEXT,
              NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
              NODE_NAME3.DES_TEXT) AS NODE_3_TEXT,
 
          ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, 
              PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
              PARENT_NODE3.STR_ID) AS NODE_3_STR_ID
-- ---------------------------------------------------------------         
    FROM
        LINK_PT_VIC
        INNER JOIN LINK_PT_STR ON LINK_PT_STR.STR_TYPE = @STR_TYPE
			AND LINK_PT_STR.PT_ID = LINK_PT_VIC.PT_ID
 
        INNER JOIN SEARCH_TREE ON SEARCH_TREE.STR_ID = LINK_PT_STR.STR_ID 
              AND SEARCH_TREE.STR_TYPE = @STR_TYPE
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME ON 
              SEARCH_TREE.STR_DES_ID = NODE_NAME.DES_ID AND NODE_NAME.DES_LNG_ID = @LNGID
 
        LEFT JOIN SEARCH_TREE AS PARENT_NODE1 ON PARENT_NODE1.STR_ID = SEARCH_TREE.STR_ID_PARENT 
              AND PARENT_NODE1.STR_TYPE = @STR_TYPE
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME1 ON 
              PARENT_NODE1.STR_DES_ID = NODE_NAME1.DES_ID AND NODE_NAME1.DES_LNG_ID = @LNGID
 
        LEFT JOIN SEARCH_TREE AS PARENT_NODE2 ON PARENT_NODE2.STR_ID = PARENT_NODE1.STR_ID_PARENT 
              AND PARENT_NODE2.STR_TYPE = @STR_TYPE
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME2 ON 
              PARENT_NODE2.STR_DES_ID = NODE_NAME2.DES_ID AND NODE_NAME2.DES_LNG_ID = @LNGID
 
        LEFT JOIN SEARCH_TREE AS PARENT_NODE3 ON PARENT_NODE3.STR_ID = PARENT_NODE2.STR_ID_PARENT
              AND PARENT_NODE3.STR_TYPE = @STR_TYPE
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME3 ON 
              PARENT_NODE3.STR_DES_ID = NODE_NAME3.DES_ID AND NODE_NAME3.DES_LNG_ID = @LNGID
 
    WHERE
        LINK_PT_VIC.LNK_ID = @PC_ID AND LINK_PT_VIC.LNK_TYPE = @STR_TYPE
    ORDER BY
        ROOT_NODE_TEXT,
        NODE_1_TEXT,
        NODE_2_TEXT,
        NODE_3_TEXT

Выбор поставщиков и товарных групп для пассажирского авто

    SET @STR_ID = 102691;
    SET @PC_ID = 1394;
    SET @STR_TYPE=1; -- passenger car
 
    SELECT DISTINCT
          SUPPLIERS.SUP_BRAND,
          PRODUCT_NAME.DES_TEXT/*,
          ARTICLES.ART_ID*/
    FROM 
          SEARCH_TREE
          INNER JOIN LINK_PT_STR ON LINK_PT_STR.STR_ID = SEARCH_TREE.STR_ID 
                AND LINK_PT_STR.STR_TYPE = @STR_TYPE
 
          INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = LINK_PT_STR.PT_ID
          INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.PT_ID = PRODUCTS.PT_ID
 
          INNER JOIN MODELS_LINKS ON MODELS_LINKS.MOD_LNK_VICH_ID = @PC_ID 
                AND MODELS_LINKS.MOD_LNK_TYPE = @STR_TYPE
 
          INNER JOIN ART_MOD_LINKS ON ART_MOD_LINKS.MOD_LNK_ID = MODELS_LINKS.MOD_LNK_ID
 
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_MOD_LINKS.ART_ID 
                AND ARTICLES.ART_ID = ART_PRODUCTS_DES.ART_ID
 
          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
 
          INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PRODUCT_NAME ON 
                PRODUCTS.PT_DES_ID = PRODUCT_NAME.DES_ID AND PRODUCT_NAME.DES_LNG_ID = @LNGID
 
    WHERE
          SEARCH_TREE.STR_ID = @STR_ID AND SEARCH_TREE.STR_TYPE = @STR_TYPE 
                AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
    ORDER BY SUP_BRAND

Получения координаторов артикулов на общем изображении для списка деталей

    SET @ART_ID = 15969381; -- EBERSPÄCHER 032025
 
    SELECT
          ARTICLES.ART_ID,
          ARTICLES.ART_ARTICLE_NR,
          SENSITIVE_COORDINATES.SEN_COORD_ID,
          SENSITIVE_COORDINATES.SEN_COORD_X,
          SENSITIVE_COORDINATES.SEN_COORD_Y,
          SENSITIVE_COORDINATES.SEN_COORD_WIDTH,
          SENSITIVE_COORDINATES.SEN_COORD_HEIGHT,
          SENSITIVE_COORDINATES.SEN_COORD_TYPE
    FROM
          ART_PL_PIC_DATA
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_PL_PIC_DATA.APL_ART_ID_COMPONENT
 
          INNER JOIN SENSITIVE_COORDINATES ON SENSITIVE_COORDINATES.ART_PARENT_GRP_ID = ART_PL_PIC_DATA.APL_ART_ID
                AND SENSITIVE_COORDINATES.SEN_COORD_ID = ART_PL_PIC_DATA.SEN_COORD_ID
 
          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
    WHERE
          ART_PL_PIC_DATA.APL_ART_ID = @ART_ID 
                AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER

Выбор списка линковочных критериев артикула для пассажирского авто

    SET @ART_ID = 10505815; -- ATE 13.0460-2847.2
    SET @TYPE_ID = 1347; -- AUDI V8 (44_, 4C_) 3.6 quattro
 
    SELECT 
         LA_CRIT_GROUP.LAC_GR_ID,
         IFNULL(INFO_TITLE_TEXT.DES_TEXT,CRITERIA_TITLE_TEXT.DES_TEXT) AS CRITERIA_TITLE_TEXT,
         CRITERIA_TITLE_SHORT_TEXT.DES_TEXT AS CRITERIA_TITLE_SHORT_TEXT,
-- ---------------------------------------------------------------
         (CASE
			  WHEN LA_CRITERIA.LAC_VALUE IS NOT NULL
				   THEN LA_CRITERIA.LAC_VALUE
 
			  WHEN KEY_VALUE_TABLE_TEXT.DES_TEXT IS NOT NULL
				   THEN KEY_VALUE_TABLE_TEXT.DES_TEXT
 
			  WHEN TEXT_MODULE_TEXTS.TMT_TEXT IS NOT NULL
				   THEN TEXT_MODULE_TEXTS.TMT_TEXT
 
			  WHEN LA_CRITERIA.LAC_LNK_ID IS NOT NULL
                   THEN(
                      CASE 
                         WHEN LA_CRITERIA.LAC_CRI_ID = 14 -- Enginies
                              THEN (
							         SELECT 
                                           CONCAT_WS(" ", MANUFACTURERS.MFA_BRAND, ENGINES.ENG_CODE)
                                     FROM
                                           ENGINES
                                           INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ENGINES.ENG_MFA_ID
 
									 WHERE
										   ENGINES.ENG_ID = LA_CRITERIA.LAC_LNK_ID
                              )
					  ELSE
                          LA_CRITERIA.LAC_LNK_ID
                      END
                   ) 
 
         END) AS LA_CRITERIA_VALUE
-- ---------------------------------------------------------------         
    FROM 
         LINK_LA_TYP
         INNER JOIN LA_CRIT_GROUP ON LA_CRIT_GROUP.LAC_GR_ID = LINK_LA_TYP.LAT_LAC_GR_ID
         INNER JOIN LA_CRITERIA ON LA_CRITERIA.LAC_LA_ID = LA_CRIT_GROUP.LAC_GR_LA_ID
         INNER JOIN CRITERIA ON CRITERIA.CRI_ID = LA_CRITERIA.LAC_CRI_ID
         INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = LA_CRITERIA.LAC_CTM
 
         LEFT OUTER JOIN LA_INFO ON LA_INFO.LIN_LA_ID = LA_CRITERIA.LAC_LA_ID
         LEFT OUTER JOIN TEXT_MODULES ON LA_INFO.LIN_TMO_ID = TEXT_MODULES.TMO_ID
         LEFT OUTER JOIN TEXT_MODULE_TEXTS ON TEXT_MODULE_TEXTS.TMT_ID = TEXT_MODULES.TMO_TMT_ID
            AND (TEXT_MODULE_TEXTS.TMT_LNG_ID = @LNGID OR TEXT_MODULE_TEXTS.TMT_LNG_ID = 255)
 
         LEFT OUTER JOIN KEY_VALUES AS INFO_TITLE 
            ON INFO_TITLE.KV_KV = TEXT_MODULES.TMO_KV_KV
            AND INFO_TITLE.KV_KT_ID = TEXT_MODULES.TMO_KV_KT_ID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS INFO_TITLE_TEXT 
            ON INFO_TITLE_TEXT.DES_ID = INFO_TITLE.KV_DES_ID
            AND INFO_TITLE_TEXT.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN KEY_VALUES AS KEY_VALUE_TABLE 
            ON KEY_VALUE_TABLE.KV_KV = LA_CRITERIA.LAC_KV_KV
            AND KEY_VALUE_TABLE.KV_KT_ID = LA_CRITERIA.LAC_KV_KT_ID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS KEY_VALUE_TABLE_TEXT 
            ON KEY_VALUE_TABLE.KV_DES_ID = KEY_VALUE_TABLE_TEXT.DES_ID 
            AND KEY_VALUE_TABLE_TEXT.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TITLE_TEXT 
            ON CRITERIA.CRI_DES_ID = CRITERIA_TITLE_TEXT.DES_ID 
            AND CRITERIA_TITLE_TEXT.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TITLE_SHORT_TEXT 
            ON CRITERIA.CRI_SHORT_DES_ID = CRITERIA_TITLE_SHORT_TEXT.DES_ID 
            AND CRITERIA_TITLE_SHORT_TEXT.DES_LNG_ID = @LNGID
 
    WHERE
         LINK_LA_TYP.LAT_ART_ID = @ART_ID
            AND LINK_LA_TYP.LAT_TYP_ID = @TYPE_ID 
            AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
	ORDER BY 
		LA_CRIT_GROUP.LAC_GR_ID

Поиск пассажирского авто по KBA-номеру

   SET @KBA_NUM = '0588549';
   SET @TYPE_NUM_COU_CODE = 'D'; -- 'F' for cartes grises and 'NL' for Kenteken
 
   SELECT
         CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
         PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
         PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
         PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
         PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TAX,
         PC_BODY_TYPE.DES_TEXT
   FROM 
         TYPE_NUMBERS
         INNER JOIN PASSENGER_CARS ON PASSENGER_CARS.PC_ID = TYPE_NUMBERS.PC_ID
 
         INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = PASSENGER_CARS.PC_CTM
 
         INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
               AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
 
         INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = PASSENGER_CARS.PC_MS_ID
 
         INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
 
         INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
               AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
               AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
               AND MS_NAME.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
               AND PC_MODEL.DES_LNG_ID = @LNGID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
               AND PC_BODY_TYPE.DES_LNG_ID = @LNGID   
   WHERE 
         MATCH (TYPE_NUMBERS.TYP_NUM) AGAINST (@KBA_NUM)
               AND TYPE_NUMBERS.COU_CODE = @TYPE_NUM_COU_CODE
               AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER

Выгрузка кроссов не оригинальный бренд / оригинальные бренды в CSV

    SET @SUPPLIER='FEBI BILSTEIN';
 
    SELECT 
        "PRODUCT_GROUP", 
        "NOT_ORIGINAL_BRAND_NAME", 
        "NOT_ORIGINAL_CODE_PARTS", 
        "NOT_ORIGINAL_ART_ID", 
        "ORIGINAL_BRANDS_NAME", 
        "ORIGINAL_BRANDS_CODE_PARTS" 
 
 UNION 
 
    SELECT DISTINCT
        PRODUCT_NAME.DES_TEXT,
        SUPPLIERS.SUP_BRAND,
        ARTICLES.ART_ARTICLE_NR,
        ARTICLES.ART_ID,
        MANUFACTURERS.MFA_BRAND,
        ART_OEM_NUMBERS.OEM_NUM
    FROM
        SUPPLIERS
        INNER JOIN ARTICLES ON ARTICLES.ART_SUP_ID = SUPPLIERS.SUP_ID
        INNER JOIN ART_OEM_NUMBERS ON ART_OEM_NUMBERS.ART_ID = ARTICLES.ART_ID
        INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ART_OEM_NUMBERS.MFA_ID
        INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID
        INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
 
        LEFT OUTER JOIN TEXT_DESIGNATIONS AS PRODUCT_NAME
             ON  PRODUCTS.PT_DES_ID = PRODUCT_NAME.DES_ID
             AND PRODUCT_NAME.DES_LNG_ID = @LNGID
    WHERE
        SUPPLIERS.SUP_BRAND = @SUPPLIER
 
    INTO OUTFILE 'D:/FEBI BILSTEIN.csv'
        FIELDS TERMINATED BY ';'
        ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n';

Выгрузка кроссов оригинальный бренд / не оригинальные бренды в CSV

   SET @BRAND='BMW';
 
   SELECT 
        "PRODUCT_GROUP",  
        "ORIGINAL_BRANDS_NAME", 
        "ORIGINAL_BRANDS_CODE_PARTS",
        "NOT_ORIGINAL_BRAND_NAME", 
        "NOT_ORIGINAL_CODE_PARTS", 
        "NOT_ORIGINAL_ART_ID"
   UNION 
 
   SELECT DISTINCT
         PRODUCT_NAME.DES_TEXT,
         MANUFACTURERS.MFA_BRAND,
         ART_OEM_NUMBERS.OEM_NUM,
         SUPPLIERS.SUP_BRAND,
         ARTICLES.ART_ARTICLE_NR,
		 ARTICLES.ART_ID
   FROM
         MANUFACTURERS
         INNER JOIN ART_OEM_NUMBERS ON ART_OEM_NUMBERS.MFA_ID = MANUFACTURERS.MFA_ID
         INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_OEM_NUMBERS.ART_ID
         INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
         INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID
         INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
 
         LEFT OUTER JOIN TEXT_DESIGNATIONS AS PRODUCT_NAME
             ON  PRODUCTS.PT_DES_ID = PRODUCT_NAME.DES_ID
             AND PRODUCT_NAME.DES_LNG_ID = @LNGID
   WHERE
         MANUFACTURERS.MFA_BRAND = @BRAND 
 
   INTO OUTFILE 'D:/BMW.csv'
         FIELDS TERMINATED BY ';'
         ENCLOSED BY '"'
         LINES TERMINATED BY '\r\n';
Источник — «https://autodevel.com/w/index.php?title=Tecdoc_2018_q1_multi&oldid=462»