Calculated Alias T-SQL with CASE

Question

Calculated Alias T-SQL with CASE

Andy Stewart-222789

Valued Member

Points: 54
More actions
April 6, 2005 at 12:39 pm

#60248

I am getting an
Invalid column name 'Set Disc'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Sales'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Adj GP Pre Rebate'
on the following T-SQL statement
SELECT GlobalRTInvHeaderCum.Co, GlobalRTInvHeaderCum.Type, GlobalRTInvHeaderCum.Invno, GlobalRTInvHeaderCum.Locn, GlobalRTInvHeaderCum.Cust, GlobalRTInvHeaderCum.Delpt, GlobalRTInvHeaderCum.Sman, GlobalRTInvHeaderCum.Sarea, GlobalRTInvHeaderCum.Txpt, GlobalRTInvHeaderCum.PstDate, GlobalRTInvHeaderCum.Cdisc, GlobalRTInvDetailCum.Item, GlobalRTInvDetailCum.Prgp, GlobalRTInvDetailCum.Qty, GlobalRTInvDetailCum.Sprc, GlobalRTInvDetailCum.Cpu, GlobalRTInvDetailCum.Disc, GlobalRTInvAddnCum.Acode, GlobalRTInvAddnCum.[Desc], GlobalRTInvAddnCum.Vrc,
'Sales' =
CASE GlobalRTInvHeaderCum.Type
WHEN 'C' THEN
  CASE [Qty]
   When 0 then -([Sprc]*(100-[Disc])/100)
   Else [Sprc]*(100-[Disc])/100*[Qty]
  End
ELSE [Sprc]*(100-[Disc])/100*[Qty]
END,
[Sales] - ([Cpu]*[Qty]) AS GP,
CASE
WHEN [Sales] = 0 then 0
Else [GP]/[Sales]
End AS [GP%],
[Sales]*[Cdisc]/100 AS [Set Disc], [GP]-[Set Disc] AS [Adj GP Pre Rebate],
CASE
WHEN [Sales]=0 then 0
Else [Adj GP Pre Rebate]/[Sales]
END AS [Adj GP% Pre Rebate],
[Product Groupings].SummaryPrdGrp
FROM ((GlobalRTInvHeaderCum LEFT JOIN GlobalRTInvDetailCum ON (GlobalRTInvHeaderCum.Co = GlobalRTInvDetailCum.Co) AND (GlobalRTInvHeaderCum.Type = GlobalRTInvDetailCum.Type) AND (GlobalRTInvHeaderCum.Invno = GlobalRTInvDetailCum.Invno)) LEFT JOIN GlobalRTInvAddnCum ON (GlobalRTInvHeaderCum.Co = GlobalRTInvAddnCum.Co) AND (GlobalRTInvHeaderCum.Type = GlobalRTInvAddnCum.Type) AND (GlobalRTInvHeaderCum.Invno = GlobalRTInvAddnCum.Invno)) LEFT JOIN [Product Groupings] ON GlobalRTInvDetailCum.Prgp = [Product Groupings].PrdGrp;
It driving me up the wall. I have tried as many variations as I can think of. HELP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rfrancisco Mr or Mrs. 500 Points: 544 More actions · Answer 1

The problem is you are using the Alias ([Sales], [GP] and [Set Disc]) in your computation, which is not allowed.

One suggestion that I can give is to create a temporary table that will store the values of your computed columns, which gets used in determining the values of succeeding columns.

http://www.sql-server-helper.com

David.Poole SSC Guru Points: 76036 More actions · Answer 2

In your line

[Sales]*[Cdisc]/100 AS [Set Disc], [GP]-[Set Disc] AS [Adj GP Pre Rebate]

I don't think that you can define [Set Disc] and use it in one statement.

Try

[Sales]*[Cdisc]/100 AS [Set Disc], [GP]-([Sales]*[Cdisc]/100) AS [Adj GP Pre Rebate]

Oh, ditch the spaces in field names and try and stick to alphanumerics and underscores. You are just storing up problems for yourself with the current naming scheme.

