3

In MS SQL Server, I spent too much time trying to resolve this. I finally figured it out, except I don't know the reason. How come, dividing by the cast statement in line 4 works below

SELECT 
  cast(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT AS decimal(18,8))
AS TOTAL_NET_AMOUNT_AMOUNT,
  cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY)) AS decimal(18,8))
AS ActualAmountPaidOnProjectGroupedByInvoice,
  ((dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
/
  (cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY)) AS decimal(18,8))))
AS 'Allocation_Amount',

But when I try and divide by the alias that I created, ''ActualAmountPaidOnMatterGroupedByInvoice' in Line 3 I get an error message:

Msg 207, Level 16, State 1, Line 131 Invalid column name 'ActualAmountPaidOnMatterGroupedByInvoice'

Sample incorrect code:

SELECT 
    cast(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT AS decimal(18,8))
  AS TOTAL_NET_AMOUNT_AMOUNT,
    cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
    OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY))
  AS decimal(18,8))
  AS ActualAmountPaidOnProjectGroupedByInvoice,
    ((dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
  /
    (ActualAmountPaidOnProjectGroupedByInvoice) AS decimal(18,8))))
  AS 'Allocation_Amount'

How come? Thanks all!

Mohammad Reza Shahrestani
  • 1,119
  • 3
  • 17
  • 28
BrianBeing
  • 431
  • 2
  • 4
  • 12

2 Answers2

3

The reason that you cannot use the alias in the query, is because the alias has not been recognized by the query engine yet. The engine evaluates queries in stages in the following order:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

One of the last steps in the SELECT stage is to apply the aliases specified in the query to the resulting dataset. Since these are not applied until the end of the SELECT stage, they are not available in the evaluation of the data to be returned nor in the WHERE, GROUP BY, or HAVING stages.

Additionally, some query engines do allow aliases (or ordinal position) to be used in the ORDER BY stage. As pointed out by Julian in the comments, MSSQL does allow for ordinal position ordering syntax.

Jacob Lambert
  • 7,449
  • 8
  • 27
  • 47
  • 2
    PS, `order by 2`, so by column index, is allowed in MSSQL – Julian Jul 21 '19 at 00:22
  • MSSQL doesn't have a `LIMIT`? – Julian Jul 21 '19 at 00:25
  • @Julian the `LIMIT` stage is the wild west of SQL queries. Almost every distribution has it's own way of doing it. In MSSQL, you would use `TOP/OFFSET/FETCH`, MySQL/Maria/Postgres uses `LIMIT`, ORACLE uses `ROWNUM`, etc. But, that stage is always evaluated at the end and specifies the window to return from the result set. – Jacob Lambert Jul 21 '19 at 00:35
1

I think you might be misunderstanding where aliased columns are available/able to be referenced by the aliased name, particularly because you said (paraphrase) "an alias I created on line 3 of the sql wasn't available on line 4":

Wrong:

SELECT
  1200 as games_won,
  25 as years_played,
  --can't use these aliases below in the same select block that they were declared in
  games_won / years_played as games_won_per_year 
  ...

Right: 

SELECT
  1200 as games_won,
  25 as years_played,
  --can use the values though 
  1200 / 25 as games_won_per_year

Right:

SELECT
  games_won / years_played as games_won_per_year  --alias from inner scope is available in this outer scope
FROM
(
  SELECT 
    --these aliases only become available outside the brackets
    1200 as games_won,
    25 as years_played
) x

You can't alias a column and use the alias again in the same select block; you can only alias in an inner/subquery and use the alias in an outer query. SQL is not like a programming language that operates line by line:

int gameswon = 1200;
int yearsplayed = 25;
int winsperyear = gameswon / yearsplayed;

Here in this C# you can see we declare variables (aliases) on earlier lines and use them on later lines but that's because the programming language operates line by line. The results of an earlier line execution are available to later lines. SQL doesn't work like that; SQL works on entire sections of the query at a time. Your columns don't acquire those aliases you gave them until the entire select block is finished being processed so you cannot give a column or calculation an alias and then use that alias again in the same select block. The only way to get round this and create an alias that you will later use repeatedly is to create the alias in a subquery.

Here's another example:

SELECT 
  fih.tot_amt / fih.amt_per_proj AS allocation_amount

FROM
  (
    SELECT
      CAST(f.total_net_amount_amount AS DECIMAL(18,8)) as tot_mt,
      CAST(SUM(f.total_net_amoun_amount) OVER (PARTITION BY p.project_key)) AS DECIMAL(18,8)) AS amt_per_proj
    FROM
      dbo.factinvoiceheader f
      INNER JOIN
      dbo.dimproject p
      ON ...
  ) fih

Here you can see I pulled the columns I wanted and aliased them in an inner query and then used the aliases in the outer query - it works because the aliases decalred inside the inner block are made available to the outer block

Always remember that SQL is not line by line line a typical programming language, but block by block. Indeed in most programming languages, things declared in inner code blocks are not available in outer code blocks (unless they're some globalised thing like javascript var) so SQL is a departure from what you're used to. Every time you create a block of instructions in SQL you have an opportunity to re-alias the columns of data.

Because SQL is block by block based, I indent my SQLs in blocks to make it easy to see what gets processed together. Keywords like SELECT, FROM, WHERE, GROUP BY and ORDER BY denote blocks and aliases can be created for columns in a SELECT, and for tables in a FROM. In taking your example above I've applied aliases not just to the calculations and columns but to the tables as well. It makes the query massively easier to read when it's indented and aliased throughout- give your table names an alias rather than writing dbo.factinvoiceheader. before every column name

Here's a set of tips for making your SQLs neater and easier to read and debug:

  • don't put them all on one line or at the same indent level - indent according to how deep or shallow the block of instructions is
  • select, from, where, group by, order by etc denote the start of a block of operations - indent them all to the same level and indent their sub-instructions another level (if your select is indent level 2, the columns being selected should be indent level 3)
  • when you have an inner query indent that too unless it's really simple and reads nicely as a one liner
  • use lowercase for column and table names, upper case for reserved words, functions, datatypes (some people prefer camel case for functions)
  • decide whether to use canelCase or underscore_style to split your words and keep to it
  • always alias tables, and always select columns as tablealias.columnname - this prevents your query breaking in future if a table has a column added that is the same name as an original column you selected without qualifying what table the original column came from
  • aliasing tables allows another vital operation; repeatedly joining the same table into a query. If your Person table has a WorkAddress and a HomeAddress the only way you can join the address table in twice to get both addresses for a person, is to alias the table (person join address h on p.homeaddressid = h.id join address w on p.workaddressid = w.id)
Caius Jard
  • 72,509
  • 5
  • 49
  • 80