Solved

Union query

Posted on 2014-11-25
6
113 Views
Last Modified: 2014-11-25
The following sql works without Union Select.

Question: Why it doesn't work with Union Select?

"SELECT '- all -' , 'n/a', 0 From tblCustomers cc Union SELECT" & _
                      " c.FirstName + ' ' + c.LastName AS Customer, o.OrderDate, o.OrderID" & _
                      " FROM tblCustomers c INNER JOIN tblOrders o ON c.CustomerID = o.CustomerID" & _
                      " Where o.CustomerID=IIf(" & custID & "=0, o.CustomerID, " & custID & ")")

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465013
UNION queries determine the column names from the first half of the UNION, and the above T-SQL doesn't have any.  

Try this...
"SELECT '- all -' AS Customer, 'n/a' as OrderDate, 0 as OrderID From tblCustomers cc Union SELECT" & _
" c.FirstName + ' ' + c.LastName , o.OrderDate, o.OrderID" & _
" FROM tblCustomers c INNER JOIN tblOrders o ON c.CustomerID = o.CustomerID" & _
" Where o.CustomerID=IIf(" & custID & "=0, o.CustomerID, " & custID & ")")

Open in new window


Also, if OrderDate is a date column, then 'n/a' isn't going to work as that's a varchar value, so you'll have to decide what you want here.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465019
Easier to read
"SELECT '- all -' AS Customer, 'n/a' as OrderDate, 0 as OrderID " & _ 
"FROM tblCustomers cc " & _ 
"UNION SELECT c.FirstName + ' ' + c.LastName, o.OrderDate, o.OrderID " & _
"FROM tblCustomers c " & _
"   INNER JOIN tblOrders o ON c.CustomerID = o.CustomerID " & _
"WHERE o.CustomerID=IIf(" & custID & "=0, o.CustomerID, " & custID & ")")

Open in new window

btw IIF works in SQL 2012, if you're using a version before that you'll have to use CASE.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40465033
The problem was missing:   cast(o.OrderDate as varchar(20))

How can I prepare this cast to produce dates like 11/25/2014
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40465058
>How can I prepare this cast to produce dates like 11/25/2014
SELECT CONVERT(varchar, o.OrderDate, 101)
See PortletPaul's handy-dandy article SQL Server Date Styles (formats) using CONVERT() as a reference.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40465104
Thank you.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465182
Thanks for the grade.  Good luck with your project.  -Jim
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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

757 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

20 Experts available now in Live!

Get 1:1 Help Now