Case When / Invalid Column Name..

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)
KruleOneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
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
KruleOneAuthor Commented:
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
DexstarCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DexstarCommented:
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
KruleOneAuthor Commented:
Yeah..well yes :p

How does that help? :p
0
DexstarCommented:
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
KruleOneAuthor Commented:
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
KruleOneAuthor Commented:
Oh ignore that last msg..thanks
0
DexstarCommented:
THANK GOODNESS!

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

Dex*
0
LowfatspreadCommented:
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
Anthony PerkinsCommented:
>>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
DexstarCommented:
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
KruleOneAuthor Commented:
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
DexstarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.