attributes.sql

SELECT
zp.zsku_parent,
a.attribute_id,
a.attribute_code,
a.attribute_name_en,
a.attribute_name_ar,
a.attribute_type,
a.attribute_namespace AS namespace_code,
a.is_multivalued,
a.is_localizable,
a.namespace_attribute_group_code,
zpa.value,
ao.attribute_option_name_en,
ao.attribute_option_name_ar,
ao.attribute_option_properties,
amuo.metric_unit_name_en,
amuo.metric_unit_option_properties
FROM noondwh.zsku_catexsp.zsku_parent zp
LEFT JOIN noondwh.zsku_catexsp.zsku_parent_attribute zpa USING (zsku_parent)
LEFT JOIN noondwh.zsku_catexsp.attribute a USING (attribute_id)
LEFT JOIN noondwh.zsku_catexsp.attribute_option ao ON (ao.attribute_id, ao.attribute_option_code) = (a.attribute_id, JSON_VALUE(zpa.value, '$.v'))
LEFT JOIN noondwh.zsku_catexsp.attribute_metric_unit_option amuo ON (amuo.attribute_id, amuo.metric_unit_code) = (a.attribute_id, JSON_VALUE(zpa.value, '$.v.metric_unit'))
WHERE a.attribute_namespace IN ('nooninstant', 'noon', 'zsku_fundamental', 'common_catalog')
AND a.attribute_code in ('shelf_life', 'keep_life', 'is_expirable', 'is_food', 'storage_condition_ds', 'storage_condition_central')
and zp.zsku_parent = 'Z9A0445205C7828D65686Z';