Solved

Case When / Invalid Column Name..

Posted on 2003-10-24
15
325 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now