Hello Friends,

How can we write a better query,

SELECT DISTINCT
a.item_code,
a.expiry_yn,
b.alpha_code,
b.item_class_code,
b.short_desc item_desc,
c.short_desc item_class_desc,
a.mfg_base_qty,
a.machine_code,
a.min_stk_qty,
a.max_stk_qty,
b.long_desc,
b.ps_item_yn,
b.sole_source_yn,
b.manufacturer_id,
b.gen_uom_code,
b.unit_cost,
d.short_desc uom_desc,
e.short_name manufacture_desc,
a.spare_part_id,
a.mfg_item_yn,
a.medical_item_yn,
a.abc_class_code,
a.trade_id_applicable_yn ,
a.batch_id_applicable_yn
FROM
st_item a,
mm_item b,
mm_item_class c,
am_uom d ,
am_manufacturer e,
mm_trade_name_for_item f,
am_trade_name g,
st_item_store h
WHERE
a.item_code=b.item_code
AND b.ITEM_CLASS_CODE=c.ITEM_CLASS_CODE(+)
AND b.gen_uom_code=d.uom_code(+)
AND b.manufacturer_id=e.manufacturer_id(+)
AND a.item_code=f.item_code(+)
AND f.trade_id=g.trade_id(+)
AND a.item_code=h.item_code(+)
AND UPPER(a.item_code) LIKE UPPER('%'||'%')
AND UPPER(b.short_desc) LIKE UPPER('%'||'%')
AND UPPER(b.alpha_code) LIKE UPPER('%'||'%')
AND UPPER(NVL(a.machine_code,'%')) LIKE UPPER('%'||'%')
AND UPPER(NVL(a.spare_part_id,'%')) LIKE UPPER('%'||'%')
AND UPPER(NVL(f.trade_id,'%')) LIKE UPPER('%'||'%')
AND UPPER(NVL(g.short_name,'%')) LIKE UPPER('%'||'%')
AND NVL(a.abc_class_code,'%') LIKE DECODE('%','','%','%')
AND a.medical_item_yn LIKE DECODE('%','A','%','%')
AND b.ps_item_yn LIKE DECODE('%','A','%','%')
AND b.sole_source_yn LIKE DECODE('%','A','%','%')
AND NVL(b.manufacturer_id,'%')LIKE DECODE('%','','%','%')
AND a.mfg_item_yn LIKE DECODE('%','A','%','%')
AND NVL(b.ITEM_CLASS_CODE,'%')LIKE DECODE('%','','%','%')
AND NVL(b.ITEM_ANAL1_CODE,'%')LIKE DECODE('%','','%','%')
AND NVL(b.ITEM_ANAL2_CODE,'%')LIKE DECODE('%','','%','%')
AND NVL(b.ITEM_ANAL3_CODE,'%')LIKE DECODE('%','','%','%')
AND a.expiry_yn LIKE DECODE('%','A','%','%')
AND NVL(h.store_code,'%') LIKE UPPER('%'||'%');


your comments are highly appreciated,

Thanks in Advance.

Fareed
==============================================================

Oracle LazyDBA home page