Solved

T-SQL Select a value

Posted on 2013-01-26
10
301 Views
Last Modified: 2013-01-28
I am working with a query that I didn't write and I not sure how to interpret the SQL code.
I can't post the entire contents of the query but I will try to abbreviate what I am looking to interpret.

Select PurchaseDate from
Customers
Union
Select 2000-01-01 as SortOrder
from Customers

My question is the query is select a date value from the PurchaseDate Field and sort
from the earliest date of 2000-01-01.
The query seems to work correctly but I wasn't aware you can select a value you like this in T-SQL.  My question is this a specific convention in SQL for selecting a value from a field as a sort order?  Thanks for any feedback you can provide.  I will try to post more of the query if my question isn't clear enough.
0
Comment
Question by:fjkaykr11
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38822877
your query is misleading!

if you run your second part alone:

Select 2000-01-01 as SortOrder
from Customers

This will return the value 2001-01-01 with a caption of SortOrder repeated as many times as you have rows in your Customers table.

Because you have a UNION clause, all the duplicate values are removed.

Also, the Alias (as SortOrder) of the second query is not useful

Your query should read like this:

Select PurchaseDate from
Customers
Union
Select 2000-01-01
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38823044
I will try to post more of the query if my question isn't clear enough.
You may have better luck that way.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38823482
@emoreau thanks for your reply.   The Select 2000-01-01 SortOrder
returns the rows with with the dates in order starting at 2000 and moving forward.
What I am not familiar with is the concept of selecting a value such as 2000-01-01, this is something I haven't seen before usually I am only selecting a column.  What I am trying to
get more info on is when you would select a value such as above versus selecting a Column.  Thanks.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38824096
because you are using a "select value" and this is unionized to another select, the value is returned part of the resultset.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38824896
Ok, so that makes it a bit clearer.  So when you are selecting a value versus selecting a column, typically you would only doing this when using a UNION ? Or are there other cases for Selecting a value.  Thanks.
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 38824920
In older T-SQL code you can still see something like
declare @val as datetime
select @val = '2000-01-01'

Open in new window

or
declare @val as datetime
set @val = select '2000-01-01'

Open in new window

(and similar) but that is not necessary. Usually you will see selecting a constant value in conjunction with UNION only, as this makes the most sense.
When doing that, remember that the first select in a UNION determines the basic types and the column names for the result set. If you revert selecting a constant value and selecting real data, you might get wrong data types and column names.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 300 total points
ID: 38824935
you can also add some values without a union clause.

for example if you have an amount in a table, and you want to return 10% tax and the total, you could do something like

select amount, amount *.1 as tax, amount * 1.1 as amountwithtax
from yourtable
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38825511
Thanks @Qlemo and @Emoreau.  I appreciate the help.  I wanted to read up more on this process that you are discussing.  I spoke with someone else who has more experience than me - they mentioned that doing something like is referred to as a 'print statement' in a Union (not sure).   Does that sound correct?
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 38826290
not at all. adding a constant like you are doing adds a row to the returned resultset. When you have a single column like you have, your application cannot distinguish which rows are coming from constants and which are real data.

A Print statement is not returned as part of the resultset.
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 38829454
thanks for all the feedback.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Update trigger 5 16
Query Syntax 17 31
T-SQL: "HAVING CASE" Clause 1 23
Sql server get data from a usp to use in a usp 5 14
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now