Solved

T-SQL Select a value

Posted on 2013-01-26
10
299 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 69

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
 
LVL 69

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 68

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 69

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 69

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

15 Experts available now in Live!

Get 1:1 Help Now