asked on

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

Jim Horn

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.

Jim Horn

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.

Mike Eghtebas

ASKER

The problem was missing: cast(o.OrderDate as varchar(20))

How can I prepare this cast to produce dates like 11/25/2014

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

Thank you.

Jim Horn

Thanks for the grade. Good luck with your project. -Jim