SELECT
COALESCE(NULLIF(ph.PH_ID, ''), '0') AS promo_id,
COALESCE(
NULLIF(ph.PH_NAME, ''),
CONCAT('Promotion ', ph.PH_ID)
) AS promo_name,
COALESCE(NULLIF(pd.PR_PRODUCTCODE, ''), '') AS promo_product_code,
COALESCE(
NULLIF(tp.PM_DESCRIPTION, ''),
NULLIF(mp.Title, ''),
pd.PR_PRODUCTCODE
) AS promo_title,
COALESCE(pd.PR_NEWPRICE, 0) AS promo_special_price,
COALESCE(pd.PR_PRICE, 0) AS promo_original_price,
COALESCE(pd.META_TERMS, 0) AS promo_terms,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_start_date,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_end_date
FROM trader.promotion_detail pd
INNER JOIN trader.promotion_header ph
ON ph.PH_ID = pd.PR_ID
LEFT JOIN trader.products tp
ON tp.PM_PRODUCTCODE = pd.PR_PRODUCTCODE
LEFT JOIN trader.meta_product mp
ON mp.Code = pd.PR_PRODUCTCODE
WHERE COALESCE(pd.PR_NEWPRICE, 0) > 0
AND (
COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
) IS NULL
OR COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
) <= CURDATE()
)
AND (
COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
) IS NULL
OR COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
) >= CURDATE()
)
ORDER BY COALESCE(pd.PR_NEWPRICE, 0) DESC, ph.PH_ID DESC, pd.PR_PRODUCTCODE
LIMIT {offset}, 1
SELECT
COALESCE(NULLIF(ph.PH_ID, ''), '0') AS promo_id,
COALESCE(
NULLIF(ph.PH_NAME, ''),
CONCAT('Promotion ', ph.PH_ID)
) AS promo_name,
COALESCE(NULLIF(pd.PR_PRODUCTCODE, ''), '') AS promo_product_code,
COALESCE(
NULLIF(tp.PM_DESCRIPTION, ''),
NULLIF(mp.Title, ''),
pd.PR_PRODUCTCODE
) AS promo_title,
COALESCE(pd.PR_NEWPRICE, 0) AS promo_special_price,
COALESCE(pd.PR_PRICE, 0) AS promo_original_price,
COALESCE(pd.META_TERMS, 0) AS promo_terms,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_start_date,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_end_date
FROM trader.promotion_detail pd
INNER JOIN trader.promotion_header ph
ON ph.PH_ID = pd.PR_ID
LEFT JOIN trader.products tp
ON tp.PM_PRODUCTCODE = pd.PR_PRODUCTCODE
LEFT JOIN trader.meta_product mp
ON mp.Code = pd.PR_PRODUCTCODE
WHERE CAST(ph.PH_ID AS CHAR(32)) = '{promo_id}'
AND pd.PR_PRODUCTCODE = '{product_code}'
LIMIT 1
SELECT
COALESCE(NULLIF(ph.PH_ID, ''), '0') AS promo_id,
COALESCE(
NULLIF(ph.PH_NAME, ''),
CONCAT('Promotion ', ph.PH_ID)
) AS promo_name,
COALESCE(NULLIF(pd.PR_PRODUCTCODE, ''), '') AS promo_product_code,
COALESCE(
NULLIF(tp.PM_DESCRIPTION, ''),
NULLIF(mp.Title, ''),
pd.PR_PRODUCTCODE
) AS promo_title,
COALESCE(pd.PR_NEWPRICE, 0) AS promo_special_price,
COALESCE(pd.PR_PRICE, 0) AS promo_original_price,
COALESCE(pd.META_TERMS, 0) AS promo_terms,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_start_date,
COALESCE(
DATE_FORMAT(
COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
),
'%Y-%m-%d'
),
''
) AS promo_end_date
FROM trader.promotion_detail pd
INNER JOIN trader.promotion_header ph
ON ph.PH_ID = pd.PR_ID
LEFT JOIN trader.products tp
ON tp.PM_PRODUCTCODE = pd.PR_PRODUCTCODE
LEFT JOIN trader.meta_product mp
ON mp.Code = pd.PR_PRODUCTCODE
WHERE pd.PR_PRODUCTCODE = '{product_code}'
AND COALESCE(pd.PR_NEWPRICE, 0) > 0
{promo_filter}
AND (
COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
) IS NULL
OR COALESCE(
STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_STARTDATE, '%Y-%m-%d')
) <= CURDATE()
)
AND (
COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
) IS NULL
OR COALESCE(
STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y'),
STR_TO_DATE(ph.PH_ENDDATE, '%Y-%m-%d')
) >= CURDATE()
)
ORDER BY COALESCE(pd.PR_NEWPRICE, 0) DESC, ph.PH_ID DESC
LIMIT 1
SELECT
COALESCE(NULLIF(ph.PH_STARTDATE, ''), '') AS promo_start_raw,
COALESCE(NULLIF(ph.PH_ENDDATE, ''), '') AS promo_end_raw
FROM trader.promotion_detail pd
INNER JOIN trader.promotion_header ph
ON ph.PH_ID = pd.PR_ID
WHERE pd.PR_PRODUCTCODE = '{product_code}'
{promo_filter}
ORDER BY ph.PH_ID DESC
LIMIT 1
¶ show_sys_user_columns
SHOW COLUMNS FROM sys_user
SELECT COLUMN_NAME AS field
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'sys_user'
SELECT *
FROM sys_user
WHERE `{msisdn_col}` IN ({escaped_values})
OR {normalized_col} IN ({escaped_values})
LIMIT 1
SELECT *
FROM sys_user
WHERE {where_clause}
LIMIT 1
SELECT Value AS ConstantValue
FROM def_constants
WHERE ConstantName = '{key}'
AND Package IN ('{package}', 'CORE', '')
ORDER BY CASE
WHEN Package = '{package}' THEN 1
WHEN Package = 'CORE' THEN 2
ELSE 3
END
LIMIT 1
SELECT Value AS ConstantValue
FROM def_constant
WHERE ConstantCode = '{key}'
AND Package IN ('{package}', 'CORE', '')
ORDER BY CASE
WHEN Package = '{package}' THEN 1
WHEN Package = 'CORE' THEN 2
ELSE 3
END
LIMIT 1
SELECT Value AS ConstantValue
FROM def_constant
WHERE ConstantName = '{key}'
AND Package IN ('{package}', 'CORE', '')
ORDER BY CASE
WHEN Package = '{package}' THEN 1
WHEN Package = 'CORE' THEN 2
ELSE 3
END
LIMIT 1
SELECT
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(PayloadJson, '$.primary_image')), ''),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(PayloadJson, '$.response.primary_image')), ''),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(PayloadJson, '$.additional_images')), ''),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(PayloadJson, '$.response.additional_images')), '')
) AS product_image
FROM data_sales_janee_api_cache
WHERE UPPER(ApiName) IN ('FULLHOUSE_PRODUCT_API', 'JANEE_FULLHOUSE_PRODUCT_API')
AND UPPER(CacheKey) = '{cache_key}'
ORDER BY UpdatedAt DESC, FetchedAt DESC
LIMIT 1