When Unions, subselects & order by don't get along

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

When Unions, subselects & order by don't get along

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2003-10-17 : 14:07:43

I did look in the archives for relevant topics, but no dice. Maybe there is a much better approach, such that the answer to this is obvious.

Anyway. Say you have the following three tables (I've included the core columns and some sample data):

1) Invoice
ID, InvoiceNumber, InvoiceDate, Last_PaymentType_ID
--------------------------------------------------
1 10100 2003/10/17 1
2 10101 2003/10/17 0
3 10102 2003/10/17 2

2) PaymentType
ID, Name
------------------------
1 Cheque
2 Credit Card

3) PaymentHistory
Amount, ChequeNumber, Invoice_ID, PaymentType_ID
10 123 1 1
05 344 3 2
30 455 1 1

I want to write a query that returns all of the invoices and the most recent payment history. It needs to return the name of PaymentType rather than the ID.

In other words, it would return something like this:
10100 2003/10/17 $30 Cheque 455
10101 2003/10/17 n/a
10102 2003/10/17 $5 Credit Card 344

I wrote two queries to return the correct information. Individually, they work. But I'd like to combine them and SQL Server bleats out that I can't do that.

Here's the complete query I had. Any suggestions?

(
SELECT
InvoiceNumber,
InvoiceDate,
(SELECT Top 1 Amount FROM PaymentHistory WHERE Invoice_ID=Invoice.ID) ORDER BY PaymentDate DESC) AS LastAmount,
PaymentType.Name AS LastPaidType,
(SELECT Top 1 ChequeNumber FROM PaymentHistory WHERE Invoice_ID=Invoice.ID) ORDER BY PaymentDate DESC) AS RefNum
FROM
Invoice INNER JOIN PaymentType ON (Invoice.Last_PaymentType_ID=PaymentType.ID)
)
UNION
(
SELECT
InvoiceNumber,
InvoiceDate,
'' AS LastAmount,
'n/a' AS LastPaidType,
'' AS RefNum
FROM
Invoice
WHERE
Last_Payment_Type=0
)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-17 : 14:17:27

Change the second select in the UNION:

SELECT	InvoiceNumber
	, InvoiceDate
	, null
	, 'n/a'
	, null
FROM
Invoice
WHERE
Last_Payment_Type=0

Phibian
Starting Member

32 Posts

Posted - 2003-10-21 : 15:38:21

Thanks for replying.

I tried out your suggestion, but the error remains the same:

"ORDER BY items must appear in the select list if the statement contains a union operator"

The problem is solved by removing the subselect, but then I'm not returning the correct data.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-21 : 16:41:09

quote:

(
SELECT
InvoiceNumber,
InvoiceDate,
(SELECT Top 1 Amount FROM PaymentHistory WHERE Invoice_ID=Invoice.ID) ORDER BY PaymentDate DESC) AS LastAmount,
PaymentType.Name AS LastPaidType,
(SELECT Top 1 ChequeNumber FROM PaymentHistory WHERE Invoice_ID=Invoice.ID) ORDER BY PaymentDate DESC) AS RefNum
FROM
Invoice INNER JOIN PaymentType ON (Invoice.Last_PaymentType_ID=PaymentType.ID)
)

you are not using proper syntax. why is there a closing paren in the the middle of the bold parts i have highlighted above ?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources