?
Solved

Case When / Invalid Column Name..

Posted on 2003-10-24
15
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

771 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