Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> different SQL approaches

different SQL approaches

From: Ruslan Kogan <ruslan00_at_yahoo.com>
Date: 13 Oct 2004 00:07:41 -0700
Message-ID: <d562b81c.0410122307.1b297024@posting.google.com>


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

Received on Wed Oct 13 2004 - 02:07:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US