Referentie-alias (berekend in SELECT) in WHERE-clausule

De berekende waarde BalanceDuedie als variabele is ingesteld in de lijst met geselecteerde kolommen, kan niet worden gebruikt in de WHERE-clausule.

Is er een manier waarop dit kan? In deze gerelateerde vraag (Een variabele gebruiken in MySQL Select Statment in een Where-clausule), lijkt het antwoord te zijn: eigenlijk, nee, je zou de berekening gewoon twee keer uitschrijven (endie berekening in de query uitvoeren), en geen van alle is bevredigend.

Antwoord 1, autoriteit 100%

U kunt niet verwijzen naar een alias, behalve in ORDER BY, omdat SELECT de op één na laatste clausule is die wordt geëvalueerd. Twee oplossingen:

SELECT BalanceDue FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;

Of herhaal gewoon de uitdrukking:

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE  (InvoiceTotal - PaymentTotal - CreditTotal)  > 0;

Ik geef de voorkeur aan het laatste. Als de uitdrukking extreem complex is (of duur om te berekenen), moet u waarschijnlijk een berekende kolom overwegen (en misschien blijven bestaan), vooral als veel zoekopdrachten naar dezelfde uitdrukking verwijzen.

PS je angsten lijken ongegrond. In dit eenvoudige voorbeeld is SQL Server tenminste slim genoeg om de berekening maar één keer uit te voeren, ook al heb je er twee keer naar verwezen. Ga je gang en vergelijk de plannen; je zult zien dat ze identiek zijn. Als u een complexere casus heeft waarbij u de uitdrukking meerdere keren ziet geëvalueerd, post dan de complexere vraag en de plannen.

Hier zijn 5 voorbeeldquery’s die allemaal exact hetzelfde uitvoeringsplan opleveren:

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;
SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;

Resulterend plan voor alle vijf zoekopdrachten:

Antwoord 2, autoriteit 5%

U kunt dit doen met cross apply

SELECT c.BalanceDue AS BalanceDue
FROM Invoices
cross apply (select (InvoiceTotal - PaymentTotal - CreditTotal) as BalanceDue) as c
WHERE  c.BalanceDue  > 0;

Antwoord 3

Het is eigenlijk mogelijk om effectief een variabele te definiëren die kan worden gebruikt in zowel de SELECT-, WHERE- als andere clausules.

Een cross-join zorgt niet noodzakelijkerwijs voor een juiste binding aan de tabelkolommen waarnaar wordt verwezen, maar OUTER APPLY wel – en behandelt nulls transparanter.

SELECT
    vars.BalanceDue
FROM
    Entity e
OUTER APPLY (
    SELECT
        -- variables   
        BalanceDue = e.EntityTypeId,
        Variable2 = ...some..long..complex..expression..etc...
    ) vars
WHERE
    vars.BalanceDue > 0

Een pluim voor Syed Mehroz Alam.

Antwoord 4

Als tijdelijke oplossing om de evaluatie van de SELECT-component vóór de WHERE-component te forceren, kunt u de eerste in een subquery plaatsen terwijl de laatste blijft
in de hoofdvraag:

SELECT * FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices) AS temp
WHERE BalanceDue > 0