• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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)
0
KruleOne
Asked:
KruleOne
  • 7
  • 5
  • 2
  • +1
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now