Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL Select a value

Posted on 2013-01-26
10
Medium Priority
?
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 71

Assisted Solution

by:Qlemo
Qlemo earned 800 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 1200 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 1200 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

660 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