Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: Stored procedure returning too few rows

    I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.

    I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.

    The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.

    The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.

    Thanks for any help in advance,
    TimC

    SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message
    INTO #EVENT_FILTERED
    FROM EVENT
    WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)

    SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE,
    CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH,
    CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC,
    CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET,
    CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED,
    CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED,
    CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE,
    EVENT.TIME_STAMP AS IC_Set,
    EVENT_1.TIME_STAMP AS Cycle_Started,
    EVENT_2.TIME_STAMP AS Leak_Test_Done,
    EVENT_3.TIME_STAMP AS End_N2_PrePurge,
    EVENT_4.TIME_STAMP AS Heating_Complete,
    EVENT_5.TIME_STAMP AS Split_Temp_Met,
    EVENT_6.TIME_STAMP AS End_N2_Final_Purge,
    EVENT_7.TIME_STAMP AS Inner_Cover_Removed,
    EVENT_8.TIME_STAMP AS Cycle_Complete,
    EVENT_9.TIME_STAMP AS Post_Purge_Time_Met

    FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN
    #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE

    WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND
    (EVENT_1.MESSAGE LIKE '%Cycle Started%') AND
    (EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND
    (EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND
    (EVENT_4.MESSAGE LIKE '%Heating Complete%') AND
    (EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND
    (EVENT_6.MESSAGE LIKE '%End N2 Final%') AND
    (EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND
    (EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND
    (EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND
    (CHARGE_INFO.TIME_STAMP >= @StartDate) AND
    (CHARGE_INFO.TIME_STAMP < @EndDate)

    ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Referencing a left-joined table in the Where clause negates the purpose of left-joining (unless you also allow for the possibility of NULLs in your criteria). A better method is to filter in the in your join, like this:

    FROM dbo.CHARGE_INFO CHARGE_INFO
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE AND EVENT_1.MESSAGE LIKE '%Cycle Started%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE AND EVENT_2.MESSAGE LIKE '%Leak Test Done%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE AND EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE AND EVENT_4.MESSAGE LIKE '%Heating Complete%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE AND EVENT_5.MESSAGE LIKE '%Split Temp Met%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE AND EVENT_6.MESSAGE LIKE '%End N2 Final%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE AND EVENT_7.MESSAGE LIKE '%Inner Cover Removed%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE AND EVENT_8.MESSAGE LIKE '%Cycle Complete%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE AND EVENT_9.MESSAGE LIKE '%Post Purge Time Met%'
    LEFT OUTER JOIN #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE AND EVENT.MESSAGE LIKE '%Inner Cover Set%'
    WHERE CHARGE_INFO.TIME_STAMP >= @StartDate AND CHARGE_INFO.TIME_STAMP < @EndDate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2003
    Posts
    22

    Smile

    That was it. I figured it was a stupid oversight like that. I haven't worked too much with joins, so I wasn't sure how to go about fixing it.

    Thanks a lot for your help!
    TimC

Posting Permissions

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