Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Unanswered: Left Joins and Inline Views

    Hi,

    I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.

    Code:
    SELECT 01 as WHSE_ID
    	, A.On_Time
    	, B.Early
    	, C.Late
    	, (D.AVG_Duration / (A.On_Time + B.Early + C.Late))AS AVG_Duration
    	, E.DelDate
    	, F.*
    	FROM
    
    (SELECT COUNT(SDD_Status) AS On_Time
    	, SDD_Date as On_Time_Date
    	FROM SDD_Store_Delivery_Data_Table 
    	WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time'
    	AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
    	GROUP BY SDD_Date) a,
    
    (SELECT COUNT(SDD_Status) AS Early
    	,SDD_DATE As Early_Date
    	FROM SDD_Store_Delivery_Data_Table
    	WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early'
    	And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
    	GROUP BY SDD_Date) b,
    
    (SELECT COUNT(SDD_Status) AS Late
    	, SDD_Date As Late_Date
    	FROM SDD_Store_Delivery_Data_Table
    	WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late'
    	AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
    	GROUP BY SDD_Date) c,
    
    (SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration
    	, SDD_Date As Stay_Date
    	FROM SDD_Store_Delivery_Data_Table
    	WHERE SDD_Route LIKE '01%'
    	AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
    	GROUP BY SDD_Date) d,
    
    (SELECT DISTINCT(SDD_Date) AS DelDate
    	FROM SDD_Store_Delivery_Data_Table
    	WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
    	GROUP BY SDD_Date)e,
    
    (SELECT *
    	FROM WAREHOUSE_METRICS
    	WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
    	AND WHSE_ID = 2
    	GROUP BY MTRC_DTE
    	, MTRC_ID
    	, WHSE_ID
    	, INVN_LVL_CS_CNT
    	, INVN_LVL_DLLR
    	, INVN_LVL_PLLT_CNT
    	, RCV_CS_CNT
    	, SHP_CS_CNT
    	, SRVC_LVL_PCT
    	, SCRTCH_CNT
    	, DROP_CNT
    	, RPLNSH_CNT
    	, DMG_CNT
    	, RET_CNT
    	, PICK_CYC_CNT
    	, PICK_ERR_CNT
    	, RSV_CHK_CNT
    	, ERR_CNT
    	, DLY_CS_VRNC
    	, DLY_DLLR_VRNC
    	, CMB_THRUPUT
    	, DELAY_MINS_CNT
    	, DELAY_PCT
    	, UNLOAD_AVG
    	, LABOR_AVG
    	, SELECT_HR_CNT
    	, CRT_USERID
    	, CRT_DTE_TME
    	, UPD_USERID
    	, UPD_DTE_TME) f
    
    WHERE 	a.On_Time_Date = E.DelDate
    	AND B.Early_Date = E.DelDate
    	AND C.Late_Date = E.DelDate
    	AND D.Stay_Date = E.DelDate
    	AND F.MTRC_DTE = E.DelDate
    
    GROUP BY E.DelDate
    	, A.On_Time
    	, B.Early
    	, C.Late
    	, AVG_Duration
    	, A.On_Time_Date
    	, B.Early_Date
    	, C.Late_Date
    	, D.Stay_Date
    	, F.WHSE_ID
    	, F.INVN_LVL_CS_CNT
    	, F.INVN_LVL_DLLR
    	, F.INVN_LVL_PLLT_CNT
    	, F.RCV_CS_CNT
    	, F.SHP_CS_CNT
    	, F.SRVC_LVL_PCT
    	, F.SCRTCH_CNT
    	, F.DROP_CNT
    	, F.RPLNSH_CNT
    	, F.DMG_CNT
    	, F.RET_CNT
    	, F.PICK_CYC_CNT
    	, F.PICK_ERR_CNT
    	, F.RSV_CHK_CNT
    	, F.ERR_CNT
    	, F.DLY_CS_VRNC
    	, F.DLY_DLLR_VRNC
    	, F.CMB_THRUPUT
    	, F.DELAY_MINS_CNT
    	, F.DELAY_PCT
    	, F.UNLOAD_AVG
    	, F.LABOR_AVG
    	, F.SELECT_HR_CNT
    	, F.CRT_USERID
    	, F.CRT_DTE_TME
    	, F.UPD_USERID
    	, F.UPD_DTE_TME
    	, F.MTRC_ID
    	, F.MTRC_DTE
    Order By E.DelDate
    Please excuse the length of the code but one of the tables has a lot of columns.

    Can anyone tell me if it's possble to do a join on an inline view?

    Any help would be greatly appreciated.

    Thanks in advance,

    John
    Last edited by JODonnell; 01-12-05 at 15:34. Reason: Left out something

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, although I use:
    Code:
    SELECT 01 as WHSE_ID
    	, A.On_Time
    	, A.Early
    	, A.Late
    	, (A.AVG_Duration / (A.On_Time + A.Early + A.Late))AS AVG_Duration
    	, E.DelDate
    	, F.*
    	FROM (
       SELECT SDD_Date AS DelDate
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'On Time' THEN 1) AS On_Time
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Early' THEN 1) AS Early
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Late' THEN 1) AS Late
    ,     Sum(Cast(SDD_Stay AS NUMERIC)) AS AVG_Duration
          FROM SDD_Store_Delivery_Data_Table
          WHERE  SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
          GROUP BY SDD_Date) AS a,
    
    (SELECT *
    	FROM WAREHOUSE_METRICS
    	WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
    	AND WHSE_ID = 2
    	GROUP BY MTRC_DTE
    	, MTRC_ID
    	, WHSE_ID
    	, INVN_LVL_CS_CNT
    	, INVN_LVL_DLLR
    	, INVN_LVL_PLLT_CNT
    	, RCV_CS_CNT
    	, SHP_CS_CNT
    	, SRVC_LVL_PCT
    	, SCRTCH_CNT
    	, DROP_CNT
    	, RPLNSH_CNT
    	, DMG_CNT
    	, RET_CNT
    	, PICK_CYC_CNT
    	, PICK_ERR_CNT
    	, RSV_CHK_CNT
    	, ERR_CNT
    	, DLY_CS_VRNC
    	, DLY_DLLR_VRNC
    	, CMB_THRUPUT
    	, DELAY_MINS_CNT
    	, DELAY_PCT
    	, UNLOAD_AVG
    	, LABOR_AVG
    	, SELECT_HR_CNT
    	, CRT_USERID
    	, CRT_DTE_TME
    	, UPD_USERID
    	, UPD_DTE_TME) f
    
    WHERE 	F.MTRC_DTE
    GROUP BY E.DelDate
    	, A.On_Time
    	, B.Early
    	, C.Late
    	, AVG_Duration
    	, A.On_Time_Date
    	, B.Early_Date
    	, C.Late_Date
    	, D.Stay_Date
    	, F.WHSE_ID
    	, F.INVN_LVL_CS_CNT
    	, F.INVN_LVL_DLLR
    	, F.INVN_LVL_PLLT_CNT
    	, F.RCV_CS_CNT
    	, F.SHP_CS_CNT
    	, F.SRVC_LVL_PCT
    	, F.SCRTCH_CNT
    	, F.DROP_CNT
    	, F.RPLNSH_CNT
    	, F.DMG_CNT
    	, F.RET_CNT
    	, F.PICK_CYC_CNT
    	, F.PICK_ERR_CNT
    	, F.RSV_CHK_CNT
    	, F.ERR_CNT
    	, F.DLY_CS_VRNC
    	, F.DLY_DLLR_VRNC
    	, F.CMB_THRUPUT
    	, F.DELAY_MINS_CNT
    	, F.DELAY_PCT
    	, F.UNLOAD_AVG
    	, F.LABOR_AVG
    	, F.SELECT_HR_CNT
    	, F.CRT_USERID
    	, F.CRT_DTE_TME
    	, F.UPD_USERID
    	, F.UPD_DTE_TME
    	, F.MTRC_ID
    	, F.MTRC_DTE
    Order By E.DelDate
    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Thanks

    Thanks Pat,

    That looks better.

    John

  4. #4
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    One other thing

    It's throwing two errors in analyzer:

    Server: Msg 170, Level 15, State 1, Line 10
    Line 10: Incorrect syntax near ')'.
    Server: Msg 170, Level 15, State 1, Line 51
    Line 51: Incorrect syntax near 'f'.

    I'm not sure why it has a problem everything looks good.

    Thanks,

    John

  5. #5
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Can't get this to Parse

    Thanks again Pat,

    I am still having a problem exectuing this query.

    Quote Originally Posted by Pat Phelan
    Yes, although I use:
    Code:
    SELECT 01 as WHSE_ID
    	, A.On_Time
    	, A.Early
    	, A.Late
    	, (A.AVG_Duration / (A.On_Time + A.Early + A.Late))AS AVG_Duration
    	, E.DelDate
    	, F.*
    	FROM (
       SELECT SDD_Date AS DelDate
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'On Time' THEN 1) AS On_Time
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Early' THEN 1) AS Early
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Late' THEN 1) AS Late
    ,     Sum(Cast(SDD_Stay AS NUMERIC)) AS AVG_Duration
          FROM SDD_Store_Delivery_Data_Table
          WHERE  SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
          GROUP BY SDD_Date) AS a,
    
    (SELECT *
    	FROM WAREHOUSE_METRICS
    	WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
    	AND WHSE_ID = 2
    	GROUP BY MTRC_DTE
    	, MTRC_ID
    	, WHSE_ID
    	, INVN_LVL_CS_CNT
    	, INVN_LVL_DLLR
    	, INVN_LVL_PLLT_CNT
    	, RCV_CS_CNT
    	, SHP_CS_CNT
    	, SRVC_LVL_PCT
    	, SCRTCH_CNT
    	, DROP_CNT
    	, RPLNSH_CNT
    	, DMG_CNT
    	, RET_CNT
    	, PICK_CYC_CNT
    	, PICK_ERR_CNT
    	, RSV_CHK_CNT
    	, ERR_CNT
    	, DLY_CS_VRNC
    	, DLY_DLLR_VRNC
    	, CMB_THRUPUT
    	, DELAY_MINS_CNT
    	, DELAY_PCT
    	, UNLOAD_AVG
    	, LABOR_AVG
    	, SELECT_HR_CNT
    	, CRT_USERID
    	, CRT_DTE_TME
    	, UPD_USERID
    	, UPD_DTE_TME) f
    
    WHERE 	F.MTRC_DTE
    GROUP BY E.DelDate
    	, A.On_Time
    	, B.Early
    	, C.Late
    	, AVG_Duration
    	, A.On_Time_Date
    	, B.Early_Date
    	, C.Late_Date
    	, D.Stay_Date
    	, F.WHSE_ID
    	, F.INVN_LVL_CS_CNT
    	, F.INVN_LVL_DLLR
    	, F.INVN_LVL_PLLT_CNT
    	, F.RCV_CS_CNT
    	, F.SHP_CS_CNT
    	, F.SRVC_LVL_PCT
    	, F.SCRTCH_CNT
    	, F.DROP_CNT
    	, F.RPLNSH_CNT
    	, F.DMG_CNT
    	, F.RET_CNT
    	, F.PICK_CYC_CNT
    	, F.PICK_ERR_CNT
    	, F.RSV_CHK_CNT
    	, F.ERR_CNT
    	, F.DLY_CS_VRNC
    	, F.DLY_DLLR_VRNC
    	, F.CMB_THRUPUT
    	, F.DELAY_MINS_CNT
    	, F.DELAY_PCT
    	, F.UNLOAD_AVG
    	, F.LABOR_AVG
    	, F.SELECT_HR_CNT
    	, F.CRT_USERID
    	, F.CRT_DTE_TME
    	, F.UPD_USERID
    	, F.UPD_DTE_TME
    	, F.MTRC_ID
    	, F.MTRC_DTE
    Order By E.DelDate
    -PatP
    I removed what wasn't being used but I am still getting this error.

    Server: Msg 170, Level 15, State 1, Line 11
    Line 11: Incorrect syntax near ')'.
    Server: Msg 170, Level 15, State 1, Line 51
    Line 51: Incorrect syntax near 'f'.

    Here is the modified code:

    Code:
    SELECT 01 as WHSE_ID
    	, A.On_Time
    	, A.Early
    	, A.Late
    	, (A.AVG_Duration / (A.On_Time + A.Early + A.Late))AS AVG_Duration
    	, A.DelDate
    	, F.*
    	FROM (
       SELECT SDD_Date AS DelDate
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'On Time' THEN 1) AS On_Time
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Early' THEN 1) AS Early
    ,     Sum(CASE WHEN SDD_Route LIKE '01%' AND SDD_State = 'Late' THEN 1) AS Late
    ,     Sum(Cast(SDD_Stay AS NUMERIC)) AS AVG_Duration
          FROM SDD_Store_Delivery_Data_Table
          WHERE  SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
          GROUP BY SDD_Date) AS a,
    
    (SELECT MTRC_DTE
    	, WHSE_ID
    	, INVN_LVL_CS_CNT
    	, INVN_LVL_DLLR
    	, INVN_LVL_PLLT_CNT
    	, RCV_CS_CNT
    	, SHP_CS_CNT
    	, SRVC_LVL_PCT
    	, SCRTCH_CNT
    	, DROP_CNT
    	, RPLNSH_CNT
    	, DMG_CNT
    	, RET_CNT
    	, PICK_CYC_CNT
    	, PICK_ERR_CNT
    	, RSV_CHK_CNT
    	, ERR_CNT
    	, DLY_CS_VRNC
    	, DLY_DLLR_VRNC
    	, CMB_THRUPUT
    	, DELAY_MINS_CNT
    	, DELAY_PCT
    	, UNLOAD_AVG
    	, LABOR_AVG
    	, SELECT_HR_CNT
    	, CRT_USERID
    	, CRT_DTE_TME
    	, UPD_USERID
    	, UPD_DTE_TME
    	FROM WAREHOUSE_METRICS
    	WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
    	AND WHSE_ID = 2
    	) f
    
    GROUP BY A.DelDate
    	, A.On_Time
    	, A.Early
    	, A.Late
    	, AVG_Duration
    	, F.WHSE_ID
    	, F.INVN_LVL_CS_CNT
    	, F.INVN_LVL_DLLR
    	, F.INVN_LVL_PLLT_CNT
    	, F.RCV_CS_CNT
    	, F.SHP_CS_CNT
    	, F.SRVC_LVL_PCT
    	, F.SCRTCH_CNT
    	, F.DROP_CNT
    	, F.RPLNSH_CNT
    	, F.DMG_CNT
    	, F.RET_CNT
    	, F.PICK_CYC_CNT
    	, F.PICK_ERR_CNT
    	, F.RSV_CHK_CNT
    	, F.ERR_CNT
    	, F.DLY_CS_VRNC
    	, F.DLY_DLLR_VRNC
    	, F.CMB_THRUPUT
    	, F.DELAY_MINS_CNT
    	, F.DELAY_PCT
    	, F.UNLOAD_AVG
    	, F.LABOR_AVG
    	, F.SELECT_HR_CNT
    	, F.CRT_USERID
    	, F.CRT_DTE_TME
    	, F.UPD_USERID
    	, F.UPD_DTE_TME
    	, F.MTRC_ID
    	, F.MTRC_DTE
    Order By E.DelDate
    Thanks again,

    John

  6. #6
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Thanks Pat

    I realized that it just wanted and END for the case statements.

    Thanks Again,

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •