Re: different SQL approaches

In message <d562b81c.0410122307.1b297024_at_posting.google.com>, Ruslan Kogan <ruslan00_at_yahoo.com> writes
>Hi,
>
>Can someone please comment if the following two SQL approaches are the
>same (i beleive they are but currently they are returning different
>data sets). Also, which approach is better to use?? Many thanks!!!
>
>*************************************************
>APPROACH 1
>*************************************************
>select
> ch.wave_nbr,
> ph.store_nbr,
> ph.whse,
> ph.co,
> ph.div,
> sm.name,
> ch.carton_nbr,
> lh.dsp_locn,
> fn_rep_GetLocationDisplay(lh.whse, lh.locn_class, lh.area, lh.zone,
> lh.aisle, lh.bay, lh.lvl, lh.posn),
> sc.code_desc,
> im.size_desc,
> im.sku_desc,
> im.color_desc,
> cd.units_pakd,
> cd.user_id
>from
> carton_hdr ch,
> pkt_hdr ph,
> store_master sm,
> item_master im,
> carton_dtl cd,
> task_dtl td,
> sys_code sc,
> locn_hdr lh
>where
> ch.pkt_ctrl_nbr = ph.pkt_ctrl_nbr and
> ph.store_nbr = sm.store_nbr and
> cd.sku_id = im.sku_id and
> ch.carton_nbr = cd.carton_nbr and
> cd.carton_nbr = td.carton_nbr and
> cd.carton_seq_nbr = td.carton_seq_nbr and
> sc.rec_type = 'S' and
> sc.code_type = '502' and
> ch.stat_code = sc.code_id and
> ch.curr_locn_id(+) = lh.locn_id
>
>*************************************************
>APPROACH 2
>*************************************************
>SELECT
> ch.wave_nbr,
> ph.store_nbr,
> ph.whse,
> ph.co,
> ph.div,
> sm.name,
> ch.carton_nbr,
> lh.dsp_locn,
> fn_rep_GetLocationDisplay(lh.whse, lh.locn_class, lh.area,
>lh.zone, lh.aisle, lh.bay, lh.lvl, lh.posn),
> sc.code_desc,
> im.size_desc,
> im.sku_desc,
> im.color_desc,
> cd.units_pakd,
> cd.user_id
>FROM
> carton_hdr ch
>
>INNER JOIN pkt_hdr ph
> ON ch.pkt_ctrl_nbr = ph.pkt_ctrl_nbr
> INNER JOIN store_master sm
> ON ph.store_nbr = sm.store_nbr
>
>INNER JOIN sys_code sc
> ON ch.stat_code = sc.code_id
> and sc.rec_type = 'S'
> and sc.code_type = '502'
>
>INNER JOIN carton_dtl cd
> ON ch.carton_nbr = cd.carton_nbr
> INNER JOIN task_dtl td
> ON cd.carton_nbr = td.carton_nbr
> and cd.carton_seq_nbr = td.carton_seq_nbr
> INNER JOIN item_master im
> ON cd.sku_id = im.sku_id
>
>LEFT JOIN locn_hdr lh
> ON ch.curr_locn_id = lh.locn_id
>*******

They are equivalent. However, I would say that

> and sc.rec_type = 'S'
> and sc.code_type = '502'

belongs in the where clause and not in the join clause.

The second one is preferred as it is more portable whereas the first one will only work on oracle. The second one is also probably easier to read.

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>