Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Union query

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
Mike Eghtebas
Asked:
Mike Eghtebas
  • 4
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
The problem was missing:   cast(o.OrderDate as varchar(20))

How can I prepare this cast to produce dates like 11/25/2014
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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