Off IF...ELSE Behavior

  • I'll try to explain this in the simplest why I can, what I am really after is why the IF/ELSE code is acting this way.

    I have multiple IF/ELSE statements that end up inserting data into a temp table if the condition is met. When I run the code however, the INSERT INTO #temp statement seems to be running even in a false evaluation of IF/ELSE.

    Here is an abbreviated version of the code:

    BEGIN

    IF OBJECT_ID('tempdb.dbo.#AEMQuotaYTD') IS NOT NULL

    DROP TABLE #AEMQuotaYTD

    END

    BEGIN

    DECLARE @curMonth AS varchar(20)

    SET @curMonth = 'August, 2008'

    IF @curMonth = 'July, 2008'

    BEGIN

    SELECT

    Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    INTO #AEMQuotaYTD

    FROM tblRepQuota

    WHERE FiscalYear = '2009' AND RevType = 'AEM'

    AND FiscalMonth IN ('July')

    GROUP BY

    Alias,

    FiscalYear

    END

    ELSE IF @curMonth = 'August, 2008'

    BEGIN

    SELECT

    Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    INTO #AEMQuotaYTD

    FROM tblRepQuota

    WHERE FiscalYear = '2009' AND RevType = 'AEM'

    AND FiscalMonth IN ('July', 'August')

    GROUP BY

    Alias,

    FiscalYear

    END

    END

    When I run this, I get the following error:

    There is already an object named '#AEMQuotaYTD' in the database.

    It seems to me that the evaluation of the first statement actually creates the temp table even though the value is false. Can anyone help determine the cause?

    _______________________________
    [font="Tahoma"]Jody Claggett
    SQL Server Reporting Analyst
    [/font][/size]

  • Actually, the message is from the parser. After you run this and get that error you will notice that #AEMQuotaYTD does not really exist.

    Create an empty table and use insert into.

    BEGIN

    IF OBJECT_ID('tempdb.dbo.#AEMQuotaYTD') IS NOT NULL

    DROP TABLE #AEMQuotaYTD

    END

    -- even better if you define the columns yourself

    select top 0 * into #AEMQuotaYTD FROM tblRepQuota

    BEGIN

    DECLARE @curMonth AS varchar(20)

    SET @curMonth = 'August, 2008'

    IF @curMonth = 'July, 2008'

    BEGIN

    insert into #AEMQuotaYTD

    SELECT

    Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    -- INTO #AEMQuotaYTD

    FROM tblRepQuota

    WHERE FiscalYear = '2009' AND RevType = 'AEM'

    AND FiscalMonth IN ('July')

    GROUP BY

    Alias,

    FiscalYear

    END

    ELSE IF @curMonth = 'August, 2008'

    BEGIN

    insert into #AEMQuotaYTD

    SELECT

    Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    -- INTO #AEMQuotaYTD

    FROM tblRepQuota

    WHERE FiscalYear = '2009' AND RevType = 'AEM'

    AND FiscalMonth IN ('July', 'August')

    GROUP BY

    Alias,

    FiscalYear

    END

    END

  • My apologies, I should have been more specific.

    The first piece of code runs fine and effectively drops the table.

    When the second batch of code is run, that's when I get the error message. So in reality, you can ignore the first BEGIN/END code block.

    _______________________________
    [font="Tahoma"]Jody Claggett
    SQL Server Reporting Analyst
    [/font][/size]

  • Sorry... deleted this post... I made an error in the code. Repaired further on below...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Crud, I made a mistake on the fiscal year :blush:... don't use the code above... I'll post a correction soon.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... you want to do 12 "IF's" per query, Jody? No, no need for IF's here. You didn't post any test data so the outer query in the following is untested, but I did test everything else including the mini-date-table subquery. As usual, details are in the comments...

    Here we go...

    --===== If the temp table already exists, drop it.

    -- This should be commented out in a production environment

    IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

    DROP TABLE #AEMQuotaYTD

    --===== Declare and identify the "current month"

    DECLARE @CurMonth AS VARCHAR(20),

    @FiscalYear AS CHAR(4),

    @FiscalQtr AS INT

    --===== Set the variables

    SELECT @CurMonth = 'August, 2008',

    @FiscalYear = DATEDIFF(mm,'18990701',@CurMonth)/12+1900,

    @FiscalQtr = DATEDIFF(qq,'18990701',@CurMonth)%4 + 1

    --===== Sanity check

    SELECT @CurMonth,@FiscalYear,@FiscalQtr

    --===== Do the problem with no IF's

    SELECT Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    INTO #AEMQuotaYTD

    FROM dbo.tblRepQuota rq

    INNER JOIN

    (--==== Create a mini-date table that should probably be a permanent table

    SELECT TOP 12

    DATENAME(mm,DATEADD(mm,Number,'19000101')) AS MonthName,

    DATEPART(qq,DATEADD(mm,Number,'18990701')) AS FiscalQtr

    FROM Master.dbo.spt_Values WITH (NOLOCK) --Used as a Tally Table

    WHERE Type = 'P'

    AND Number < 12) fy

    ON rq.FiscalMonth = fy.MonthName

    WHERE rq.FiscalYear = @FiscalYear

    AND rq.RevType = 'AEM'

    AND fy.FiscalQtr = @FiscalQtr

    GROUP BY

    rq.Alias,

    rq.FiscalYear

    WHERE FiscalQtr = @FiscalQtr

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff, you've helped me out a few times in the past couple of weeks. I really appreciate it.

    I like the idea of not using 12 IF's, but it looks like your code sample is suming the data based on the fiscal quarter.

    What I am looking for is to sum the YTD quota up to the current fiscal month. I've provided some a sample data table in this code that will help everyone understand the objective.

    The @curMonth variable uses my fiscal calender where the current date is between fiscal month begin and fiscal month end. That code is in the sample as well, but commented out. In the sample that variable is set to 'October, 2008', which means the sum of the YTD quota should be $4000.

    --===== If the temp table already exists, drop it.

    -- This should be commented out in a production environment

    IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

    DROP TABLE #AEMQuotaYTD

    IF OBJECT_ID('TempDB.dbo.#tmpQuota') IS NOT NULL

    DROP TABLE #tmpQuota

    CREATE TABLE #tmpQuota

    (

    Aliasvarchar(20) NULL,

    FiscalYearINT NULL,

    FiscalMonthvarchar(20) NULL,

    RevTypevarchar(3) NULL,

    QuotaAmtINT

    )

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'July', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'August', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'September', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'October', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'November', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'December', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'January', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'February', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'March', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'April', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'May', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'June', 'AEM', '1000')

    --SELECT * FROM #tmpQuota

    --===== Declare and identify the "current month"

    DECLARE @CurMonth AS VARCHAR(20),

    @FiscalYear AS CHAR(4),

    @FiscalQtr AS INT

    --===== Set the variables

    SELECT @CurMonth = 'October, 2008' --(

    --SELECT FiscalMonth FROM vFiscalCalDays

    --WHERE GETDATE() BETWEEN FiscalMonthBegin and FiscalMonthEnd

    --)

    ,

    @FiscalYear = DATEDIFF(mm,'18990701',@CurMonth)/12+1900,

    @FiscalQtr = DATEDIFF(qq,'18990701',@CurMonth)%4 + 1

    --===== Sanity check

    --SELECT @CurMonth,@FiscalYear,@FiscalQtr

    --===== Do the problem with no IF's

    SELECT Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    INTO #AEMQuotaYTD

    FROM dbo.#tmpQuota rq

    INNER JOIN

    (

    SELECT TOP 12

    DATENAME(mm,DATEADD(mm,Number,'19000101')) AS MonthName,

    DATEPART(qq,DATEADD(mm,Number,'18990701')) AS FiscalQtr

    FROM Master.dbo.spt_Values WITH (NOLOCK) --Used as a Tally Table

    WHERE Type = 'P'

    AND Number < 12

    ) fy

    ON rq.FiscalMonth = fy.MonthName

    WHERE

    rq.FiscalYear = @FiscalYear

    AND rq.RevType = 'AEM'

    AND fy.FiscalQtr = @FiscalQtr

    GROUP BY

    rq.Alias,

    rq.FiscalYear

    select * from #AEMQuotaYTD

    _______________________________
    [font="Tahoma"]Jody Claggett
    SQL Server Reporting Analyst
    [/font][/size]

  • Jody Claggett (9/11/2008)


    ...but it looks like your code sample is suming the data based on the fiscal quarter.

    What I am looking for is to sum the YTD quota up to the current fiscal month.

    Correct... it's summing fiscal quarter data. Thank you for the clarification... I'll be back...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here you go, Jody...

    --===== If the temp table already exists, drop it.

    -- This should be commented out in a production environment

    IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

    DROP TABLE #AEMQuotaYTD

    IF OBJECT_ID('TempDB.dbo.#tmpQuota') IS NOT NULL

    DROP TABLE #tmpQuota

    CREATE TABLE #tmpQuota

    (

    Alias varchar(20) NULL,

    FiscalYear INT NULL,

    FiscalMonth varchar(20) NULL,

    RevType varchar(3) NULL,

    QuotaAmt INT

    )

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'July', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'August', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'September', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'October', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'November', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'December', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'January', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'February', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'March', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'April', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'May', 'AEM', '1000')

    INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'June', 'AEM', '1000')

    --SELECT * FROM #tmpQuota

    --===== Declare and identify the "current month"

    DECLARE @CurMonth AS VARCHAR(20),

    @FiscalYear AS CHAR(4),

    @FiscalMo AS INT

    --===== Set the variables

    SELECT @CurMonth = 'October, 2008' ,

    @FiscalYear = DATEDIFF(mm,'18990701',@CurMonth)/12+1900,

    @FiscalMo = DATEDIFF(mm,'18990701',@CurMonth)%12 + 1

    --===== Sanity check

    --SELECT @CurMonth,@FiscalYear,@FiscalMo

    --===== Do the problem with no IF's

    SELECT Alias,

    FiscalYear,

    SUM(QuotaAmt) AS QuotaAmt

    INTO #AEMQuotaYTD

    FROM dbo.#tmpQuota rq

    INNER JOIN

    (SELECT TOP 12

    DATENAME(mm,DATEADD(mm,Number,'19000101')) AS MonthName,

    MONTH(DATEADD(mm,Number,'18990701')) AS FiscalMonth

    FROM Master.dbo.spt_Values WITH (NOLOCK) --Used as a Tally Table

    WHERE Type = 'P'

    AND Number < 12) fm

    ON rq.FiscalMonth = fm.MonthName

    WHERE rq.FiscalYear = @FiscalYear

    AND rq.RevType = 'AEM'

    AND fm.FiscalMonth <= @FiscalMo

    GROUP BY

    rq.Alias,

    rq.FiscalYear

    --select * from #AEMQuotaYTD

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Excellent, worked exactly as I originally needed.

    Curious, I've never heard of spt_Values until now. What is it typically used for?

    _______________________________
    [font="Tahoma"]Jody Claggett
    SQL Server Reporting Analyst
    [/font][/size]

  • Think of spt_Values as a "config" table for SQL Server. I just happen to know that there's a set of sequential numbers from 0 to 2047 in SQL Server 2005 (0-255 in SQL Server 2000). I needed numbers from 0 to 11 to pull off what you wanted without using a bunch of IFs.

    The best thing to do, though, is to make a permanent table of numbers. Some folks call these a "Numbers" table... I call them "Tally" tables because they help count things. I wrote an article on how they work and how they can be used to eliminate some loops... please see the link below...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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