Untitled
unknown
plain_text
a year ago
1.5 kB
4
Indexable
INSERT INTO dds.fct_product_sales (product_id, order_id, "count", price, total_sum, bonus_payment, bonus_grant) with sub_t as (select json_array_elements(object_value::json -> 'order_items' ) ->> 'id' as product_id, REPLACE((object_value::json -> '_id'):: varchar, '"', '') as order_id, (json_array_elements(object_value::json -> 'order_items' ) ->> 'quantity')::int as quantity, (json_array_elements(object_value::json -> 'order_items' ) ->> 'price')::numeric(19, 5) as price, (json_array_elements(object_value::json -> 'order_items' ) ->> 'price')::numeric(19, 5) * (json_array_elements(object_value::json -> 'order_items' ) ->> 'quantity')::int as total_sum, (object_value::json -> 'bonus_grant')::varchar::numeric(19, 5) as bonus_grant, (object_value::json -> 'bonus_payment')::varchar::numeric(19, 5) as bonus_payment from stg.ordersystem_orders) select dmp.id, do2.id, tt.quantity as "count", tt.price, tt.total_sum, tt.bonus_payment, tt.bonus_grant from sub_t as tt left join dds.dm_products as dmp on dmp.product_id = tt.product_id left join dds.dm_orders as do2 on do2.order_key = tt.order_id full join dds.fct_product_sales as fps on fps.product_id = dmp.id and fps.order_id = do2.id where fps.order_id is null;
Editor is loading...
Leave a Comment