variable usage in cast and column naming 1

[hedgracer]

Here is my code:

use actg

--NEED PARAMETERS FOR CURRENT MONTH AND NUMBER OF MONTHS
DECLARE @CurrMth NUMERIC(18,0);
SET @CurrMth = 9;

DECLARE @CurrMthName VARCHAR(3);


IF @CurrMth = 1
BEGIN
SET @CurrMthName = 'Jan'
END
ELSE if @CurrMth = 2
BEGIN
SET @CurrMthName = 'Feb'
END
ELSE IF @CurrMth = 3
BEGIN
SET @CurrMthName = 'Mar'
END
ELSE IF @CurrMth = 4
BEGIN
SET @CurrMthName = 'Apr'
END
ELSE IF @CurrMth = 5
BEGIN
SET @CurrMthName = 'May'
END
ELSE IF @CurrMth = 6
BEGIN
SET @CurrMthName= 'Jun'
END
ELSE IF @CurrMth = 7
BEGIN
SET @CurrMthName= 'Jul'
END
ELSE IF @CurrMth = 8
BEGIN
SET @CurrMthName= 'Aug'
END
ELSE IF @CurrMth = 9
BEGIN
SET @CurrMthName= 'Sep'
END
ELSE IF @CurrMth = 10
BEGIN
SET @CurrMthName= 'Oct'
END
ELSE IF @CurrMth = 11
BEGIN
SET @CurrMthName= 'Nov'
END
ELSE IF @CurrMth = 12
BEGIN
SET @CurrMthName= 'Dec'
END


DECLARE @NITable table
(
Reg Varchar(1),
Dept Varchar(3),
CurrentMonth Numeric(18,0),
CurrentYTD Numeric(18,0),
CurrentAvg Numeric(18,0),
PriorYTD Numeric(18,0),
PriorAvg Numeric(18,0)
)

Declare @NIGrouped Table
(
Reg Varchar(1),
Dept Varchar(3)
)

insert into @NIGrouped
(
Reg,
Dept
)
select Reg, Dept from Actg_FPG_Reg_All_Deps_Report_NI
group by Reg, Dept

insert into @NIGrouped
(
Reg,
Dept
)
select Reg, Dept from Actg_FPG_Reg_All_Deps_Report_2010
group by Reg, Dept

insert into @NITable
(
Reg,
Dept
)
select Reg, Dept from @NIGrouped
group by Reg, Dept



