Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: Error when performing a case when task

    aaaaaaaaaaaaaaaaaaaaaaaaa
    Last edited by washingtondcmiss; 03-02-11 at 09:52.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not sure what would not be the issue.

    Syntax: The case statements are not in a select or where clause.
    Binding: Table or view V2 is not declared
    Logical: Jan_Plan will include results from January, October, November, and December.

    Is there a particular error you are facing? Maybe if you told us a little more about what you wanted to get, and what you have to work with?

  3. #3
    Join Date
    Mar 2011
    Posts
    4

    Error when performing a case when task.

    This is the format of the source view:
    Last edited by washingtondcmiss; 03-01-11 at 20:14.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the syntax above supposed to be in Informatica? Or is it in SQL Server? Flattening out records is generally a job for the reporting layer. Doing that in the SQL layer is an invitation to performance problems. What options does the reporting layer you are working with give you?

  5. #5
    Join Date
    Mar 2011
    Posts
    4

    Case when syntax error

    The syntax is supposed to be in SQL Server 2005. I
    Last edited by washingtondcmiss; 03-02-11 at 09:52.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, business usually deserves to get what business wants......good and hard. Here is part of what it looks like you want:
    Code:
    select 
    	v2.Sales_Boundary_Level ,
    	v2.Territory ,
    	v2.Region ,
    	v2.Area ,
    	v2.Division ,
    	v2.ProductID ,
    	sum(case when v2.months = '2010, Month 1' then v2.value else 0 end) as Jan_Plan,
    	sum(case when v2.months = '2010, Month 2' then v2.value else 0 end) as Feb_Plan,
    	sum(case when v2.months = '2010, Month 3' then v2.value else 0 end) as Mar_Plan,
    	sum(case when v2.months = '2010, Month 4' then v2.value else 0 end) as Apr_Plan,
    	sum(case when v2.months = '2010, Month 5' then v2.value else 0 end) as May_Plan,
    	sum(case when v2.months = '2010, Month 6' then v2.value else 0 end) as Jun_Plan,
    	sum(case when v2.months = '2010, Month 7' then v2.value else 0 end) as Jul_Plan,
    	sum(case when v2.months = '2010, Month 8' then v2.value else 0 end) as Aug_Plan,
    	sum(case when v2.months = '2010, Month 9' then v2.value else 0 end) as Sep_Plan,
    	sum(case when v2.months = '2010, Month 10' then v2.value else 0 end) as Oct_Plan,
    	sum(case when v2.months = '2010, Month 11' then v2.value else 0 end) as Nov_Plan,
    	sum(case when v2.months = '2010, Month 12' then v2.value else 0 end) as Dec_Plan
    from  [QL Sales Plan by Chair Overall] v2
    where v2.Months like '2010%'
    group by  v2.Sales_Boundary_Level ,
    	v2.Territory ,
    	v2.Region ,
    	v2.Area ,
    	v2.Division ,
    	v2.ProductID
    One of the major hangups is the months field. It won't convert easily with any of the date functions (namely DATENAME), so using the PIVOT operator is kind of out.
    Last edited by MCrowley; 03-01-11 at 17:33. Reason: Forgot the group by clause (oops)

  7. #7
    Join Date
    Mar 2011
    Posts
    4

    Error when performing a case when task.

    Thanks for your quick response. I inserted the table name into that query and this is what i executed:
    Last edited by washingtondcmiss; 03-02-11 at 09:53.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When you want to use an alias (V2) for a table name (dbo.BSCQueryLibrarySalesPlan), you first have to declare the link between those two.
    Add this to your code
    Code:
    ...
    from dbo.BSCQueryLibrarySalesPlan AS V2
    ....
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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