用了DISTINCT ,数据也重复

原因

检查方法,不能偷懒,一个个表去关联关系,即使很麻烦

[@more@]

1.数据存在空格 -- 可惜我不是这种情况

2.DATE类型数据为秒存在差异

SELECT distinct gl.batch_number, h.order_number, h.order_number,
h.member_name || ' ' || cm.member_title, cm.member_num,
tava.NAME || tavb.NAME || tavc.NAME || cm.address addr, cm.postalcode,
SUBSTR (cm.postalcode, 1, 1) p1, SUBSTR (cm.postalcode, 2, 1) p2,
SUBSTR (cm.postalcode, 3, 1) p3, SUBSTR (cm.postalcode, 4, 1) p4,
SUBSTR (cm.postalcode, 5, 1) p5, SUBSTR (cm.postalcode, 6, 1) p6,
cm.primary_tel || ' ' || cm.mobile_tel || '/' || cm.second_tel tel,
msi.segment1
|| (SELECT fis.concatenated_segment_delimiter
FROM fnd_id_flex_structures_vl fis
WHERE fis.id_flex_structure_name = 'Item Catalogs')
|| msi.segment2
|| (SELECT fis.concatenated_segment_delimiter
FROM fnd_id_flex_structures_vl fis
WHERE fis.id_flex_structure_name = 'Item Catalogs')
|| msi.segment3 sku_num,
l.reserve_qty, msi.description, slp.meaning, slp.attribute1,
trunc(pb.picked_date), hl_new.location_code new_location,
hl_old.location_code old_location, hl_old.postal_code,
fl.meaning || hl_old.address_line_1,
SUBSTR (hl_old.postal_code, 1, 1) p1o,
SUBSTR (hl_old.postal_code, 2, 1) p2o,
SUBSTR (hl_old.postal_code, 3, 1) p3o,
SUBSTR (hl_old.postal_code, 4, 1) p4o,
SUBSTR (hl_old.postal_code, 5, 1) p5o,
SUBSTR (hl_old.postal_code, 6, 1) p6o, hl_new.telephone_number_1,
hl_old.ece_tp_location_code, l.channel_price - base_price zhekou,
h.order_date, l.base_price,
NVL (h.coupon_amount, 0)
+ NVL (h.voucher_amount, 0)
+ NVL (h.redeemable_amount, 0)
+ NVL (h.refundable_amount, 0) zhengquan,
NVL (h.actual_freight, 0), gl.goods_number, oh.attribute14
FROM tvsn_order_headers_all h,
tvsn_order_lines_all l,
oe_order_headers_all oh,
oe_order_lines_all ol,
tvsn_oe_goods_label_all gl,
tvsn_customer_member_all cm,
mtl_system_items_b msi,
tvsn_pmp_lookups slp,
tvsn_oe_pick_batch_all pb,
hr_locations_all hl_old,
hr_locations_all hl_new,
tvsn_area_all tava,
tvsn_area_all tavb,
tvsn_area_all tavc,
fnd_lookup_values fl,
wsh_carriers_v c,
wsh_carrier_services_v s
WHERE h.order_header_id = l.order_header_id
AND oh.header_id = ol.header_id

AND h.order_number = oh.order_number
AND TO_CHAR (h.order_header_id) = oh.orig_sys_document_ref
AND TO_CHAR (l.order_line_id) = ol.orig_sys_line_ref

AND gl.oe_header_id = oh.header_id

AND cm.cust_member_id = h.customer_id

AND msi.inventory_item_id = l.sku_id

AND pb.oe_header_id = gl.oe_header_id

AND pb.batch_number = gl.batch_number

AND slp.lookup_type = 'TVSN_SALES_CHANNEL'
AND slp.lookup_code = l.sales_source
AND slp.attribute1 = hl_new.location_id

AND cm.province_area_id = tava.area_id(+)
AND cm.city_area_id = tavb.area_id(+)
AND cm.district_area_id = tavc.area_id(+)
AND hl_new.ship_to_location_id = hl_old.location_id
AND NVL (gl.cancel_flag, 'N') != 'Y'
AND NVL (gl.join_flag, 'N') = 'Y'
AND fl.lookup_type = 'CN_PROVINCE'
AND fl.enabled_flag = 'Y'
AND fl.lookup_code = hl_old.town_or_city
AND fl.LANGUAGE = 'ZHS'
AND c.carrier_id = s.carrier_id
AND s.carrier_service_id = gl.ship_method_id
-- AND c.freight_code = 'CP'
--and s.ship_method_meaning='CP'
AND msi.organization_id = :p_org_id
AND oh.order_number >= NVL (:p_order_number_f, oh.order_number)
AND oh.order_number <= NVL (:p_order_number_t, oh.order_number)
AND s.carrier_service_id = NVL (:p_carrier_id, s.carrier_service_id)
AND pb.batch_id = NVL(:p_batch_id, pb.batch_id)

这里面的PB.PICKED_DATE

PICK_BATCH_ID BATCH_NUMBER DB_SESSION_ID INVALID_FLAG UNABLED_PICKED_FLAG PICKED_DATE BATCH_ID SHIP_METHOD_ID OE_HEADER_ID OE_LINE_ID OE_LINE_QUANTITY DELIVER_NUMBER INVENTORY_ITEM_ID LOCATOR_IN_ID LOCATOR_OUT_ID LOCATOR_CONTROL_NUMBER PICKED_FLAG UNPICK_FLAG IMPORT_FLAG ERRORS_INFO ORG_ID CREATION_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 REQUEST_ID PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE

41 4 264310 N N 2007-06-05 4:45:02 PM 42 1 1090 1081 1 2018 3962 119 4746 Y Y 101 2007-06-05 4:45:00 PM 1112 1112 2007-06-05 4:46:07 PM
42 4 264310 N N 2007-06-05 4:45:03 PM 42 1 1090 1080 1 2017 1082 3726 4746 Y Y 101 2007-06-05 4:45:00 PM 1112 1112 2007-06-05 4:46:06 PM
43 4 264310 N N 2007-06-05 4:45:03 PM 42 1 1090 1079 1 2016 4377 4554 4746 Y Y 101 2007-06-05 4:45:00 PM 1112 1112 2007-06-05 4:46:05 PM

select pb.*
from tvsn_oe_pick_batch_all pb ,
oe_order_headers_all oh
where pb.OE_HEADER_ID=oh.header_id
and oh.order_number=10000027

其中挑库运行的日期就差那么一秒,

2007-06-05 4:45:02

2007-06-05 4:45:03

导致我里面选出重复的数据

所以说用日期的时候用TRUNC 是个好习惯

而空值可能存在,用NVL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-917917/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-917917/