--Update to current month
update NITable
SET CurrentMonth = A.SumOf + @CurrMthName
From @NITable As NITable
Inner Join (
select [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
[Actg_FPG_Reg_All_Deps_Report_NI].dept,
CAST(SUM(@CurrMthName)AS NUMERIC(18,0)) As SumOf + @CurrMthName
from [Actg_FPG_Reg_All_Deps_Report_NI]
Group By [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
[Actg_FPG_Reg_All_Deps_Report_NI].dept
) As A
On NITable.Reg = A.Reg
And NITable.Dept = A.Dept

The problem I have is with the last update. The usage of @CurrMthName is not working and I have googled this thing to death to try to find out why. Can anyone help me on this? Any help is appreciated. The error message I get is that the SQL Server does not like the '+'.

 

[djj55]

Your problem is with
As SumOf + @CurrMthName
move the AS SumOf to the end of the line

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[hedgracer]

To djj55: What do you mean to the end of the line?

 

[gmmastros]

This line here:

CAST(SUM(@CurrMthName)AS NUMERIC(18,0)) As SumOf + @CurrMthName

I see a couple problems with this.

First, you are trying to SUM a varchar.

CAST(SUM([!]@CurrMthName[/!])AS NUMERIC(18,0)) As SumOf + @CurrMthName

You cannot use the SUM function on a varchar.

Second, the "As SumOf" appears to SQL Server as a column alias. Immediately following the column alias is the + symbol.

PS. The big If/Else If block at the top can be replaced with this:

--NEED PARAMETERS FOR CURRENT MONTH AND NUMBER OF MONTHS
DECLARE @CurrMth NUMERIC(18,0);
SET @CurrMth = 9;

DECLARE @CurrMthName VARCHAR(3);

Set @CurrMthName = Left(DateName(Month, DateAdd(Month, @CurrMth-1, 0)), 3)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hedgracer]

The two lines:

SET CurrentMonth = A.SumOf + @CurrMthName

CAST(SUM(@CurrMthName)AS NUMERIC(18,0))

@CurrMthName is meant to dynamically replace a column name depending on the number generated (i.e. 1 = Jan = column name is Jan). How would I go about doing this if I am not creating a varchar?

 

[gmmastros]

I see. @CurrMthName actually represents a column name.

The usual way of doing this would be to use dynamic SQL. This will be a bit difficult with your query because you are using table variable, which don't play nice with dynamic SQL.

Dynamic SQL is where you build a string in code and execute it, like this:

Declare @SQL VarChar(8000)

Set @SQL = 'Select Col1, Col2 From SomeTable Where Col3 = 7'
Exec (@SQL)

The problem is this.... The Exec command starts a new process. This new process doesn't know about the table variables.

Try this:

--Update to current month
update NITable
SET CurrentMonth = A.SumOf + @CurrMthName
From   @NITable As NITable
       Inner Join (
         select [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept,
                CAST(SUM(
                   Case @CurrMth When 1 Then Jan
                                 When 2 Then Feb
                                 When 3 Then Mar
                                 When 4 Then Apr
                                 When 5 Then May
                                 When 6 Then Jun
                                     (etc...)
                                 Else 0
                                 End
                   )AS NUMERIC(18,0)) As SumOf 
         from   [Actg_FPG_Reg_All_Deps_Report_NI] 
         Group By [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept
         ) As A
         On NITable.Reg = A.Reg
         And NITable.Dept = A.Dept

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hedgracer]

this code:


update NITable
SET CurrentMonth = A.SumOf + @CurrMthName
From @NITable As NITable
Inner Join (
select [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
[Actg_FPG_Reg_All_Deps_Report_NI].dept,
CAST(SUM(
CASE @CurrMth WHEN 1 THEN Jan
CASE @CurrMth WHEN 2 THEN Feb
CASE @CurrMth WHEN 3 THEN Mar
CASE @CurrMth WHEN 4 THEN Apr
CASE @CurrMth WHEN 5 THEN May
CASE @CurrMth WHEN 6 THEN Jun
CASE @CurrMth WHEN 7 THEN Jul
CASE @CurrMth WHEN 8 THEN Aug
CASE @CurrMth WHEN 9 THEN Sep
CASE @CurrMth WHEN 10 THEN Oct
CASE @CurrMth WHEN 11 THEN Nov
CASE @CurrMth WHEN 12 THEN Dec
ELSE 0
END
)AS NUMERIC(18,0)) AS SumOf
from [Actg_FPG_Reg_All_Deps_Report_NI]
Group By [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
[Actg_FPG_Reg_All_Deps_Report_NI].dept
) As A
On NITable.Reg = A.Reg
And NITable.Dept = A.Dept

is throwing the following error:

Msg 156, Level 15, State 1, Line 126
Incorrect syntax near the keyword 'CASE'.

The line throwing the error is the Feb line of the CASE.

 

[gmmastros]

try this:

update NITable
SET CurrentMonth = A.SumOf --+ @CurrMthName
From   @NITable As NITable
       Inner Join (
         select [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept,
                CAST(SUM(
                    CASE @CurrMth WHEN 1 THEN Jan
                                  WHEN 2 THEN Feb 
                                  WHEN 3 THEN Mar 
                                  WHEN 4 THEN Apr 
                                  WHEN 5 THEN May 
                                  WHEN 6 THEN Jun
                                  WHEN 7 THEN Jul 
                                  WHEN 8 THEN Aug 
                                  WHEN 9 THEN Sep 
                                  WHEN 10 THEN Oct 
                                  WHEN 11 THEN Nov 
                                  WHEN 12 THEN Dec 
                    ELSE 0
                    END
                )AS NUMERIC(18,0)) AS SumOf
         from   [Actg_FPG_Reg_All_Deps_Report_NI] 
         Group By [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept
         ) As A
         On NITable.Reg = A.Reg
         And NITable.Dept = A.Dept

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hedgracer]

In a previous post of yours you had this code:

Declare @SQL VarChar(8000)

Set @SQL = 'Select Col1, Col2 From SomeTable Where Col3 = 7'
Exec (@SQL)

Should I be adding this?

 

[gmmastros]

no. That was an explanation of what dynamic SQL is. You don't need it here, and it would be difficult to use it here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hedgracer]

Thanks George, this worked like a charm. I may have some more questions on another portion of this sql code later. Is that okay?

 

[gmmastros]

Is that okay?

Of course it's ok. [small]Will query for food.[/small]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom