Menu
lets say I have a huge select on a certain table. One value for a column is calculated with complex logc and its called ColumnA. Now, for another column, I need the value from ColumnA and add some other static value to it.
Sample SQL:
select table.id, table.number, complex stuff [ColumnA], [ColumnA] + 10 .. from table ... The [ColumnA] + 10 is what im looking for. The complex stuff is a huge case/when block.
Ideas?
885
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
The HLOOKUP function syntax has the following arguments: Lookup_value Required. The value to be found in the first row of the table.
In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared.
The CONVERT() function in SQL server is used to convert a value of one type to another type. It is the target data type to which the to expression will be converted, e.g: INT, BIT, SQL_VARIANT, etc. It provides the length of the target_type. Length is not mandatory.
If you want to reference a value that's computed in the SELECT clause, you need to move the existing query into a sub-SELECT:
SELECT /* Other columns */, ColumnA, ColumnA + 10 as ColumnB FROM (select table.id, table.number, complex stuff [ColumnA].. from table ... ) t You have to introduce an alias for this table (in the above, t, after the closing bracket) even if you're not going to use it.
(Equivalently - assuming you're using SQL Server 2005 or later - you can move your existing query into a CTE):
;WITH PartialResults as ( select table.id, table.number, complex stuff [ColumnA].. from table ... ) SELECT /* other columns */, ColumnA, ColumnA+10 as ColumnB from PartialResults CTEs tend to look cleaner if you've got multiple levels of partial computations being done, I.e. if you've now got a calculation that depends on ColumnB to include in your query.
91
Unfortunately, in SQL Server 2016:
SELECT 3 AS a, 6/a AS b; Error: Invalid column name: 'a'.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
