Tecdoc sql sample

Материал из AutoDevel
Перейти к: навигация, поиск
На этой странице были произведены изменения, не отмеченные для перевода.
Другие языки:
English • ‎русский

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

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

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

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

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

  1.    SELECT 
  2.          MFA_BRAND
  3.    FROM 
  4.          MANUFACTURERS
  5.          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = MANUFACTURERS.MFA_PC_CTM
  6.    WHERE 
  7.          MANUFACTURERS.MFA_PC = 1 AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  8.    ORDER BY MFA_BRAND;

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

  1.    SET @MFAID = 35; -- FIAT
  2.    SELECT DISTINCT
  3.          TEXT_DESIGNATIONS.DES_TEXT
  4.    FROM 
  5.          MODELS_SERIES
  6.          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = MODELS_SERIES.MS_PC_CTM
  7.  
  8.          INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
  9.              AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
  10.              AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
  11.  
  12.          INNER JOIN TEXT_DESIGNATIONS ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = TEXT_DESIGNATIONS.DES_ID 
  13.              AND TEXT_DESIGNATIONS.DES_LNG_ID = @LNGID
  14.    WHERE 
  15.          MODELS_SERIES.MS_MFA_ID = @MFAID  
  16.              AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  17.              AND MODELS_SERIES.MS_PC = 1

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

  1.    SET @MSID = 348; -- FIAT ARGENTA (132_)
  2.  
  3.    SELECT
  4.          CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
  5.          PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
  6.          PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
  7.          PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
  8.          PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
  9.          PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TAX,
  10.          PC_BODY_TYPE.DES_TEXT
  11.    FROM 
  12.          PASSENGER_CARS
  13.          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = PASSENGER_CARS.PC_CTM
  14.  
  15.          INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
  16.                AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
  17.  
  18.          INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = @MSID
  19.  
  20.          INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
  21.  
  22.          INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
  23.                AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
  24.                AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
  25.  
  26.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
  27.                AND MS_NAME.DES_LNG_ID = @LNGID
  28.  
  29.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
  30.                AND PC_MODEL.DES_LNG_ID = @LNGID
  31.  
  32.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
  33.                AND PC_BODY_TYPE.DES_LNG_ID = @LNGID   
  34.    WHERE 
  35.          PASSENGER_CARS.PC_MS_ID = @MSID  
  36.          AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  37.          AND MODELS_SERIES.MS_PC = 1

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

  1.    SET @PCID = 1014; -- FORD TRANSIT Bus (V_ _) 2.0 (VAS, VIL, VLS, VUL, VZS)
  2.  
  3.    SELECT
  4.          CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
  5.          PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
  6.          PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
  7.          PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
  8.          PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
  9.          PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TAX,
  10.          PC_COUNTRY_SPECIFICS.PCS_CAPACITY_LT,
  11.          PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TECH,
  12.          PC_COUNTRY_SPECIFICS.PCS_ABS,
  13.          PC_COUNTRY_SPECIFICS.PCS_ASR,
  14.          PC_COUNTRY_SPECIFICS.PCS_NUMBER_OF_CYLINDERS,
  15.  
  16.          PC_BODY_TYPE.DES_TEXT AS PC_BODY_TYPE,
  17.          PC_ENGINE_TYPE.DES_TEXT AS PC_ENGINE_TYPE,
  18.          PC_GEAR_TYPE.DES_TEXT AS PC_GEAR_TYPE,
  19.          PC_DRIVE_TYPE.DES_TEXT AS PC_DRIVE_TYPE,
  20.          PC_BRAKE_SYSTEM.DES_TEXT AS PC_BRAKE_SYSTEM,
  21.          PC_BRAKE_TYPE.DES_TEXT AS PC_BRAKE_TYPE,
  22.          PC_FUEL_TYPE.DES_TEXT AS PC_FUEL_TYPE,
  23.          PC_CATALYSATOR_TYPE.DES_TEXT AS PC_CATALYSATOR_TYPE,
  24.          PC_FUEL_MIXTURE.DES_TEXT AS PC_FUEL_MIXTURE,
  25.  
  26.          (SELECT
  27.                 GROUP_CONCAT(ENGINES.ENG_CODE)
  28.           FROM
  29.                 ENGINES
  30.                 JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
  31.           WHERE
  32.                 ENG_DESIGNATIONS.PC_ID = PASSENGER_CARS.PC_ID) AS PC_ENG_CODES
  33.  
  34.    FROM 
  35.          PASSENGER_CARS
  36.  
  37.          INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
  38.               AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
  39.  
  40.          INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = PASSENGER_CARS.PC_MS_ID
  41.  
  42.          INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
  43.  
  44.          INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
  45.               AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
  46.               AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
  47.  
  48.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
  49.               AND MS_NAME.DES_LNG_ID = @LNGID
  50.  
  51.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
  52.               AND PC_MODEL.DES_LNG_ID = @LNGID
  53.  
  54.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
  55.               AND PC_BODY_TYPE.DES_LNG_ID = @LNGID
  56.  
  57.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_ENGINE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_ENGINE_TYPE = PC_ENGINE_TYPE.DES_ID 
  58.               AND PC_ENGINE_TYPE.DES_LNG_ID = @LNGID
  59.  
  60.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_GEAR_TYPE ON PC_COUNTRY_SPECIFICS.PCS_GEAR_TYPE = PC_GEAR_TYPE.DES_ID 
  61.               AND PC_GEAR_TYPE.DES_LNG_ID = @LNGID
  62.  
  63.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_DRIVE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_DRIVE_TYPE = PC_DRIVE_TYPE.DES_ID 
  64.               AND PC_DRIVE_TYPE.DES_LNG_ID = @LNGID
  65.  
  66.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BRAKE_SYSTEM ON PC_COUNTRY_SPECIFICS.PCS_BRAKE_SYSTEM = PC_BRAKE_SYSTEM.DES_ID 
  67.               AND PC_BRAKE_SYSTEM.DES_LNG_ID = @LNGID
  68.  
  69.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BRAKE_TYPE ON PC_COUNTRY_SPECIFICS.PCS_BRAKE_TYPE = PC_BRAKE_TYPE.DES_ID 
  70.               AND PC_BRAKE_TYPE.DES_LNG_ID = @LNGID
  71.  
  72.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_FUEL_TYPE ON PC_COUNTRY_SPECIFICS.PCS_FUEL_TYPE = PC_FUEL_TYPE.DES_ID 
  73.               AND PC_FUEL_TYPE.DES_LNG_ID = @LNGID
  74.  
  75.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_CATALYSATOR_TYPE 
  76.               ON PC_COUNTRY_SPECIFICS.PCS_CATALYSATOR_TYPE = PC_CATALYSATOR_TYPE.DES_ID 
  77.               AND PC_CATALYSATOR_TYPE.DES_LNG_ID = @LNGID
  78.  
  79.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_FUEL_MIXTURE ON PC_COUNTRY_SPECIFICS.PCS_FUEL_MIXTURE = PC_FUEL_MIXTURE.DES_ID 
  80.               AND PC_FUEL_MIXTURE.DES_LNG_ID = @LNGID
  81.  
  82.    WHERE 
  83.          PASSENGER_CARS.PC_ID = @PCID

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

  1.     SET @CV_ID=17582; -- DAF CF FA 290
  2.  
  3.     SELECT DISTINCT
  4.            CV_ID AS TECDOC_TYPE_NO,
  5.            CONCAT( MFA_BRAND, ' ', TEXT_DESIGNATIONS2.DES_TEXT, ' ', TEXT_DESIGNATIONS1.DES_TEXT) AS TYPEL,
  6.            CV_COUNTRY_SPECIFICS.CCS_CONSTRUCTION_INTERVAL_START,
  7.            CV_COUNTRY_SPECIFICS.CCS_CONSTRUCTION_INTERVAL_END,
  8.            CV_COUNTRY_SPECIFICS.CCS_POWER_PS_START,
  9.            CV_COUNTRY_SPECIFICS.CCS_POWER_PS_UPTO,
  10.            CV_COUNTRY_SPECIFICS.CCS_POWER_KW_START,
  11.            CV_COUNTRY_SPECIFICS.CCS_POWER_KW_UPTO,
  12.            CV_COUNTRY_SPECIFICS.CCS_CAPACITY_TECH,
  13.            CV_COUNTRY_SPECIFICS.CCS_TONNAGE,
  14.  
  15.            CV_PLATFORM_TYPE.DES_TEXT AS CV_PLATFORM_TYPE,
  16.            CV_ENGINE_TYPE.DES_TEXT AS CV_ENGINE_TYPE,
  17.            CV_AXLE_CONFIGURATION.DES_TEXT AS CV_AXLE_CONFIGURATION,
  18. -- ---------------------------------------------------------------    
  19.            (SELECT
  20.                   GROUP_CONCAT( CONCAT(  WHEELBASES.AXLE_GROUP, '/',  WHEELBASES.WHELL_BASE, ' mm' ) )
  21.             FROM
  22.                   WHEELBASES
  23.             WHERE
  24.                   WHEELBASES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_WHEELBASES,
  25. -- ---------------------------------------------------------------
  26.             (SELECT
  27.                   GROUP_CONCAT( CONCAT( AXLE_POS_TEXT.DES_TEXT, '/',  SUSPENSION_TEXT.DES_TEXT) )
  28.              FROM
  29.                   CV_SUSPENSIONS
  30.                   LEFT OUTER JOIN TEXT_DESIGNATIONS AS AXLE_POS_TEXT 
  31. 			       ON CV_SUSPENSIONS.AXLE_POS_DES = AXLE_POS_TEXT.DES_ID AND AXLE_POS_TEXT.DES_LNG_ID = @LNGID
  32.  
  33.                   LEFT OUTER JOIN TEXT_DESIGNATIONS AS SUSPENSION_TEXT 
  34. 			       ON  CV_SUSPENSIONS.SUSPENSION_DES = SUSPENSION_TEXT.DES_ID AND SUSPENSION_TEXT.DES_LNG_ID = @LNGID
  35.              WHERE
  36.                   CV_SUSPENSIONS.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_SUSPENSIONS,
  37. -- ---------------------------------------------------------------
  38.             (SELECT
  39.                   GROUP_CONCAT( DC_MODEL_TEXT.DES_TEXT )
  40.              FROM
  41.                   DRIVERS_CABS
  42.                   LEFT OUTER JOIN DRIVERS_CABS_DES ON DRIVERS_CABS_DES.DC_ID = DRIVERS_CABS.DC_ID
  43.  
  44.                   LEFT OUTER JOIN TEXT_DESIGNATIONS AS DC_MODEL_TEXT ON 
  45.                   DRIVERS_CABS.DC_MODEL_DES = DC_MODEL_TEXT.DES_ID AND DC_MODEL_TEXT.DES_LNG_ID = @LNGID
  46.              WHERE
  47.                   DRIVERS_CABS_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as DRIVERS_CABS,
  48. -- ---------------------------------------------------------------
  49.             (SELECT
  50.                   GROUP_CONCAT( AXLES.AXL_DESCRIPTION )
  51.              FROM
  52.                   AXLES
  53.                   LEFT OUTER JOIN CV_AXLES_DES ON CV_AXLES_DES.AXL_ID = AXLES.AXL_ID
  54.              WHERE
  55.                   CV_AXLES_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as AXLES,
  56. -- ---------------------------------------------------------------
  57.             (SELECT
  58.                   GROUP_CONCAT( CV_MANUFACTURER_IDS.CV_MANUF_NUM )
  59.              FROM
  60.                   CV_MANUFACTURER_IDS
  61.                   LEFT OUTER JOIN CV_MANUF_IDS_DES ON CV_MANUF_IDS_DES.CV_MANUF_ID = CV_MANUFACTURER_IDS.CV_MANUF_ID
  62.              WHERE
  63.                   CV_MANUF_IDS_DES.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_MANUF_CODES,
  64. -- ---------------------------------------------------------------
  65.             (SELECT
  66.                   GROUP_CONCAT(ENGINES.ENG_CODE)
  67.              FROM
  68.                   ENGINES
  69.                   JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
  70.              WHERE
  71.                   ENG_DESIGNATIONS.CV_ID = COMMERCIAL_VEHICLES.CV_ID) as CV_ENG_CODES
  72.     FROM
  73.         COMMERCIAL_VEHICLES
  74.  
  75.         INNER JOIN CV_COUNTRY_SPECIFICS ON CV_COUNTRY_SPECIFICS.CCS_CV_ID = COMMERCIAL_VEHICLES.CV_ID
  76.              AND (CV_COUNTRY_SPECIFICS.CCS_COU_ID = @COUNTRY_FILTER OR CV_COUNTRY_SPECIFICS.CCS_COU_ID = 255)
  77.  
  78.         INNER JOIN MODELS_SERIES ON COMMERCIAL_VEHICLES.CV_MS_ID = MODELS_SERIES.MS_ID
  79.  
  80.         INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
  81.              AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
  82.              AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
  83.  
  84.         INNER JOIN MANUFACTURERS ON COMMERCIAL_VEHICLES.CV_MFA_ID = MANUFACTURERS.MFA_ID
  85.  
  86.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS TEXT_DESIGNATIONS1 ON 
  87.              COMMERCIAL_VEHICLES.CV_MODEL_DES = TEXT_DESIGNATIONS1.DES_ID AND TEXT_DESIGNATIONS1.DES_LNG_ID = @LNGID
  88.  
  89.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS TEXT_DESIGNATIONS2 ON 
  90.              MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = TEXT_DESIGNATIONS2.DES_ID AND TEXT_DESIGNATIONS2.DES_LNG_ID = @LNGID
  91.  
  92.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_PLATFORM_TYPE ON 
  93.              CV_COUNTRY_SPECIFICS.CCS_PLATFORM_TYPE = CV_PLATFORM_TYPE.DES_ID AND CV_PLATFORM_TYPE.DES_LNG_ID = @LNGID
  94.  
  95.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_ENGINE_TYPE 
  96.              ON CV_COUNTRY_SPECIFICS.CCS_ENGINE_TYPE = CV_ENGINE_TYPE.DES_ID 
  97.              AND CV_ENGINE_TYPE.DES_LNG_ID = @LNGID
  98.  
  99.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS CV_AXLE_CONFIGURATION 
  100.              ON CV_COUNTRY_SPECIFICS.CCS_AXLE_CONFIGURATION = CV_AXLE_CONFIGURATION.DES_ID 
  101.              AND CV_AXLE_CONFIGURATION.DES_LNG_ID = @LNGID
  102.     WHERE
  103.         COMMERCIAL_VEHICLES.CV_ID = @CV_ID

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

  1.     SET @ART_NUMBER = '19025100'; -- REMY 19025100
  2.  
  3.     SELECT
  4.           ARTICLES.ART_ID,
  5.           ARTICLES.ART_ARTICLE_NR,
  6.           ART_COUNTRY_SPECIFICS.ACS_PACK_UNIT,
  7.           ART_COUNTRY_SPECIFICS.ACS_QUANTITY_PER_UNIT,
  8.           ART_COUNTRY_SPECIFICS.ACS_STATUS_DATE,
  9.           ART_STATUS_TEXT.DES_TEXT AS ART_STATUS_TEXT,
  10.           SUPPLIERS.SUP_BRAND,
  11. -- ---------------------------------------------------------------        
  12.           CONCAT_WS(' ',
  13.                     (SELECT
  14.                            GROUP_CONCAT( PT_DES_TEXT.DES_TEXT SEPARATOR ',' )
  15.                      FROM
  16.                            PRODUCTS
  17.                            INNER JOIN ART_PRODUCTS_DES ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
  18.  
  19.                            LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT 
  20.                                 ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID 
  21.                                 AND PT_DES_TEXT.DES_LNG_ID = @LNGID
  22.                      WHERE
  23.                            ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID), ART_NAME.DES_TEXT) AS ART_PRODUCT_NAME,
  24. -- ---------------------------------------------------------------                  
  25.           (SELECT 
  26.                  CONCAT_WS(0x0a0d2d, ART_INFO_TITLE.DES_TEXT, GROUP_CONCAT(TEXT_MODULE_TEXTS.TMT_TEXT SEPARATOR 0x0a0d2d ) ) 
  27.            FROM
  28.                  ART_INFO
  29.                  LEFT OUTER JOIN TEXT_MODULES ON ART_INFO.AIN_TMO_ID = TEXT_MODULES.TMO_ID
  30.  
  31.                  LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KV = TEXT_MODULES.TMO_KV_KV 
  32.                       AND KEY_VALUES.KV_KT_ID = TEXT_MODULES.TMO_KV_KT_ID
  33.  
  34.                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_INFO_TITLE ON ART_INFO_TITLE.DES_ID = KEY_VALUES.KV_DES_ID
  35.                       AND ART_INFO_TITLE.DES_LNG_ID = @LNGID
  36.  
  37.                  LEFT OUTER JOIN TEXT_MODULE_TEXTS ON TEXT_MODULE_TEXTS.TMT_ID = TEXT_MODULES.TMO_TMT_ID
  38.                       AND (TEXT_MODULE_TEXTS.TMT_LNG_ID = 255 OR TEXT_MODULE_TEXTS.TMT_LNG_ID = @LNGID)
  39.            WHERE
  40.                  ART_INFO.AIN_ART_ID = ARTICLES.ART_ID) AS ART_INFO,
  41. -- ---------------------------------------------------------------            
  42.           (SELECT
  43.                  GROUP_CONCAT( ART_NEW_LOOKUP.ART_ARTICLE_NR )
  44.            FROM
  45.                  ART_NEW_NBR
  46.                  LEFT OUTER JOIN ARTICLES AS ART_NEW_LOOKUP ON 
  47.                    ART_NEW_NBR.ART_NEW_ID = ART_NEW_LOOKUP.ART_ID
  48.            WHERE
  49.                  ART_NEW_NBR.ART_ID = ARTICLES.ART_ID) AS ART_NEW_NBR,
  50. -- ---------------------------------------------------------------                 
  51.           (SELECT
  52.                  GROUP_CONCAT( ART_REP_LOOKUP.ART_ARTICLE_NR )
  53.            FROM
  54.                  ART_REP_NUM
  55.                  LEFT OUTER JOIN ARTICLES AS ART_REP_LOOKUP ON 
  56.                       ART_REP_NUM.ART_NEW_ID = ART_REP_LOOKUP.ART_ID
  57.            WHERE
  58.                  ART_REP_NUM.ART_ID = ARTICLES.ART_ID) AS ART_REPLACE_NBR,
  59. -- ---------------------------------------------------------------              
  60.           (SELECT
  61.                  GROUP_CONCAT( 
  62.                               CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
  63.                                 (CASE
  64.                                      WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
  65.                                           THEN (SELECT CRIT_TEXT.DES_TEXT 
  66.                                                 FROM  KEY_VALUES
  67. 													  INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
  68.                                                             ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
  69.                                                             AND CRIT_TEXT.DES_LNG_ID = @LNGID
  70.                                                 WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
  71.                                                       AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
  72.  
  73.                                      ELSE
  74.                                         ARTICLE_CRITERIA.ACR_VALUE
  75.                                 END) )
  76.                  SEPARATOR '; ')
  77.           FROM
  78.                  ARTICLE_CRITERIA
  79.                  INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
  80.  
  81.                  INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
  82.  
  83.                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
  84.                       ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
  85.                       AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
  86.  
  87.           WHERE
  88.                  ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
  89.                       AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  90.  
  91.                  LIMIT 4 ) AS ARTICLE_CRITERIA,
  92. -- --------------------------------------------------------------- 
  93.           (SELECT
  94.                  GROUP_CONCAT( CONCAT_WS( ': ', MANUFACTURERS.MFA_BRAND,ART_OEM_NUMBERS.OEM_NUM) SEPARATOR 0x0a )
  95.           FROM
  96.                  ART_OEM_NUMBERS
  97.                  INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ART_OEM_NUMBERS.MFA_ID
  98.                  INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ART_OEM_NUMBERS.OEM_CTM
  99.           WHERE
  100.                  ART_OEM_NUMBERS.ART_ID = ARTICLES.ART_ID
  101.                     AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER) AS OEM_NUMBERS,
  102. -- --------------------------------------------------------------- 
  103.           (SELECT
  104.                  GROUP_CONCAT( ART_EAN_NUMBERS.EAN_NUM SEPARATOR 0x0a )
  105.           FROM
  106.                  ART_EAN_NUMBERS
  107.           WHERE
  108.                  ART_EAN_NUMBERS.ART_ID = ARTICLES.ART_ID) AS EAN_NUMBERS
  109. -- ---------------------------------------------------------------       
  110.     FROM
  111.         ARTICLES
  112.         INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
  113.              AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
  114.  
  115.         LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
  116.              AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
  117.  
  118.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
  119.              ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
  120.              AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
  121.  
  122.         INNER JOIN SUPPLIERS ON ARTICLES.ART_SUP_ID = SUP_ID
  123.  
  124.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
  125.              ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
  126.              AND ART_NAME.DES_LNG_ID = @LNGID
  127.     WHERE
  128.         ARTICLES.ART_ARTICLE_NR = @ART_NUMBER

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

  1.     SET @ART_ID = 316483103; -- REMY 19025100 
  2.  
  3.     SELECT DISTINCT
  4.            PASSENGER_CARS.PC_ID,
  5.            MODELS_SERIES.MS_ID,
  6.            CONCAT( MFA_BRAND, ' ', MS_NAME.DES_TEXT, ' ', PC_MODEL.DES_TEXT) AS TYPEL,
  7.            PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_START,
  8.            PC_COUNTRY_SPECIFICS.PCS_CONSTRUCTION_INTERVAL_END,
  9.            PC_COUNTRY_SPECIFICS.PCS_POWER_KW,
  10.            PC_COUNTRY_SPECIFICS.PCS_POWER_PS,
  11.            PC_COUNTRY_SPECIFICS.PCS_CAPACITY_TECH,
  12.            PC_BODY_TYPE.DES_TEXT,
  13.  
  14.            (SELECT
  15.                   GROUP_CONCAT(ENGINES.ENG_CODE)
  16.             FROM
  17.                   ENGINES
  18.                   JOIN ENG_DESIGNATIONS ON (ENGINES.ENG_ID=ENG_DESIGNATIONS.ENG_ID)
  19.             WHERE
  20.                   ENG_DESIGNATIONS.PC_ID = PASSENGER_CARS.PC_ID) AS PC_ENG_CODES
  21.     FROM
  22.            ART_MOD_LINKS
  23.  
  24.            INNER JOIN MODELS_LINKS ON MODELS_LINKS.MOD_LNK_ID = ART_MOD_LINKS.MOD_LNK_ID
  25.                AND MODELS_LINKS.MOD_LNK_TYPE = 1 /* PASSENGER_CARS */
  26.  
  27.            INNER JOIN PASSENGER_CARS ON PASSENGER_CARS.PC_ID = MODELS_LINKS.MOD_LNK_VICH_ID
  28.  
  29.            INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = PASSENGER_CARS.PC_CTM
  30.  
  31.            INNER JOIN PC_COUNTRY_SPECIFICS ON PC_COUNTRY_SPECIFICS.PCS_PC_ID = PASSENGER_CARS.PC_ID
  32.                AND (PC_COUNTRY_SPECIFICS.PCS_COU_ID = @COUNTRY_FILTER OR PC_COUNTRY_SPECIFICS.PCS_COU_ID = 255)
  33.  
  34.            INNER JOIN MODELS_SERIES ON MODELS_SERIES.MS_ID = PASSENGER_CARS.PC_MS_ID       
  35.  
  36.            INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = PASSENGER_CARS.PC_MFA_ID
  37.  
  38.            INNER JOIN MS_COUNTRY_SPECIFICS ON MS_COUNTRY_SPECIFICS.MSCS_ID = MODELS_SERIES.MS_ID
  39.                AND (MS_COUNTRY_SPECIFICS.MSCS_COU_ID = @COUNTRY_FILTER OR MS_COUNTRY_SPECIFICS.MSCS_COU_ID = 255)
  40.                AND MS_COUNTRY_SPECIFICS.MSCS_AXL = 0
  41.  
  42.            LEFT OUTER JOIN TEXT_DESIGNATIONS AS MS_NAME ON MS_COUNTRY_SPECIFICS.MSCS_NAME_DES = MS_NAME.DES_ID 
  43.                AND MS_NAME.DES_LNG_ID = @LNGID
  44.  
  45.            LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_MODEL ON PASSENGER_CARS.PC_MODEL_DES = PC_MODEL.DES_ID 
  46.                AND PC_MODEL.DES_LNG_ID = @LNGID
  47.  
  48.            LEFT OUTER JOIN TEXT_DESIGNATIONS AS PC_BODY_TYPE 
  49.                ON PC_COUNTRY_SPECIFICS.PCS_BODY_TYPE = PC_BODY_TYPE.DES_ID 
  50.                AND PC_BODY_TYPE.DES_LNG_ID = @LNGID 
  51.     WHERE
  52.            ART_MOD_LINKS.ART_ID = @ART_ID

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

  1.     SET @SEARCH_NUMBER = '19025100';
  2.  
  3.     SELECT  
  4.           ARTICLES.ART_ID, 
  5.           ARTICLES.ART_ARTICLE_NR,
  6.           SUPPLIERS.SUP_BRAND,
  7.           PT_DES_TEXT.DES_TEXT,
  8.           ART_LOOKUP.ARL_KIND AS FOUND_VIA
  9.     FROM 
  10.           ART_LOOKUP
  11.           INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
  12.           INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
  13.           INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID
  14.           INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
  15.  
  16.           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID 
  17.             AND PT_DES_TEXT.DES_LNG_ID = @LNGID
  18.     WHERE 
  19.           ART_LOOKUP.ARL_SEARCH_NUMBER = @SEARCH_NUMBER

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

  1.     SET @ART_ID = 2209153; -- 1EJ 004 440-121
  2.  
  3.     SELECT 
  4.           ARTICLES.ART_ARTICLE_NR,
  5.           ART_STATUS_TEXT.DES_TEXT AS ARTICLE_STATE,
  6. -- ---------------------------------------------------------------
  7.           (SELECT
  8.                  PT_DES_TEXT.DES_TEXT
  9.            FROM
  10.                  PRODUCTS
  11.                  INNER JOIN ART_PRODUCTS_DES ON PRODUCTS.PT_ID = ART_PRODUCTS_DES.PT_ID
  12.  
  13.                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS PT_DES_TEXT ON 
  14.                       PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID AND PT_DES_TEXT.DES_LNG_ID = @LNGID
  15.            WHERE
  16.                  ART_PRODUCTS_DES.ART_ID = ARTICLES.ART_ID) AS ART_PRODUCT_NAME,
  17. -- ---------------------------------------------------------------              
  18.           (SELECT
  19.                  GROUP_CONCAT( 
  20.                               CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
  21.                                 (CASE
  22.                                      WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
  23.                                           THEN (SELECT CRIT_TEXT.DES_TEXT 
  24.                                                 FROM  KEY_VALUES
  25.                                                       INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
  26.                                                             ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
  27.                                                             AND CRIT_TEXT.DES_LNG_ID = @LNGID
  28.                                                 WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
  29.                                                       AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
  30.  
  31.                                      ELSE
  32.                                         ARTICLE_CRITERIA.ACR_VALUE
  33.                                 END) )
  34.                  SEPARATOR '; ')
  35.           FROM
  36.                  ARTICLE_CRITERIA
  37.                  INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
  38.  
  39.                  INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
  40.  
  41.                  LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
  42.                       ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
  43.                       AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
  44.  
  45.     WHERE
  46.                  ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
  47.                       AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  48.  
  49.                  LIMIT 4 ) AS ARTICLE_CRITERIA,
  50. -- --------------------------------------------------------------- 
  51.  
  52. 		ARTICLES_PART_LIST.APL_QUANTITY AS QUANTITY,
  53.         ARTICLES_PART_LIST.APL_SORT AS ORDER_IN_LIST
  54.  
  55. 	FROM
  56.              ARTICLES_PART_LIST
  57.              INNER JOIN ARTICLES ON ARTICLES.ART_ID = ARTICLES_PART_LIST.APL_ART_ID_COMPONENT
  58.  
  59.              INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
  60.                    AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
  61.  
  62.              LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
  63.                    AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
  64.  
  65.              LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
  66.                    ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
  67.                    AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
  68.  
  69.              LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
  70.                    ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
  71.                    AND ART_NAME.DES_LNG_ID = @LNGID
  72.     WHERE
  73.         ARTICLES_PART_LIST.APL_ART_ID = @ART_ID

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

  1.     SET @ART_ID = 1895743; -- 1A3 005 760-501
  2.  
  3.     SELECT 
  4.           ARTICLES.ART_ARTICLE_NR,
  5.           ART_STATUS_TEXT.DES_TEXT AS ARTICLE_STATE,
  6. -- ---------------------------------------------------------------              
  7.           (SELECT
  8.                  GROUP_CONCAT( 
  9.                               CONCAT_WS(': ', CRITERIA_TEXT.DES_TEXT, 
  10.                                 (CASE
  11.                                      WHEN CRITERIA.CRI_TYPE = 3 /* ARTICLE_CRITERIA.ACR_KV_KT_ID IS NOT NULL */
  12.                                           THEN (SELECT CRIT_TEXT.DES_TEXT 
  13.                                                 FROM  KEY_VALUES
  14.                                                       INNER JOIN TEXT_DESIGNATIONS AS CRIT_TEXT 
  15.                                                             ON KEY_VALUES.KV_DES_ID = CRIT_TEXT.DES_ID
  16.                                                             AND CRIT_TEXT.DES_LNG_ID = @LNGID
  17.                                                 WHERE KEY_VALUES.KV_KT_ID = ARTICLE_CRITERIA.ACR_KV_KT_ID 
  18.                                                       AND KEY_VALUES.KV_KV = ARTICLE_CRITERIA.ACR_KV_KV )
  19.  
  20.                                      ELSE
  21.                                         ARTICLE_CRITERIA.ACR_VALUE
  22.                                 END) )
  23.                   SEPARATOR '; ')
  24.           FROM
  25.                   ARTICLE_CRITERIA
  26.                   INNER JOIN CRITERIA ON ARTICLE_CRITERIA.ACR_CRI_ID = CRITERIA.CRI_ID
  27.  
  28.                   INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLE_CRITERIA.ACR_CTM
  29.  
  30.                   LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TEXT 
  31.                       ON CRITERIA.CRI_DES_ID = CRITERIA_TEXT.DES_ID 
  32.                       AND CRITERIA_TEXT.DES_LNG_ID = @LNGID
  33.  
  34.           WHERE
  35.                   ARTICLE_CRITERIA.ACR_ART_ID = ARTICLES.ART_ID 
  36.                       AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  37.  
  38.           LIMIT 4 ) AS ARTICLE_CRITERIA
  39. -- ---------------------------------------------------------------
  40.  
  41.     FROM
  42.         ART_ACCS_LIST
  43.         INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_ACCS_LIST.ART_ACCS_ID
  44.  
  45.         INNER JOIN ART_COUNTRY_SPECIFICS ON ART_COUNTRY_SPECIFICS.ACS_ART_ID = ARTICLES.ART_ID
  46.             AND (ART_COUNTRY_SPECIFICS.ACS_COU_ID = @COUNTRY_FILTER OR ART_COUNTRY_SPECIFICS.ACS_COU_ID = 255)
  47.  
  48.         LEFT OUTER JOIN KEY_VALUES ON KEY_VALUES.KV_KT_ID = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KT_ID
  49.             AND KEY_VALUES.KV_KV = ART_COUNTRY_SPECIFICS.ACS_STATUS_KV_KV
  50.  
  51.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_STATUS_TEXT 
  52.             ON KEY_VALUES.KV_DES_ID = ART_STATUS_TEXT.DES_ID 
  53.             AND ART_STATUS_TEXT.DES_LNG_ID = @LNGID
  54.  
  55.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS ART_NAME 
  56.             ON  ARTICLES.ART_DES_ID = ART_NAME.DES_ID 
  57.             AND ART_NAME.DES_LNG_ID = @LNGID
  58.     WHERE
  59.         ART_ACCS_LIST.ART_ID = @ART_ID

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

  1.     SET @ART_ID = 2209153; -- 1EJ 004 440-121
  2.     SET @TECDOC_Q = 32017; -- TecDoc 2017Q3
  3.  
  4.     SELECT 
  5.           ART_MEDIA_INFO.ART_MEDIA_FILE_NAME,
  6.           ART_MEDIA_INFO.ART_MEDIA_TYPE,
  7. -- ---------------------------------------------------------------
  8.           (CASE
  9.                WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 1
  10.                   THEN CONCAT_WS('/', 'http://toc-cdn.tecdoc.net/webdownload/Bilder', @TECDOC_Q, ART_MEDIA_INFO.ART_MEDIA_FILE_NAME )
  11.  
  12.                WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 2
  13.                   THEN CONCAT_WS('/', 'http://dvsegmbh.info/pdf/einbau', @TECDOC_Q, ART_MEDIA_INFO.ART_MEDIA_SUP_ID, ART_MEDIA_INFO.ART_MEDIA_FILE_NAME )
  14.  
  15.                WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 3
  16.                   THEN ART_MEDIA_INFO.ART_MEDIA_HIPPERLINK
  17.           END) AS ART_MEDIA_HIPPERLINK,
  18. -- ---------------------------------------------------------------
  19.           ART_MEDIA_INFO.ART_MEDIA_SUP_ID,
  20.           ART_MEDIA_INFO.ART_MEDIA_IS_TECDRWG,
  21.           ART_MEDIA_INFO.ART_MEDIA_CTM
  22.     FROM
  23.           ART_MEDIA_INFO
  24.           INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ART_MEDIA_INFO.ART_MEDIA_CTM
  25.     WHERE
  26.           ART_MEDIA_INFO.ART_ID = @ART_ID
  27.               AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER

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

  1.     SET @PC_ID = 1394;
  2.     SET @STR_TYPE=1; -- passenger cars
  3.  
  4.     SELECT DISTINCT
  5.           SEARCH_TREE.STR_LEVEL,
  6. -- ---------------------------------------------------------------        
  7.           ELT(SEARCH_TREE.STR_LEVEL, NODE_NAME.DES_TEXT,
  8.               NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
  9.               NODE_NAME3.DES_TEXT) AS ROOT_NODE_TEXT,
  10.  
  11.           ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, 
  12.               PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
  13.               PARENT_NODE3.STR_ID) AS ROOT_NODE_STR_ID,
  14. -- ---------------------------------------------------------------            
  15.           ELT(SEARCH_TREE.STR_LEVEL-1, NODE_NAME.DES_TEXT,
  16.               NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
  17.               NODE_NAME3.DES_TEXT) AS NODE_1_TEXT,
  18.  
  19.           ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, 
  20.               PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
  21.               PARENT_NODE3.STR_ID) AS NODE_1_STR_ID, 
  22. -- ---------------------------------------------------------------            
  23.           ELT(SEARCH_TREE.STR_LEVEL-2, NODE_NAME.DES_TEXT,
  24.               NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
  25.               NODE_NAME3.DES_TEXT) AS NODE_2_TEXT,
  26.  
  27.           ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, 
  28.               PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
  29.               PARENT_NODE3.STR_ID) AS NODE_2_STR_ID, 
  30. -- ---------------------------------------------------------------            
  31.           ELT(SEARCH_TREE.STR_LEVEL-3, NODE_NAME.DES_TEXT,
  32.               NODE_NAME1.DES_TEXT,  NODE_NAME2.DES_TEXT, 
  33.               NODE_NAME3.DES_TEXT) AS NODE_3_TEXT,
  34.  
  35.           ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, 
  36.               PARENT_NODE1.STR_ID, PARENT_NODE2.STR_ID, 
  37.               PARENT_NODE3.STR_ID) AS NODE_3_STR_ID
  38. -- ---------------------------------------------------------------         
  39.     FROM
  40.         LINK_PT_VIC
  41.         INNER JOIN LINK_PT_STR ON LINK_PT_STR.STR_TYPE = @STR_TYPE
  42. 			AND LINK_PT_STR.PT_ID = LINK_PT_VIC.PT_ID
  43.  
  44.         INNER JOIN SEARCH_TREE ON SEARCH_TREE.STR_ID = LINK_PT_STR.STR_ID 
  45.               AND SEARCH_TREE.STR_TYPE = @STR_TYPE
  46.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME ON 
  47.               SEARCH_TREE.STR_DES_ID = NODE_NAME.DES_ID AND NODE_NAME.DES_LNG_ID = @LNGID
  48.  
  49.         LEFT JOIN SEARCH_TREE AS PARENT_NODE1 ON PARENT_NODE1.STR_ID = SEARCH_TREE.STR_ID_PARENT 
  50.               AND PARENT_NODE1.STR_TYPE = @STR_TYPE
  51.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME1 ON 
  52.               PARENT_NODE1.STR_DES_ID = NODE_NAME1.DES_ID AND NODE_NAME1.DES_LNG_ID = @LNGID
  53.  
  54.         LEFT JOIN SEARCH_TREE AS PARENT_NODE2 ON PARENT_NODE2.STR_ID = PARENT_NODE1.STR_ID_PARENT 
  55.               AND PARENT_NODE2.STR_TYPE = @STR_TYPE
  56.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME2 ON 
  57.               PARENT_NODE2.STR_DES_ID = NODE_NAME2.DES_ID AND NODE_NAME2.DES_LNG_ID = @LNGID
  58.  
  59.         LEFT JOIN SEARCH_TREE AS PARENT_NODE3 ON PARENT_NODE3.STR_ID = PARENT_NODE2.STR_ID_PARENT
  60.               AND PARENT_NODE3.STR_TYPE = @STR_TYPE
  61.         LEFT OUTER JOIN TEXT_DESIGNATIONS AS NODE_NAME3 ON 
  62.               PARENT_NODE3.STR_DES_ID = NODE_NAME3.DES_ID AND NODE_NAME3.DES_LNG_ID = @LNGID
  63.  
  64.     WHERE
  65.         LINK_PT_VIC.LNK_ID = @PC_ID AND LINK_PT_VIC.LNK_TYPE = @STR_TYPE
  66.     ORDER BY
  67.         ROOT_NODE_TEXT,
  68.         NODE_1_TEXT,
  69.         NODE_2_TEXT,
  70.         NODE_3_TEXT

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

  1.     SET @STR_ID = 102691;
  2.     SET @PC_ID = 1394;
  3.     SET @STR_TYPE=1; -- passenger car
  4.  
  5.     SELECT DISTINCT
  6.           SUPPLIERS.SUP_BRAND,
  7.           PRODUCT_NAME.DES_TEXT/*,
  8.           ARTICLES.ART_ID*/
  9.     FROM 
  10.           SEARCH_TREE
  11.           INNER JOIN LINK_PT_STR ON LINK_PT_STR.STR_ID = SEARCH_TREE.STR_ID 
  12.                 AND LINK_PT_STR.STR_TYPE = @STR_TYPE
  13.  
  14.           INNER JOIN PRODUCTS ON PRODUCTS.PT_ID = LINK_PT_STR.PT_ID
  15.           INNER JOIN ART_PRODUCTS_DES ON ART_PRODUCTS_DES.PT_ID = PRODUCTS.PT_ID
  16.  
  17.           INNER JOIN MODELS_LINKS ON MODELS_LINKS.MOD_LNK_VICH_ID = @PC_ID 
  18.                 AND MODELS_LINKS.MOD_LNK_TYPE = @STR_TYPE
  19.  
  20.           INNER JOIN ART_MOD_LINKS ON ART_MOD_LINKS.MOD_LNK_ID = MODELS_LINKS.MOD_LNK_ID
  21.  
  22.           INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_MOD_LINKS.ART_ID 
  23.                 AND ARTICLES.ART_ID = ART_PRODUCTS_DES.ART_ID
  24.  
  25.           INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
  26.  
  27.           INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
  28.           LEFT OUTER JOIN TEXT_DESIGNATIONS AS PRODUCT_NAME ON 
  29.                 PRODUCTS.PT_DES_ID = PRODUCT_NAME.DES_ID AND PRODUCT_NAME.DES_LNG_ID = @LNGID
  30.  
  31.     WHERE
  32.           SEARCH_TREE.STR_ID = @STR_ID AND SEARCH_TREE.STR_TYPE = @STR_TYPE 
  33.                 AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  34.     ORDER BY SUP_BRAND

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

  1.     SET @ART_ID = 15329885;
  2.  
  3.     SELECT
  4.           ARTICLES.ART_ID,
  5.           ARTICLES.ART_ARTICLE_NR,
  6.           SENSITIVE_COORDINATES.SEN_COORD_ID,
  7.           SENSITIVE_COORDINATES.SEN_COORD_X,
  8.           SENSITIVE_COORDINATES.SEN_COORD_Y,
  9.           SENSITIVE_COORDINATES.SEN_COORD_WIDTH,
  10.           SENSITIVE_COORDINATES.SEN_COORD_HEIGHT,
  11.           SENSITIVE_COORDINATES.SEN_COORD_TYPE
  12.     FROM
  13.           ART_PL_PIC_DATA
  14.           INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_PL_PIC_DATA.APL_ART_ID_COMPONENT
  15.  
  16.           INNER JOIN SENSITIVE_COORDINATES ON SENSITIVE_COORDINATES.ART_PARENT_GRP_ID = ART_PL_PIC_DATA.APL_ART_ID
  17.                 AND SENSITIVE_COORDINATES.SEN_COORD_ID = ART_PL_PIC_DATA.SEN_COORD_ID
  18.  
  19.           INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = ARTICLES.ART_CTM
  20.     WHERE
  21.           ART_PL_PIC_DATA.APL_ART_ID = @ART_ID 
  22.                 AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER

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

  1.     SET @ART_ID = 10328087;
  2.     SET @TYPE_ID = 1347;
  3.  
  4.     SELECT 
  5.          LA_CRIT_GROUP.LAC_GR_ID,
  6.          IFNULL(INFO_TITLE_TEXT.DES_TEXT,CRITERIA_TITLE_TEXT.DES_TEXT) AS CRITERIA_TITLE_TEXT,
  7.          CRITERIA_TITLE_SHORT_TEXT.DES_TEXT AS CRITERIA_TITLE_SHORT_TEXT,
  8. -- ---------------------------------------------------------------
  9.          (CASE
  10. 			  WHEN LA_CRITERIA.LAC_VALUE IS NOT NULL
  11. 				   THEN LA_CRITERIA.LAC_VALUE
  12.  
  13. 			  WHEN KEY_VALUE_TABLE_TEXT.DES_TEXT IS NOT NULL
  14. 				   THEN KEY_VALUE_TABLE_TEXT.DES_TEXT
  15.  
  16. 			  WHEN TEXT_MODULE_TEXTS.TMT_TEXT IS NOT NULL
  17. 				   THEN TEXT_MODULE_TEXTS.TMT_TEXT
  18.  
  19. 			  WHEN LA_CRITERIA.LAC_LNK_ID IS NOT NULL
  20.                    THEN(
  21.                       CASE 
  22.                          WHEN LA_CRITERIA.LAC_CRI_ID = 14 -- Enginies
  23.                               THEN (
  24. 							         SELECT 
  25.                                            CONCAT_WS(" ", MANUFACTURERS.MFA_BRAND, ENGINES.ENG_CODE)
  26.                                      FROM
  27.                                            ENGINES
  28.                                            INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ENGINES.ENG_MFA_ID
  29.  
  30. 									 WHERE
  31. 										   ENGINES.ENG_ID = LA_CRITERIA.LAC_LNK_ID
  32.                               )
  33. 					  ELSE
  34.                           LA_CRITERIA.LAC_LNK_ID
  35.                       END
  36.                    ) 
  37.  
  38.          END) AS LA_CRITERIA_VALUE
  39. -- ---------------------------------------------------------------         
  40.     FROM 
  41.          LINK_LA_TYP
  42.          INNER JOIN LA_CRIT_GROUP ON LA_CRIT_GROUP.LAC_GR_ID = LINK_LA_TYP.LAT_LAC_GR_ID
  43.          INNER JOIN LA_CRITERIA ON LA_CRITERIA.LAC_LA_ID = LA_CRIT_GROUP.LAC_GR_LA_ID
  44.          INNER JOIN CRITERIA ON CRITERIA.CRI_ID = LA_CRITERIA.LAC_CRI_ID
  45.          INNER JOIN COUNTRY_RESTRICTIONS ON COUNTRY_RESTRICTIONS.CNTR_ID = LA_CRITERIA.LAC_CTM
  46.  
  47.          LEFT OUTER JOIN LA_INFO ON LA_INFO.LIN_LA_ID = LA_CRITERIA.LAC_LA_ID
  48.          LEFT OUTER JOIN TEXT_MODULES ON LA_INFO.LIN_TMO_ID = TEXT_MODULES.TMO_ID
  49.          LEFT OUTER JOIN TEXT_MODULE_TEXTS ON TEXT_MODULE_TEXTS.TMT_ID = TEXT_MODULES.TMO_TMT_ID
  50.             AND (TEXT_MODULE_TEXTS.TMT_LNG_ID = @LNGID OR TEXT_MODULE_TEXTS.TMT_LNG_ID = 255)
  51.  
  52.          LEFT OUTER JOIN KEY_VALUES AS INFO_TITLE 
  53.             ON INFO_TITLE.KV_KV = TEXT_MODULES.TMO_KV_KV
  54.             AND INFO_TITLE.KV_KT_ID = TEXT_MODULES.TMO_KV_KT_ID
  55.  
  56.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS INFO_TITLE_TEXT 
  57.             ON INFO_TITLE_TEXT.DES_ID = INFO_TITLE.KV_DES_ID
  58.             AND INFO_TITLE_TEXT.DES_LNG_ID = @LNGID
  59.  
  60.          LEFT OUTER JOIN KEY_VALUES AS KEY_VALUE_TABLE 
  61.             ON KEY_VALUE_TABLE.KV_KV = LA_CRITERIA.LAC_KV_KV
  62.             AND KEY_VALUE_TABLE.KV_KT_ID = LA_CRITERIA.LAC_KV_KT_ID
  63.  
  64.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS KEY_VALUE_TABLE_TEXT 
  65.             ON KEY_VALUE_TABLE.KV_DES_ID = KEY_VALUE_TABLE_TEXT.DES_ID 
  66.             AND KEY_VALUE_TABLE_TEXT.DES_LNG_ID = @LNGID
  67.  
  68.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TITLE_TEXT 
  69.             ON CRITERIA.CRI_DES_ID = CRITERIA_TITLE_TEXT.DES_ID 
  70.             AND CRITERIA_TITLE_TEXT.DES_LNG_ID = @LNGID
  71.  
  72.          LEFT OUTER JOIN TEXT_DESIGNATIONS AS CRITERIA_TITLE_SHORT_TEXT 
  73.             ON CRITERIA.CRI_SHORT_DES_ID = CRITERIA_TITLE_SHORT_TEXT.DES_ID 
  74.             AND CRITERIA_TITLE_SHORT_TEXT.DES_LNG_ID = @LNGID
  75.  
  76.     WHERE
  77.          LINK_LA_TYP.LAT_ART_ID = @ART_ID
  78.             AND LINK_LA_TYP.LAT_TYP_ID = @TYPE_ID 
  79.             AND COUNTRY_RESTRICTIONS.CNTR_COU_ID = @COUNTRY_FILTER
  80. 	ORDER BY 
  81. 		LA_CRIT_GROUP.LAC_GR_ID
Источник — «https://autodevel.com/w/index.php?title=Tecdoc_sql_sample&oldid=316»