Yikes, obfuscation abounds. I just woke up, so maybe I'm missing
something, but it appears that all of these do nothing?

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('%'||'%');

Can't these be removed?

HTH.


-----Original Message-----
From: Fareed [mailto:[Email Address Removed]
Sent: Saturday, May 31, 2003 5:09 AM
To: LazyDBA.com Discussion
Subject: Can this query be improved.


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 documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page