RE: Returning ITEM_NO from a Dimension table and a

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

RE: Returning ITEM_NO from a Dimension table and a

Author

Topic

Pilgrim007
Starting Member

2 Posts

Posted - 2009-08-18 : 07:55:42

Hi

On a particular period, some ITEM_NO's ,will not exist on one table (i.e. RTL_LOC_ITEM_DY).

However I would like to see the ITEM_NO's, which do not exist, and return the values (even if it is NULL or 0) for the measures (e.g. SOH_UNITS). I will use DIM_ITEM as the main table to get the ITEM_NO's.

I have tried several JOINT statements to DIM_ITEM (e.g. INNER, OUTER, LEFT OUTER, LEFT INNER), but still no luck.

select
b.item_no,
a.fin_year,
a.fin_week,
b.department,
a.soh_units

from
rtl_loc_item_dy a

inner join dim_item b
on b.item_no = a.item_no,

dim_control c,
dim_location l

where

b.item_no = a.item_no AND
a.fin_year = c.last_wk_year AND
a.fin_week = c.last_wk_week AND
l.location= a.location AND
b.department = 11 AND
a.fin_day = 7 AND
a.com_flag = 'Y' AND
b.rms_item_ind = 'Y' AND
l.chain_no=10 AND
l.loc_type_code='S';

boom boom pow

singularity
Posting Yak Master

153 Posts

Posted - 2009-08-18 : 09:12:29

Maybe this:


select
b.item_no,
a.fin_year,
a.fin_week,
b.department,
a.soh_units

from
dim_item b 

left join rtl_loc_item_dy a on b.item_no = a.item_no and a.fin_day = 7 AND a.com_flag = 'Y'
left join dim_control c on a.fin_year = c.last_wk_year AND a.fin_week = c.last_wk_week
left join dim_location l on l.location= a.location and l.chain_no = 10 AND l.loc_type_code = 'S'

where b.department = 11 and b.rms_item_ind = 'Y';

Pilgrim007
Starting Member

2 Posts

Posted - 2009-08-19 : 10:01:23

Thanks for your help...

however using the outer join on rtl_loc_item_dy is what is actually working better than the left joins you recemmending.

I asked some people why would they use a table like rtl_loc_item_dy, which is a fact table, instead using a dimenension table? The answer I got, was that "we wanna see" the measures which do not exist, to be returned.

for shizzle ma nizzle...lol

boom boom pow

singularity
Posting Yak Master

153 Posts

Posted - 2009-08-23 : 23:19:52

A left join is an outer join.

Would need some sample data and expected output...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources