61 lines
2.7 KiB
SQL
61 lines
2.7 KiB
SQL
-- Databricks notebook source
|
|
-- =============================================================================
|
|
-- Purpose : Standardize GND configuration codes (zero-padding) so join keys
|
|
-- line up across the market configuration tables. Kept on the DWD
|
|
-- config tables themselves (same targets as legacy).
|
|
-- Source : dwd.dwd_gnd_ims_tblbrandratio,
|
|
-- dwd.dwd_gnd_tblmarket_bymonth,
|
|
-- dwd.dwd_gnd_ims_tblkeycompetitor,
|
|
-- dwd.dwd_gnd_ims_tblbrandtype
|
|
-- Target : dwd.dwd_gnd_ims_tblbrandratio (UPDATE),
|
|
-- dwd.dwd_gnd_tblmarket_bymonth (UPDATE),
|
|
-- dwd.dwd_gnd_ims_tblkeycompetitor (UPDATE),
|
|
-- dwd.dwd_gnd_ims_tblbrandtype (UPDATE)
|
|
-- Grain : N/A -- in-place column updates, row grain unchanged.
|
|
-- Write mode : In-place UPDATE (idempotent padding).
|
|
-- Replaces : Code-padding block of legacy CHPA/01 dwd_update.sql
|
|
-- (time-window defaults moved to
|
|
-- 05_normalize_gnd_time_windows.sql).
|
|
-- Consumers : IMS market config refresh, DWS pack-property/market and other
|
|
-- jobs joining the four updated IMS configuration tables.
|
|
-- Notes : Padding rules and literals preserved exactly:
|
|
-- - Pack_Code/PACK_COD: left-padded with zeros to 12 digits when
|
|
-- the code starts with a digit, otherwise left unchanged.
|
|
-- - Product_Code: unconditional 9-digit left zero-pad.
|
|
-- - Molecule_Code/CMPS_COD: unconditional 6-digit left zero-pad.
|
|
-- Must run before the IMS market snapshot and DWS consumers.
|
|
-- =============================================================================
|
|
|
|
-- COMMAND ----------
|
|
|
|
UPDATE dwd.dwd_gnd_ims_tblbrandratio
|
|
SET
|
|
PACK_COD = if(CAST(PACK_COD AS string) REGEXP '^[0-9]', right(concat('000000000000', CAST(PACK_COD AS string)), 12), CAST(PACK_COD AS string)),
|
|
CMPS_COD = RIGHT(concat('000000', CAST(CMPS_COD AS string)), 6)
|
|
;
|
|
|
|
-- COMMAND ----------
|
|
|
|
UPDATE dwd.dwd_gnd_tblmarket_bymonth
|
|
SET
|
|
Pack_Code = if(Pack_Code REGEXP '^[0-9]', right(concat('000000000000', Pack_Code), 12), Pack_Code),
|
|
Product_Code = RIGHT(concat('000000000', Product_Code), 9),
|
|
Molecule_Code = RIGHT(concat('000000', Molecule_Code), 6)
|
|
;
|
|
|
|
-- COMMAND ----------
|
|
|
|
UPDATE dwd.dwd_gnd_ims_tblkeycompetitor
|
|
SET
|
|
Pack_Code = if(Pack_Code REGEXP '^[0-9]', right(concat('000000000000', Pack_Code), 12), Pack_Code),
|
|
Product_Code = RIGHT(concat('000000000', Product_Code), 9),
|
|
Molecule_Code = RIGHT(concat('000000', Molecule_Code), 6)
|
|
;
|
|
|
|
-- COMMAND ----------
|
|
|
|
UPDATE dwd.dwd_gnd_ims_tblbrandtype
|
|
SET
|
|
PACK_COD = if(PACK_COD REGEXP '^[0-9]', right(concat('000000000000', PACK_COD), 12), PACK_COD)
|
|
;
|