Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my first post and not sure how to fix this auto format draft? Using SQL Server 2000. The table variable that is giving me this one error is '@SBLH' The following is the multi-select function producing this error:

USE [OfficeData]
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[WC_Period_Wage_Class]
(   
    @StartPED datetime, 
    @EndPED datetime,
    @StartPeriod datetime, 
    @EndPeriod datetime
)

RETURNS

@WcWages    TABLE   (
                    WorkDate datetime,
                    PED datetime,
                    Amount5213 money,
                    Amount5221 money,
                    Amount8810 money,
                    AmountOther money,
                    Reason nvarchar(50),
                    Reportable int
                    )
AS
BEGIN
DECLARE @SBLH TABLE (
                    Project nvarchar(10),
                    EmplID int,
                    Phase nvarchar(5),
                    Cost nvarchar(5),
                    Type nvarchar(2),
                    Amount money,
                    PayED datetime,
                    WkDate datetime,
                    RunTotal money
                    )

INSERT INTO @SBLH   (
                    Project,
                    EmplID,
                    Phase,
                    Cost,
                    Type,
                    Amount,
                    PayED,
                    WkDate
                    )

SELECT  job,
        emp,
        phs,
        cst,
        ty,
        Sum(glamt) AS Amt,
        payend_dt, 
        CASE
            WHEN Cast(day1 AS int) > 0  THEN DATEADD(d, -6, payend_dt)
            WHEN Cast(day2 AS int) > 0  THEN DATEADD(d, -5, payend_dt)
            WHEN Cast(day3 AS int) > 0  THEN DATEADD(d, -4, payend_dt)
            WHEN Cast(day4 AS int) > 0  THEN DATEADD(d, -3, payend_dt)
            WHEN Cast(day5 AS int) > 0  THEN DATEADD(d, -2, payend_dt)
            WHEN Cast(day6 AS int) > 0  THEN DATEADD(d, -1, payend_dt)
            WHEN Cast(day7 AS int) > 0  THEN payend_dt
                                        ELSE DATEADD(d, -2, payend_dt)
        END AS WDate

FROM    SB_PRLaborHist

WHERE   (co = '1') AND
        (glamt > 0)

GROUP BY    job,
            emp,
            phs,
            cst,
            ty,
            payend_dt,
            CASE
                WHEN Cast(day1 AS int) > 0 THEN DATEADD(d, -6, payend_dt)
                WHEN Cast(day2 AS int) > 0 THEN DATEADD(d, -5, payend_dt)
                WHEN Cast(day3 AS int) > 0 THEN DATEADD(d, -4, payend_dt)
                WHEN Cast(day4 AS int) > 0 THEN DATEADD(d, -3, payend_dt)
                WHEN Cast(day5 AS int) > 0 THEN DATEADD(d, -2, payend_dt)
                WHEN Cast(day6 AS int) > 0 THEN DATEADD(d, -1, payend_dt)
                WHEN Cast(day7 AS int) > 0 THEN payend_dt
                                           ELSE DATEADD(d, -2, payend_dt)
            END

HAVING  (payend_dt Between @StartPED And @EndPED)

ORDER BY    emp,
            WDate

-- Update the temporary table #EBLH with a running total reset for each employee

DECLARE @RunTotal   money,
        @EID        int

SET @RunTotal = 0
SET @EID = 0

UPDATE @SBLH

SET @RunTotal = RunTotal = CASE
                           WHEN EmplID = @EID
                           THEN @RunTotal + Amount
                           ELSE Amount
                           END,
    @EID = EmplID

-- Remove wages above the $36,000 cap and compile into the three WC classes
-- Error is caused by the following code:

INSERT INTO @WcWages    (
                        WorkDate,
                        PED,
                        Amount5221,
                        Amount5213,
                        Amount8810,
                        AmountOther,
                        Reason,
                        Reportable
                        )

SELECT  WkDate,
        PayED,
        Sum(CASE
            WHEN JC.JC_WC_Class_Code = '5221'
            THEN Amount
            ELSE 0
            END) AS Amt5221,
        Sum(CASE
            WHEN JC.JC_WC_Class_Code = '5213'
            THEN Amount
            ELSE 0
            END) AS Amt5213,
        Sum(CASE
            WHEN JC.JC_WC_Class_Code = '8810'
            THEN Amount
            ELSE 0
            END) AS Amt8810,
        Sum(CASE
            WHEN Len(JC.JC_WC_Class_Code) = 0
            THEN Amount
            ELSE 0
            END) AS AmtOther,
        CASE
            WHEN Len(JC.JC_WC_Class_Code) > 0
            THEN ''
            ELSE JC.JC_Phase_Descrip + '-' + JC.JC_Cost_Descrip
        END AS OtherReason,
        CASE
            WHEN RunTotal > 36000.00
            THEN 0
            ELSE 1
        END AS IsReportable

FROM   @SBLH

        LEFT JOIN JC_D_CodeMaster AS JC
               ON @SBLH.Type = JC.JC_Cost_Type
              AND @SBLH.Cost = JC.JC_Cost_No
              AND @SBLH.Phase = JC.JC_Phase_No
              AND @SBLH.Project = JC.Project_Number

        LEFT JOIN JC_D_List_WC_Codes AS WC
               ON JC.JC_WC_Class_Code = WC.JC_WC_Class_Code

GROUP BY    @SBLH.WkDate,
            @SBLH.PayED,
            CASE
                WHEN Len(JC.JC_WC_Class_Code) > 0
                THEN '' 
                ELSE JC.JC_Phase_Descrip + '-' + JC.JC_Cost_Descrip
            END,
            CASE
                WHEN RunTotal > 36000.00
                THEN 0
                ELSE 1
            END

HAVING  (@SBLH.WkDate Between @StartPeriod And @EndPeriod)

RETURN
END
GO

The error is the Insert code after the Update code. This should be one batch and the variable @SBLH should not be out of scope. I found on the internet references to the variable losing it's scope after an update. However, I have seen MS knowledge base examples showing a table variable used in a select statement after an update statement within a multi select function similar to my code above. I know it's something simple, but it is eluding me. This is the first multi select function I've written, so be patient with me.

Thanks for all the help.

share|improve this question

1 Answer 1

You can't use the table variable name as a column prefix in join/where clauses, you need to do:

FROM @slbh AS slbh
...
ON slbh.col = ...
share|improve this answer
    
Thanks, that was it. Now that I see it, it makes sense. I knew it was something simple, stupid me was trying to hard. –  Dean Lavell Sep 21 '11 at 0:39
2  
@DeanLavell If Aaron's answer fixed your problem, you can mark it as the accepted solution by clicking on the checkmark underneath his answer's score. –  Matt M Sep 21 '11 at 12:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.