Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
É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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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