This will be the first post in the T-SQL Basics series, where i try to explain some basic concepts and usage of common T-SQL commands, and this time i decided to cover the logical processing of the select command and an error that I often see in some developers.
To start, a select statement looks something like this:
With this in mind, let's run a simple query to obtain product with price greater than $100.00
You obtain the following output:
With this in mind, let's suppose you want the price in GTQ, this should be a piece of cake, right?, using what we know, we could expect to run a query like this:
RIGHT? no. Instead, you obtain an error like this:
Msg 207, Level 16, State 1, Line 1 Invalid column name 'PriceGTQ'.
Why is this? because of the logical processing of the SELECT statement:
The FROM clause is evaluated first, after that, in our query, the WHERE is evaluated, based on the fields obtained in the previous clause, so at this time, the PriceGTQ column does not exists, and therefore the error is shown.
To fix this, a simple approach is to copy the field declaration in the WHERE clause:
This way, the query is executed without issues.
Because the ORDER BY is evaluated after the SELECT clause, you can use the PriceGTQ label without issues:
You obtain the following output:
According to the Microsoft documentation, the logical query processing for all the clauses is this:
FROM > WHERE > SELECT > ORDER BY
This could help you improve your code, avoiding you to repeat unnecessary code or troubleshoot non-existent columns quickly, Making it more readable and efficient.
Source:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
To start, a select statement looks something like this:
SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]
With this in mind, let's run a simple query to obtain product with price greater than $100.00
SELECT ProductID, Name, ListPrice FROM [Production].[Product] where ListPrice > 100.00
You obtain the following output:
Let's assume price is in USD |
With this in mind, let's suppose you want the price in GTQ, this should be a piece of cake, right?, using what we know, we could expect to run a query like this:
SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] FROM [Production].[Product] where [PriceGTQ] > 100.00
RIGHT? no. Instead, you obtain an error like this:
Msg 207, Level 16, State 1, Line 1 Invalid column name 'PriceGTQ'.
Why is this? because of the logical processing of the SELECT statement:
The FROM clause is evaluated first, after that, in our query, the WHERE is evaluated, based on the fields obtained in the previous clause, so at this time, the PriceGTQ column does not exists, and therefore the error is shown.
To fix this, a simple approach is to copy the field declaration in the WHERE clause:
SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] FROM [Production].[Product] where (ListPrice *7.8) > 100.00 --PriceGTQ definition
This way, the query is executed without issues.
Because the ORDER BY is evaluated after the SELECT clause, you can use the PriceGTQ label without issues:
SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] FROM [Production].[Product] where (ListPrice *7.8) > 100.00 --PriceGTQ definition order by [PriceGTQ]
According to the Microsoft documentation, the logical query processing for all the clauses is this:
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
FROM > WHERE > SELECT > ORDER BY
This could help you improve your code, avoiding you to repeat unnecessary code or troubleshoot non-existent columns quickly, Making it more readable and efficient.
Source:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
Comments
Post a Comment