Try Sales = rather than 'Sales' = , otherwise try putting AS Sales after your END statement.

LinkedIn Profile

EdVassie SSC Guru Points: 60479 More actions · Answer 3

You can define and use the alias [Set Disc] in the same statement using the following model SQL:

SELECT

[Set Disc] * 2 AS [New Disc]

FROM

(SELECT Value * 0.10 AS [Set Disc] FROM YOUR_TABLE) AS X

i.e. You can define an alias in a table expression then use it in the parent SQL statement. This can save the hassle of explicitly creating and populating a #temp table. The nesting can be multi-level, and we have production SQL that nests 3 level deep - it is not pretty but it works, and this set-based approach normally well out-performs othe approaches.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Andy Stewart-222789 Valued Member Points: 54 More actions · Answer 4

Here's the answer that i finally came up with, I dont know how iit will perform but time scales are very short and I have lots to do still. Thanks to everyone for the input:

Create a UDF called Sales

CREATE FUNCTION dbo.sales (@type character, @SPRC int, @Disc int, @Qty int )

RETURNS int

BEGIN

Declare @Sales int

If @type = 'C'

IF @Qty = 0

Set @sales = -(@Sprc*(100-@Disc)/100)

Else

Set @sales = @Sprc*(100-@Disc)/100*@Qty

ELSE

Set @sales = @Sprc*(100-@Disc)/100*@Qty

Return @Sales

End

Then

SELECT GlobalRTInvHeaderCum.Co, GlobalRTInvHeaderCum.Type, GlobalRTInvHeaderCum.Invno, GlobalRTInvHeaderCum.Locn, GlobalRTInvHeaderCum.Cust, GlobalRTInvHeaderCum.Delpt, GlobalRTInvHeaderCum.Sman, GlobalRTInvHeaderCum.Sarea, GlobalRTInvHeaderCum.Txpt, GlobalRTInvHeaderCum.PstDate, GlobalRTInvHeaderCum.Cdisc, GlobalRTInvDetailCum.Item, GlobalRTInvDetailCum.Prgp, GlobalRTInvDetailCum.Qty, GlobalRTInvDetailCum.Sprc, GlobalRTInvDetailCum.Cpu, GlobalRTInvDetailCum.Disc, GlobalRTInvAddnCum.Acode, GlobalRTInvAddnCum.[Desc], GlobalRTInvAddnCum.Vrc,

dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) as Sales,

dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) - ([Cpu]*[Qty]) AS GP,

CASE

WHEN dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) = 0 then 0

Else dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) - ([Cpu]*[Qty])/dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])

End AS [GP%],

dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])*[Cdisc]/100 AS [Set Disc],

dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) - ([Cpu]*[Qty])-dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])*[Cdisc]/100 AS [Adj GP Pre Rebate],

CASE

WHEN dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])=0 then 0

Else (dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty]) - ([Cpu]*[Qty])-(dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])*[Cdisc]/100))/dbo.Sales(GlobalRTInvHeaderCum.Type,[Sprc],[Disc],[Qty])

END AS [Adj GP% Pre Rebate],

[Product Groupings].SummaryPrdGrp

FROM ((GlobalRTInvHeaderCum LEFT JOIN GlobalRTInvDetailCum ON (GlobalRTInvHeaderCum.Co = GlobalRTInvDetailCum.Co) AND (GlobalRTInvHeaderCum.Type = GlobalRTInvDetailCum.Type) AND (GlobalRTInvHeaderCum.Invno = GlobalRTInvDetailCum.Invno)) LEFT JOIN GlobalRTInvAddnCum ON (GlobalRTInvHeaderCum.Co = GlobalRTInvAddnCum.Co) AND (GlobalRTInvHeaderCum.Type = GlobalRTInvAddnCum.Type) AND (GlobalRTInvHeaderCum.Invno = GlobalRTInvAddnCum.Invno)) LEFT JOIN [Product Groupings] ON GlobalRTInvDetailCum.Prgp = [Product Groupings].PrdGrp;

Messy, overworked but if this is all that t-sql can use then so be it!