Tecdoc sql sample
Материал из AutoDevel
Эта страница — перевод страницы Tecdoc sql sample. Перевод выполнен на 100%.
To filter text information in the different languages, you can use the index from the table LANGUAGES.LNG_ID
.
To filter objects by country, you can use the index from the table COUNTRIES.COU_ID
.
In all the examples presented here, the values of variables @COUNTRY_FILTER
and @LNGID
is:
SET @COUNTRY_FILTER = 54; -- Germany
SET @LNGID=4; -- english
Содержание
- 1 Select a list of manufacturers for the passenger cars
- 2 Selecting a list of model series for the manufacturer of passenger cars
- 3 Selecting a list of passenger car models for the model series
- 4 Selection of detailed information about the passenger car
- 5 Selection of detailed information about commercial vehicles
- 6 Selection of information about the article
- 7 Selecting of the applicability of the article to passenger cars
- 8 Search for analog spare parts by the articles numbers
- 9 List of parts for article
- 10 Select a list of accessories list for the article
- 11 Selecting a list of images and documents for the article
- 12 Building a tree of the products groups for a passenger car
- 13 Selection of suppliers and articles of the product group node for passenger cars
- 14 Get the coordinates of a article on a group image for the article parts list
- 15 Select linkage criteria when applying the article to a vehicle
Select a list of manufacturers for the passenger cars
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;
Selecting a list of model series for the manufacturer of passenger cars
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
Selecting a list of passenger car models for the model series
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
Selection of detailed information about the passenger car
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
Selection of detailed information about commercial vehicles
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
Selection of information about the article
SET @ART_NUMBER = '19025100'; -- 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_ARTICLE_NR = @ART_NUMBER
Selecting of the applicability of the article to passenger cars
SET @ART_ID = 316483103; -- 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
Search for analog spare parts by the articles numbers
SET @SEARCH_NUMBER = '19025100';
SELECT
ARTICLES.ART_ID,
ARTICLES.ART_ARTICLE_NR,
SUPPLIERS.SUP_BRAND,
PT_DES_TEXT.DES_TEXT,
ART_LOOKUP.ARL_KIND AS FOUND_VIA
FROM
ART_LOOKUP
INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_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 PT_DES_TEXT ON PRODUCTS.PT_DES_ID = PT_DES_TEXT.DES_ID
AND PT_DES_TEXT.DES_LNG_ID = @LNGID
WHERE
ART_LOOKUP.ARL_SEARCH_NUMBER = @SEARCH_NUMBER
List of parts for article
SET @ART_ID = 2209153; -- 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
Select a list of accessories list for the article
SET @ART_ID = 1895743; -- 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
Selecting a list of images and documents for the article
SET @ART_ID = 2209153; -- 1EJ 004 440-121
SET @TECDOC_Q = 32017; -- TecDoc 2017Q3
SELECT
ART_MEDIA_INFO.ART_MEDIA_FILE_NAME,
ART_MEDIA_INFO.ART_MEDIA_TYPE,
-- ---------------------------------------------------------------
(CASE
WHEN ART_MEDIA_INFO.ART_MEDIA_TYPE = 1
THEN CONCAT_WS('/', 'http://toc-cdn.tecdoc.net/webdownload/Bilder', @TECDOC_Q, 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_HIPPERLINK,
-- ---------------------------------------------------------------
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
Building a tree of the products groups for a passenger car
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
Selection of suppliers and articles of the product group node for passenger cars
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
Get the coordinates of a article on a group image for the article parts list
SET @ART_ID = 15329885;
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
Select linkage criteria when applying the article to a vehicle
SET @ART_ID = 10328087;
SET @TYPE_ID = 1347;
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