CODEWITHSUNDEEP
sql-server
Bill H.
Bill H.

Reputation: 37

MS SQL Invalid Column Name

Getting an "Invalid Column Name" error when including the following in my PHP MSSQL Select statement ...

IIf([PD_Mill_Start].[Job Name] Like '%glass%' Or [PD_Mill_Start].[Job Name] Like '%hinge%' Or [PD_Mill_Start].[Job Name] Like '%weather%' Or [PD_Mill_Start].[Job Name] Like '%molding%' Or [PD_Mill_Start].[Style] Like '%N/A%' Or [PD_Mill_Start].[Interior or Exterior] Like '%other%',[Batch Door Count],0) AS [Del Other Qty],

The whole Select statement is as follows in case it helps ...

SELECT PD_Mill_Start.[Order Number], PD_Mill_Start.[Job Name], PD_Mill_Start.[Mill Start], PD_Mill_Start.[Mill Start] AS [Mill Start To Min], PD_Mill_Start.[Date Shipped], PD_Mill_Start.[Stain Grade/Paint Grade] AS Grade, PD_Delivery_Schedule.[Ship To Verified], PD_Delivery_Schedule.[Mill Start Sent Date], IIf([Date Shipped] Is Not Null,[Date Shipped],IIf([PD_Delivery_Schedule].[Departure Date] Is Not Null,[PD_Delivery_Schedule].[Departure Date],IIf([PD_Mill_Start].[Promised Date] Is Not Null,[PD_Mill_Start].[Promised Date],' '))) AS [Forecast Date], PD_Mill_Start.[Date Entered], PD_Mill_Start.[Promised Date], PD_Mill_Start.Style, PD_Mill_Start.[Batch Door Count], PD_Mill_Start.[Batch Jamb Count], PD_Mill_Start.[Interior or Exterior], IIf(SUBSTRING([PD_Mill_Start].[Interior or Exterior],1,1) Like '%i%','Interior',IIf(SUBSTRING([PD_Mill_Start].[Interior or Exterior],1,1) Like '%e%','Exterior','UnKnown')) AS [Rpt Int or Ext], PD_Mill_Start.Status, PD_Delivery_Schedule.Driver, PD_Delivery_Schedule.[Departure Date], PD_Delivery_Schedule.[Departure Time], PD_Delivery_Schedule.[Delivery Sequence], PD_Delivery_Schedule.[Delivery Date/Time], PD_Delivery_Schedule.Truck, IIf([PD_Delivery_Schedule].[Special Delivery Inst]>' ',[PD_Delivery_Schedule].[Special Delivery Inst],[PD_Mill_Start].[Special Delivery Inst]) AS [Special Delivery Inst], PD_Delivery_Schedule.[COD Over Ride], PD_Mill_Start.Batch, PD_Mill_Start.[Stain Grade/Paint Grade], PD_Mill_Start.Species, PD_Mill_Start.[Stave Core], PD_Delivery_Schedule.[Mill Start Sent], PD_Delivery_Schedule.[Mill Start Confirmed], PD_Mill_Start.[Last Of Door Desc], PD_Mill_Start.[Ship Via], IIf([PD_Mill_Start].[Job Name] Like '%glass%' Or [PD_Mill_Start].[Job Name] Like '%hinge%' Or [PD_Mill_Start].[Job Name] Like '%weather%' Or [PD_Mill_Start].[Job Name] Like '%molding%' Or [PD_Mill_Start].[Style] Like '%N/A%' Or [PD_Mill_Start].[Interior or Exterior] Like '%other%',[Batch Door Count],0) AS [Del Other Qty], IIf([Batch Jamb Count] Is Not Null,[Batch Jamb Count],0) AS [Del Jamb Qty], IIf([Batch Door Count] Is Null,0,IIf([PD_Mill_Start].[Interior or Exterior] Like '%case%' Or [Style] Like '%case%',0,[Batch Door Count]-[Del Other Qty])) AS [Del Door Qty]
FROM PD_Mill_Start LEFT JOIN PD_Delivery_Schedule ON PD_Mill_Start.[Order Number] = PD_Delivery_Schedule.[Order Number]
WHERE PD_Mill_Start.[Order Number]='350844'
ORDER BY PD_Mill_Start.[Order Number] DESC;

Error Msg 207, Level 16, State 1, Line 1 Invalid column name 'Del Other Qty'.

Upvotes: 2

Views: 2021

Answers (1)

Kritner
Kritner

Reputation: 13765

You can't use "derived/aliased" columns in the select query in calculations within that same query... I don't know if those are the correct words, but consider:

declare @table table (val1 int, val2 int)

insert into @table (val1, val2)
values (5,10)

select 
    val1, -- 5
    val2, -- 10
    val1*val2 as val3, -- this works, 50
    val3+1 as val4 -- this does not work, you probably expect 51.
from @table

You can do this (among other things)

select val1, val2, val3, 
val3+1 as val4 -- this now works, returns 51
from (
    select 
        val1, 
        val2, 
        val1*val2 as val3, -- this works, 50
    from @table
) thingy

another option is:

select 
    val1,
    val2,
    val3,
    val3+1 as val4
from @table
cross apply (select val1 * val2 as val3) thing

Sorry I don't know the correct terminology for these concepts. The important part is you cannot use a "derived/aliased" column as part of a calculation within the same select query - which is what you're attempting to do in your query [Batch Door Count]-[Del Other Qty]. If you refactor your query utilizing the ideas from above, you will no longer have your issue. I'm trying to work on that now, but this query is quite unwieldy.

Upvotes: 1

Related Questions