• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

T-SQL Select a value

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
fjkaykr11
Asked:
fjkaykr11
3 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Anthony PerkinsCommented:
I will try to post more of the query if my question isn't clear enough.
You may have better luck that way.
0
 
fjkaykr11Author Commented:
@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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Éric MoreauSenior .Net ConsultantCommented:
because you are using a "select value" and this is unionized to another select, the value is returned part of the resultset.
0
 
fjkaykr11Author Commented:
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
 
QlemoDeveloperCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
fjkaykr11Author Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
fjkaykr11Author Commented:
thanks for all the feedback.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now