If an alias is used in an ORDER BY
it must be used on its own, not inside an expression.
If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.
So for example
SELECT A AS B
FROM (VALUES (1, 3),
(2, 2),
(3, 1)) V(A, B)
ORDER BY B
Returns (ordered by alias)
+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+
But
SELECT A AS B
FROM (VALUES (1, 3),
(2, 2),
(3, 1)) V(A, B)
ORDER BY B + 0
Returns (Ordered by base table column B
)
+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+
You can of course just wrap the whole thing in a derived table or CTE.
WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;
This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).
Try to break the internal query into three parts (INNER JOIN
, LEFT JOIN
with WHERE IS NULL
check, RIGHT JOIN
with IS NULL
check) and then UNION ALL
the three parts. This has the following advantages:
The optimizer has less transformation options available for FULL
joins than for (the more common) INNER
and LEFT
joins.
The Z
derived table can be removed (you can do that anyway) from the view definition.
The NOT(pe.ThisThing = 1 AND se.OtherThing = 0)
will be needed only on the INNER
join part.
Minor improvement, the use COALESCE()
will be minimal if any at all (I assumed that se.SEId
and pe.PEId
are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE()
calls.)
More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE()
is not blocking the push.)
All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.
In all, the view can be written as:
SELECT
se.SEId + '-' + pe.PEId AS Id,
se.SEId, pe.PEId,
pe.StaffName,
pe.EventTime,
COALESCE(pe.EventType, se.EventType) AS EventType,
pe.Duration,
COALESCE(pe.Data, se.Data) AS Data,
COALESCE(pe.Field, se.Field) AS Field,
pe.ThisThing, se.OtherThing,
DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se
ON pe.StaffName = se.StaffName
AND pe.Duration = se.Duration
AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0)
UNION ALL
SELECT
'0-0',
NULL, pe.PEId,
pe.StaffName,
pe.EventTime,
pe.EventType,
pe.Duration,
pe.Data,
pe.Field,
pe.ThisThing, NULL,
DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se
ON pe.StaffName = se.StaffName
AND pe.Duration = se.Duration
AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
AND se.StaffName IS NULL
UNION ALL
SELECT
'0-0',
se.SEId, NULL,
se.StaffName,
se.EventTime,
se.EventType,
se.Duration,
se.Data,
se.Field,
NULL, se.OtherThing,
DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se
ON pe.StaffName = se.StaffName
AND pe.Duration = se.Duration
AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
AND pe.StaffName IS NULL ;
Best Answer
All set operators are translated to joins or join-like operators. You can witness that in the query plan.
For that reason the full outer join that you have there is the most efficient you can do. Disregarding, of course, the hopefully rare situation in which the optimizer picks a bad plan and a rewrite happens to perform better by luck. This can always happen.