En ART LOOKUP.sql — различия между версиями

Текущая версия на 09:18, 15 мая 2013

/* Product search (any suppliers) by a specified original/non-original/commercial number. */

/* It is used to specify the supplier, to further search for analogs by a particular combination: */

/* Number + Supplier */

/* BRAND - The name of the product supplier */

/* NUMBER - Product number */

/* ARL_KIND - Type of the product number: 1 or 4 - Non-original, 2 - Commercial, 3 - Original */

/* ARL_ART_ID - Key ART_ID of the corresponding non-original product */

/* ART_COMPLETE_DES_TEXT - Product name */

/* */

/* There are simplified number variations at the input and at the output (only letters/digits -without spaces/points/dashes etc.) */

/* It is better to search by simplified numbers, as different number variations are excluded - */

/* TecDoc itself searches exactly this way. */

/* You have to delete everything besides letters/digits from the desired number before using this query,

/* or to use MySQL-function CLEAN_NUMBER from the example FUNCTIONS.sql. */

/* */

/* WARNING: Do not forget 'single quotes' during values substitution instead of variable @NUMBER,

            otherwise this query is running VERY slowly, since it does not use the text index by the field ARL_SEARCH_NUMBER */

SET @NUMBER = '1244211212'; /* MERCEDES-BENZ | Brake disk */

/* SET @NUMBER = '52107'; /* DINEX/NRF/PAGID/SPIDAN | Exhaust pipe */

SET @LNG_ID = 16; /* 1 - German; 16 - Russian */

SELECT DISTINCT
IF (ART_LOOKUP.ARL_KIND IN (3, 4), BRANDS.BRA_BRAND, SUPPLIERS.SUP_BRAND) AS BRAND,	ART_LOOKUP.ARL_SEARCH_NUMBER AS NUMBER,	ART_LOOKUP.ARL_KIND,	ART_LOOKUP.ARL_ART_ID,	DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT
FROM       ART_LOOKUP
LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID
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 DESIGNATIONS ON DESIGNATIONS.DES_ID = ARTICLES.ART_COMPLETE_DES_ID
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
WHERE	ART_LOOKUP.ARL_SEARCH_NUMBER = @NUMBER AND	ART_LOOKUP.ARL_KIND IN (1, 2, 3, 4) AND	DESIGNATIONS.DES_LNG_ID = @LNG_ID
GROUP BY	BRAND,	NUMBER;

• Back to index page: Tecdoc_queries_and_structure_manual_-_version_2

• Prices and packages for Irbis software based on tecdoc from Mstarproject company: http://www.mstarproject.com/en/?action=tecdoc_mysql_site