0

I'm trying to create a query that pulls a certain item on a certain day . and also gives me the sum of Total store Inventory on hand, Total Distrisbution Center (DC ) On hand and On Order . I'm having issues joining all the tables. any ideas? 

select    
d.DAY_DATE,     
c.wic_nbr,    
c.wic_Desc,      
c.basic_prod_ind,    
sum(b.lo_ioh_units) "Total Store Inventory on Hand Units",   
sum(a.TOT_REG_IOH_UNITS+a.TOT_DISTR_IOH_UNITS) "Total DC Inventory on Hand Units",       
sum(a.TOT_REG_ordr_UNITS+a.TOT_DISTR_ordr_UNITS) "Total DC On Order Units "      
from MSS_OWNER.FCT_DA_DAY_DC_VEND_WIC a     
 Join fct_ioh_day_str_pln b   
 ON a.PROD_ID = b.PROD_ID   
JOIN dim_prod_wic c   
ON a.PROD_ID = c.PROD_ID   
JOIN dim_period d    
on a.PER_ID = d.PER_ID;    
Where   (a.TOT_REG_IOH_UNITS+a.TOT_DISTR_IOH_UNITS+a.TOT_REG_ordr_UNITS+a.TOT_DISTR_ordr_UNITS)>0    
and (b.lo_ioh_units)> 0     
and d.DAY_DATE = to_date ('01/24/2017','MM/DD/YYYY')    
and b.wic_nbr in ('955612')  

group by   
d.DAY_DATE,   
c.wic_nbr,   
c.wic_desc,   
c.basic_prod_ind
S3S
  • 24,809
  • 5
  • 26
  • 45
Chelsea Weber
  • 1
  • 1
  • 2
    show some results, show why the results are wrong and what you are wanting to see. you will get answers that way. – T McKeown Jan 25 '17 at 19:37
  • Isn't `to_date` an `Oracle` function? What RDBMS are you using? – SS_DBA Jan 25 '17 at 19:43
  • This is a Oracle. The result I got was Error: ORA-00972: identifier is too long (State:37000, Native Code: 3CC) – Chelsea Weber Jan 25 '17 at 19:48
  • Based on the error message, one of your identifiers is too long. I'm not sure how Oracle counts the length of an identifier, but `MSS_OWNER.FCT_DA_DAY_DC_VEND_WIC` is 32 characters and the Oracle limit is 30 characters. There's another question related to that here: http://stackoverflow.com/questions/3085562/ora-00972-identifier-is-too-long-alias-column-name – akousmata Jan 25 '17 at 19:57
  • select DAY_DATE, b.wic_nbr, b.wic_Desc, ops_dept_nbr, b.basic_prod_ind, sum(a.TOT_REG_IOH_UNITS+a.TOT_DISTR_IOH_UNITS) "DC OH HAND Units", -- This is the total on Hand. It includes Reg OH and Dist OH sum(a.TOT_REG_ordr_UNITS+a.TOT_DISTR_ordr_UNITS) "DC Dist OO " From MSS_OWNER.FCT_DA_DAY_DC_VEND_WIC a, dim_prod_wic b, dim_period c where a.PROD_ID = b.PROD_ID and a.PER_ID = c.PER_ID and DAY_DATE = to_date ('01/24/2017','MM/DD/YYYY') and – Chelsea Weber Jan 25 '17 at 20:00
  • (TOT_REG_IOH_UNITS+TOT_DISTR_IOH_UNITS+TOT_REG_ordr_UNITS+TOT_DISTR_ordr_UNITS)>0 --and ops_dept_nbr in ('126','135') and b.wic_nbr in ('955612') --and b.whse_prod_vendor_nbr in () group by DAY_DATE, b.wic_nbr, b.wic_desc, ops_dept_nbr, b.basic_prod_ind} – Chelsea Weber Jan 25 '17 at 20:03
  • I use MSS_OWNER.FCT_DA_DAY_DC_VEND_WIC a in that query and it works fine – Chelsea Weber Jan 25 '17 at 20:04

1 Answers1

0

Your identifiers "Total Store Inventory on Hand Units" and "Total DC Inventory on Hand Units" cannot be longer than 30 characters. Try to use something shorter such as "Ttl Str Invntry Hnd Unts".

Abdulgood89
  • 359
  • 2
  • 9