Post reply

SQL If statement finds same temp table

  • May 3, 2012 at 3:15 am

    #1482875

    Ok cool. Didn't think it's possible 🙂

    tx guys

  • May 3, 2012 at 3:25 am

    #1482876

    renvilo (5/3/2012)

    GilaMonster (5/3/2012)

    What's the default collation of your database?

    What's the default collation of TempDB?

    Database Default - Latin1_General_CI_AS

    Didn't set any for the TempDB

    Databases always have a default collation, no matter whether you set it or not. If you don't, they inherit the server default collation. For completeness, please check the TempDB default collation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 3, 2012 at 8:14 am

    #1483051

    Couldn't you use the following query to create your temp table instead of the complicated IF construct and two different temp tables?

    Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode

    into #TempProduction

    from ALLOCTNTIMESTAMP a Join

    MEASURETIMESTAMP m ON m.TSKey = a.TSKey join

    V_Locations l ON l.Source_Code = a.Location join

    #TempShift s ON s.ShiftCode = a.ShKey Join

    V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join

    V_Equipment e ON e.EquipmentCode = a.Equipment Join

    V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join

    V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code

    Where Measure_Description = case @Measure when 'TONS' then N'Tons' else N'Bags' end

    and ef.Equipment_Function_Description = 'Loading'

    The two queries were identical except for the value of Measure_Description.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply