|
whizzo1982
Starting Member
1 Post |
 Posted - 2015-02-05 : 06:20:22
|
| Hi, I'm somewhat of an SQL novice. I need to update this script (below) so that I can drag 'Shipped Date' into my BO report as an object. I think it needs to be in the select statement but I don't know how to do it! This is from a data provider created using free hand SQL. Grateful for an assistance ASAP... SELECT country_d, CASE WHEN country_d Not in ('GBR','IRL') THEN 'NON-UK-DELIVERY' WHEN country_d = 'IRL' THEN 'Ireland' WHEN shm_oline.postcode IS NULL THEN 'NO_POSTCODE' ELSE REPLACE (SUBSTR (shm_oline.postcode, 0, LENGTH (shm_oline.postcode) - 2 ), ' ', '' ) END AS "DEL_POSTCODE", SUM (shpd_qty) shqty, SUM (shpd_val) shvl FROM (SELECT a.order_id ord, a.line_id lid, a.sku_id skid, a.customer_id cuid, b.product_currency curr, SUBSTR (c.product_group, 0, 2) mmg, d.country country_d, d.postcode, SUM (a.qty_shipped) shpd_qty, SUM (b.product_price) shpd_val FROM shipping_manifest a, order_line b, sku c, order_header d WHERE a.line_id = b.line_id AND a.order_id = b.order_id AND a.order_id = d.order_id AND a.sku_id = c.sku_id AND TRUNC (a.shipped_dstamp) BETWEEN TRUNC (SYSDATE, 'dy') - 7 AND TRUNC (SYSDATE, 'dy') - 1 AND d.order_type <> 'CNC' GROUP BY a.order_id, a.line_id, a.sku_id, a.customer_id, b.product_currency, SUBSTR (c.product_group, 0, 2), d.inv_country, d.country, d.postcode) shm_oline WHERE cuid = '4500' GROUP BY country_d, postcode UNION SELECT a.country country_d, CASE WHEN a.country Not in ('GBR','IRL') THEN 'NON-UK-DELIVERY' WHEN a.country = 'IRL' THEN 'Ireland' WHEN a.postcode IS NULL THEN 'NO_POSTCODE' ELSE REPLACE (SUBSTR (a.postcode, 0, LENGTH (a.postcode) - 2), ' ', '' ) END AS "DEL_POSTCODE", CASE WHEN NVL (freight_cost, 0) = 0 THEN 0 ELSE 1 END shqty, freight_cost FROM order_header a WHERE TRUNC (a.shipped_date) BETWEEN TRUNC (SYSDATE, 'dy') - 7 AND TRUNC (SYSDATE, 'dy') - 1 AND a.status = 'Shipped' AND a.customer_id = '4500' AND a.order_type <> 'CNC' |
|
Basic SQL Select help