Result in same rows [message #635024] |
Thu, 19 March 2015 05:04 |
|
mehboob110233
Messages: 55 Registered: February 2014 Location: Pakistan
|
Member |
|
|
Dear all,
how can i remove empty cell in this query.
print screen attached.
Quote:SELECT HOLDING, OPERATING_ROOM, PACU
FROM (SELECT MRNO HOLDING, NULL OPERATING_ROOM, NULL PACU
FROM ORDERENTRY.PATIENT_CURRENT_LOCATION
WHERE ORDER_LOCATION_ID = '313'
UNION
SELECT NULL HOLDING, MRNO OPERATING_ROOM, NULL PACU
FROM ORDERENTRY.PATIENT_CURRENT_LOCATION
WHERE ORDER_LOCATION_ID = '072'
UNION
SELECT NULL HOLDING, NULL OPERATING_ROOM, MRNO PACU
FROM ORDERENTRY.PATIENT_CURRENT_LOCATION
WHERE ORDER_LOCATION_ID = '413')
[mod-edit: image inserted into message body by bb]
-
Attachment: query.png
(Size: 42.43KB, Downloaded 982 times)
[Updated on: Mon, 03 August 2015 14:12] by Moderator Report message to a moderator
|
|
|
Re: Result in same rows [message #635026 is a reply to message #635024] |
Thu, 19 March 2015 05:38 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
You need to explain why you are using UNION. The three union queries could be written in a single query:
SELECT HOLDING, OPERATING_ROOM, PACU
FROM ORDERENTRY.PATIENT_CURRENT_LOCATION
WHERE ORDER_LOCATION_ID in ('313','072','413');
Also, you don't have to use quote tags to post the code. Use code tags.
[Updated on: Thu, 19 March 2015 05:39] Report message to a moderator
|
|
|
|
|
|
Re: Result in same rows [message #635051 is a reply to message #635047] |
Thu, 19 March 2015 08:44 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You realise that you've got no relationship between the three columns so you can get different combinations every time you run the query?
|
|
|
|
Re: Result in same rows [message #635055 is a reply to message #635037] |
Thu, 19 March 2015 09:13 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Thu, 19 March 2015 12:00Use Lalit's query, but enclose the three column names in a COALESCE function.
He's only getting null cause he specifically coded it in the original query so he could have three columns in each select. It's doubtful you'll get the same nulls with Lalit's query, so coalesce won't help.
|
|
|
Re: Result in same rows [message #635056 is a reply to message #635055] |
Thu, 19 March 2015 09:24 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
I agree, COALESCE was in my mind, however, OP has not told the reason for using UNION to append the result sets, and thus using NULL for respective columns. I guess that's why John suggested COALESCE as OP has not explained the relationship.
|
|
|