Solved

Case When / Invalid Column Name..

Posted on 2003-10-24
15
328 Views
Last Modified: 2012-05-04
I'm trying to create a nice complex query..and I've run into so many issues that at this point, i'm giving it to you people.

This is the query..it's formated for notpad..it may wrap here, if it does I'm sorry.

I get a 'Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Commission_Mult'.

When I try to run it...please help

-- Commission Query
SELECT      [First Name],
      [Last Name],
      [Product Name],
      [Paid On Date],
      [Draft Date],
      [Employees].[Name] As "Sale Rep",

-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'

      Eligibility = (CASE
            WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
            Else 'Un-Eligible'
      End),

-- Commission Multiplier: 0, 1, -1

      Commission_Mult = CASE
            WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
            WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
            ELSE 0
      END,

-- Commission Amount

      [Commission]*Commission_Mult As "Commission",

-- Status Name: Paid, Returned, Cancelled, Ordered

      Case
            WHEN [Status] = 1 THEN 'Ordered'
            WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
            WHEN [Status] > 2 THEN 'Cancelled'
            WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
      End As "Status_Name"

FROM AntiTel
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE      ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)
AND       ([Seen]=0)
0
Comment
Question by:KruleOne
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9615657
KruleOne,
> When I try to run it...please help

-- Commission Query
SELECT     [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Employees].[Name] As "Sale Rep",

-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'

     Eligibility = (CASE
          WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
          Else 'Un-Eligible'
     End),

-- Commission Multiplier: 0, 1, -1

     Commission_Mult = CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END,

-- Commission Amount

     [Commission]*CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END As "Commission",

-- Status Name: Paid, Returned, Cancelled, Ordered

     Case
          WHEN [Status] = 1 THEN 'Ordered'
          WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
          WHEN [Status] > 2 THEN 'Cancelled'
          WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
     End As "Status_Name"

FROM AntiTel
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE     ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)
AND      ([Seen]=0)


Hope that helps,
Dex*
0
 

Author Comment

by:KruleOne
ID: 9615671
U've done away with the point of the commission_mult.... I guess that would work, theres no way to do what I want other than that eh?
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9615689
KruleOne,

Also, if you don't need the Commission_Mult field for anything other than computing the Commission Field, then you can just omit this part:

     -- Commission Multiplier: 0, 1, -1
     Commission_Mult = CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END,

This is just a stab in the dark, but did you used to work with Microsoft Access?  I've seen lots of people who moved from Access to SQL make similiar mistakes.

Dex*
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 19

Expert Comment

by:Dexstar
ID: 9615723
KruleOne,

> U've done away with the point of the commission_mult.... I guess that would
> work, theres no way to do what I want other than that eh?

There might be, but I doubt they'd be more efficient.  Basically, in SQL Server, you can't use the value of a computed column to compute another computed column in the same query.  So instead of doing it in 2 steps, you do it in one.

Glad you got it working.

Enjoy,
Dex*
0
 

Author Comment

by:KruleOne
ID: 9615728
Yeah..well yes :p

How does that help? :p
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9615756
KruleOne,

> How does that help? :p

It was just out of curiousity.  :)
Will the updated query that I posted work out for you, or do you need some more help with it?  And if so, what's the issue?

Dex*
0
 

Author Comment

by:KruleOne
ID: 9615757
Actually it doesn't quite work...

Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'As'.

Thats at the As "Commission"
0
 

Author Comment

by:KruleOne
ID: 9615775
Oh ignore that last msg..thanks
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9615834
THANK GOODNESS!

I was tearing my hairs out trying to find the problem!  :)  So you got it working now?

Dex*
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9616087
you need to nest the query and then calculate the commission in the outer ring..

like this..

-- Commission Query
Select      [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Sales Rep],
 Commission_Mult,
 [Commission]*Commission_Mult As [Commission],
 [Status_Name]
 From (
SELECT     [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Employees].[Name] As "Sale Rep",

-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'

     Eligibility = (CASE
          WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
          Else 'Un-Eligible'
     End),

-- Commission Multiplier: 0, 1, -1

     Commission_Mult = CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END,

-- Commission Amount

--   [Commission]*Commission_Mult As "Commission",

-- Status Name: Paid, Returned, Cancelled, Ordered

     Case
          WHEN [Status] = 1 THEN 'Ordered'
          WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
          WHEN [Status] > 2 THEN 'Cancelled'
          WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
     End As "Status_Name"

FROM AntiTel
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE     ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)
AND      ([Seen]=0)
 ) as X

 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9616094
>>Oh ignore that last msg..thanks <<
Then now is a good time to award the points and close the question. And while you are at it, please maintain these old open questions:

1 04/27/2003 50 Singly Linked List Macros and Examples  Unlocked C Programming
2 07/31/2003 250 Data Bound Controls  Unlocked VB Databases

Thanks,
Anthony
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9616124
Lowfatspread,

> you need to nest the query and then calculate the commission in the outer ring..
> like this..

Actually, it won't work exactly the way you posted it.  You need to include the "Eligibility" field to the output of the outer query, and you need to include "Commission" in the inner query (so that it is available to the outer query for multiplication).

Try this:

-- Commission Query
Select      [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Sales Rep],
 Eligibility,
 Commission_Mult,
 [Commission]*Commission_Mult As [Commission],
 [Status_Name]
 From (
SELECT     [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Employees].[Name] As "Sale Rep",

-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'

     Eligibility = (CASE
          WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
          Else 'Un-Eligible'
     End),

-- Commission Multiplier: 0, 1, -1

     Commission_Mult = CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END,

-- Raw Commission Value
     Commission,

-- Status Name: Paid, Returned, Cancelled, Ordered

     Case
          WHEN [Status] = 1 THEN 'Ordered'
          WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
          WHEN [Status] > 2 THEN 'Cancelled'
          WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
     End As "Status_Name"

FROM AntiTel
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE     ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)
AND      ([Seen]=0)
 ) as X


Dex*
0
 

Author Comment

by:KruleOne
ID: 9616665
Wow..I just realised I needed to do what we did again :\


The line :

WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1

NEEDS to be:

WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 And [Eligibility] = 'Eligible' THEN 1

Since I can't use a calculated field in a calculation...any suggestions?
0
 
LVL 19

Accepted Solution

by:
Dexstar earned 125 total points
ID: 9616701
KruleOne,

Try this:

-- Commission Query
SELECT     [First Name],
     [Last Name],
     [Product Name],
     [Paid On Date],
     [Draft Date],
     [Employees].[Name] As "Sale Rep",

-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'

     Eligibility = (CASE
          WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
          Else 'Un-Eligible'
     End),

-- Commission Amount

     [Commission]*CASE
          WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 AND (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) THEN 1
          WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
          ELSE 0
     END As "Commission",

-- Status Name: Paid, Returned, Cancelled, Ordered

     Case
          WHEN [Status] = 1 THEN 'Ordered'
          WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
          WHEN [Status] > 2 THEN 'Cancelled'
          WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
     End As "Status_Name"

FROM AntiTel
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE     ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)
AND      ([Seen]=0)

Basically, apply the same solution to the new problem.  Viola.

Dex*
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9617442
>>Ordered

>>     Case
>>          WHEN [Status] = 1 THEN 'Ordered'
>>          WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
>>          WHEN [Status] > 2 THEN 'Cancelled'
>>          WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
>>     End As "Status_Name"

can be written as

 Case When [Status] > 2 then 'Cancelled'
         When [status] = 1 then 'Ordered'
         when [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
 End as [Status_Name]        
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hyperlink data type in SQL 3 28
Connecting to multiple databases to create a Dashboard 5 26
Help in Bulk Insert 9 35
T-SQL:  Collapsing 9 25
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question