March 4, 2016 at 9:24 am
Hi,
Does SQL Server allow programming within a SELECT statement?
For instance, SELECT col1, col2 (some programming result) as Col3 FROM tables
Could you please share some thoughts.
thank you
March 4, 2016 at 9:27 am
etl2016 (3/4/2016)
Hi,Does SQL Server allow programming within a SELECT statement?
For instance, SELECT col1, col2 (some programming result) as Col3 FROM tables
Could you please share some thoughts.
thank you
Yes.
Select Col3 = Col1 + Col2
for example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 4, 2016 at 9:35 am
etl2016 (3/4/2016)
Hi,Does SQL Server allow programming within a SELECT statement?
For instance, SELECT col1, col2 (some programming result) as Col3 FROM tables
Could you please share some thoughts.
thank you
What kind of programming?
Including expressions as Phil did? That won't change the value of the column in the table, it will only affect the result set.
March 4, 2016 at 9:38 am
thanks.
But, its a bit more programming, involving local-scoped variables, some Queries, some aggregation and a final result.
Can we say something like, SELECT col1, col2, (DECLARE local_variable INT; SET @local_variable = SELECT (Query)) as Col3 etc..
Any T-SQL specific programming constructs to be taken care here, both performance wise and functionality wise .
thank you very much
March 4, 2016 at 9:47 am
Absolutely no variable declarations or multiple instructions. All variables should be defined before the query.
You can have queries inside queries. These are called subqueries and are a great tool. However, if you abuse them, they'll generate performance problems.
Also, you can have a SELECT to return a result set
SELECT col1,
col2,
col3
FROM TableA
Or you can have a SELECT to assign values to variables
SELECT @val1 = col1,
@val2 = col2,
@val3 = col3
FROM TableA
But you can't mix them.
If you're trying to do something specific, share it with us and we'll try to guide you. Read the article in my signature to know how to post better questions.
March 4, 2016 at 11:49 am
etl2016 (3/4/2016)
thanks.But, its a bit more programming, involving local-scoped variables, some Queries, some aggregation and a final result.
Can we say something like, SELECT col1, col2, (DECLARE local_variable INT; SET @local_variable = SELECT (Query)) as Col3 etc..
Any T-SQL specific programming constructs to be taken care here, both performance wise and functionality wise .
thank you very much
That smacks of RBAR (Row By Agonizing Row) and should be avoided even if you could do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2016 at 12:19 pm
It's also worth noting that using SET vs SELECT when assigning a variable will work similarly but have important differences.
Here is some code to illustrate:
--Declare a simple table and add some test data
DECLARE @test-2 TABLE (ID INT, Name VARCHAR(10))
INSERT INTO @test-2
VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
--Declare our variables
DECLARE @set VARCHAR(10), @select VARCHAR(10)
--Let's see what the table looks like
SELECT * FROM @test-2
--This will fail because it will only accept a single (scalar) value
BEGIN TRY
SET @set = (SELECT Name FROM @test-2)
END TRY
BEGIN CATCH
SELECT 'ERROR: A SET with multiple values will not work!'
END CATCH
--This will work because it's only one value
SET @set = (SELECT Name FROM @test-2 WHERE ID = 1)
--This will work but if you don't specify an exact value it will store the last returned value. In this case DDD.
SELECT @select = Name FROM @test-2
SELECT @set
SELECT @select
--------------------------------------------------------------------
--Now let's test what happens when no values are found.
--Set our variables to an initial value.
SET @set = 'INIT'
SET @select = 'INIT'
--This won't fail but notice how it assigns a NULL to the variable
SET @set = (SELECT Name FROM @test-2 WHERE ID = 5)
--This won't fail either but the intial value is perserved.
SELECT @select = Name FROM @test-2 WHERE ID = 5
SELECT @set
SELECT @select
March 4, 2016 at 1:35 pm
thank you, I think in my case there is no RBAR
My query extracts from DW and manufactures a summarised aggregate table, which has 1 day information per record, so, max 31 records as the final result set.
Say, it has below structure:
A B C
a1
a2
a3
a4
In the above A,B, C are columns and a1, a2, a3.....are unique days in a month.
Now there is requirement to add a new column D which qualifies each aX, whether it is start of the month, if so what year and what month etc. The unique values a1, a2, a3 .... are not from 1 to 31 always. Instead, they represent sequentially incremented INTs, the smallest INT being dayID representing the inception of business, till today and into future.
There is a Static reference table which holds info on these INTs along with associated attributes.
Here is where the programming is needed when I say what I need is: SELECT c1, c2, (some programming with a result) as c3
Here "some programming" is JOIN related programming. Because, after JOIN-ing, there is some logic and computation to manufacture the last column D in the Aggregate table. There is single field in the ref static table, to derive straight forward.
Hope you agree there is no adversely influencing RBAR situation here (we are talking about 31 max records)
One option I am thinking is to generate a CTE on Static Ref table to reduce programming complexity within the above SELECT statement, and keep JOIN light weight between CTE and aggregate table, to generate final column D.
thank you.
March 4, 2016 at 1:49 pm
etl2016 (3/4/2016)
thank you, I think in my case there is no RBARMy query extracts from DW and manufactures a summarised aggregate table, which has 1 day information per record, so, max 31 records as the final result set.
Say, it has below structure:
A B C
a1
a2
a3
a4
In the above A,B, C are columns and a1, a2, a3.....are unique days in a month.
Now there is requirement to add a new column D which qualifies each aX, whether it is start of the month, if so what year and what month etc. The unique values a1, a2, a3 .... are not from 1 to 31 always. Instead, they represent sequentially incremented INTs, the smallest INT being dayID representing the inception of business, till today and into future.
There is a Static reference table which holds info on these INTs along with associated attributes.
Here is where the programming is needed when I say what I need is: SELECT c1, c2, (some programming with a result) as c3
Here "some programming" is JOIN related programming. Because, after JOIN-ing, there is some logic and computation to manufacture the last column D in the Aggregate table. There is single field in the ref static table, to derive straight forward.
Hope you agree there is no adversely influencing RBAR situation here (we are talking about 31 max records)
One option I am thinking is to generate a CTE on Static Ref table to reduce programming complexity within the above SELECT statement, and keep JOIN light weight between CTE and aggregate table, to generate final column D.
thank you.
We cannot really agree anything based on this description. It's just not detailed enough. If you want help with how to code your solution, you are going to have to provide the level of detail previously requested. That includes sample DDL, data and desired results in a format which can be cut & pasted straight into SSMS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 4, 2016 at 4:13 pm
etl2016 (3/4/2016)
One option I am thinking is to generate a CTE on Static Ref table to reduce programming complexity within the above SELECT statement, and keep JOIN light weight between CTE and aggregate table, to generate final column D.
SQL Server will have to perform the same JOIN, doesn't matter if you wrap it in CTE or not. It will only simplify the coding.
March 4, 2016 at 7:21 pm
Hope you agree there is no adversely influencing RBAR situation here (we are talking about 31 max records)
Like Phil said, with no code, it's impossible to say one way or another. Also, it sounds like you're talking about generating 31 rows. My guess is that the source of those 31 rows is bigger than 31 rows. There could be 10M rows that go into each one of them.
March 5, 2016 at 7:20 am
etl2016 (3/4/2016)
Now there is requirement to add a new column D which qualifies each aX, whether it is start of the month, if so what year and what month etc. The unique values a1, a2, a3 .... are not from 1 to 31 always. Instead, they represent sequentially incremented INTs, the smallest INT being dayID representing the inception of business, till today and into future.
To first address your original question, a lot of "programming" can be done in a SELECT, as long as it is in a single expression. Expressions can be very complex though, includinig subqueries. They can even include user-defined functions but this generally performs so poor that my advice is to not go there unless you really know what you are doing.
The description above sounds like something that can easily be done using expressions.
Can you post the tables involved (as CREATE TABLE statements - please incluce all constraints, but please remove extra columns that are not relevant to the question), a few well-chose rows of sample data (as INSERT statements), the output you need to get from that sample data, plus an explanation of how this output logically derives from the sample data? That makes it a lot easier for us to help you!
March 7, 2016 at 6:32 am
thanks everyone for sharing your thoughts.
I implemented what I need.
Structurally it looks like a RBAR scenario, but, given the situation that, tables being joined T1 and T2 are slim, I believe RBAR is okay.
T1 is manufactured from DW and always contains 30-odd records only (1 day per month). And T2 contains Reference dates of the business, since its inception.
I derived my final column D as below:
(
SELECT
(
CONVERT ( varchar(30), ( SELECT MAX (some_date_field) from REF_TABLE T2 WHERE CLAUSE on T1 and T2), 103 )
+
' - '
+
CONVERT ( varchar(30), ( SELECT MAX (some_date_field) from REF_TABLE T2 WHERE CLAUSE on T1 and T2), 103 )
)
) AS Column-D
thank you
March 7, 2016 at 3:37 pm
etl2016 (3/7/2016)
thanks everyone for sharing your thoughts.I implemented what I need.
Structurally it looks like a RBAR scenario, but, given the situation that, tables being joined T1 and T2 are slim, I believe RBAR is okay.
T1 is manufactured from DW and always contains 30-odd records only (1 day per month). And T2 contains Reference dates of the business, since its inception.
I derived my final column D as below:
(
SELECT
(
CONVERT ( varchar(30), ( SELECT MAX (some_date_field) from REF_TABLE T2 WHERE CLAUSE on T1 and T2), 103 )
+
' - '
+
CONVERT ( varchar(30), ( SELECT MAX (some_date_field) from REF_TABLE T2 WHERE CLAUSE on T1 and T2), 103 )
)
) AS Column-D
thank you
Check CROSS APPLY.
You may find some examples here:
_____________
Code for TallyGenerator
March 8, 2016 at 12:23 pm
Going deeper with the CROSS APPLY suggestion :
Consider something like this >
SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal
FROM Table
WHERE someFilterColumn= 'west coast'
Could be done with something like ( I didnt totally check my syntax but the idea is )
SELECT SUM(t1.someNumberColumn) / (t2.totalSales) AS westCoastSalesAsA%OfTotal
FROM Table t1 CROSS APPLY
(SELECT SUM(someNumberColumn) as totalSales FROM Table) as t2
WHERE t1.someFilterColumn= 'west coast'
It appears cleaner.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply