Référence à un alias (calculé dans SELECT) dans la clause WHERE

Question

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

La valeur calculée BalanceDue qui est définie comme variable dans la liste des colonnes sélectionnées ne peut pas être utilisée dans la fonction WHERE clause.

Y a-t-il un moyen de le faire ? Dans cette question connexe ( Utilisation d'une variable dans un Select Statment MySQL dans une clause Where ), il semble que la réponse soit, en fait, non, il suffit d'écrire le calcul ( et effectuer ce calcul dans la requête) deux fois, ce qui n'est pas satisfaisant.

Answer 1

Vous ne pouvez pas faire référence à un alias sauf dans ORDER BY parce que SELECT est l'avant-dernière clause évaluée. Il existe deux solutions :

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

Ou répétez simplement l'expression :

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

Je préfère cette dernière. Si l'expression est extrêmement complexe (ou coûteuse à calculer), vous devriez probablement envisager une colonne calculée (et peut-être persistée) à la place, surtout si de nombreuses requêtes font référence à cette même expression.

PS vos craintes semblent infondées. Dans cet exemple simple au moins, SQL Server est suffisamment intelligent pour n'effectuer le calcul qu'une seule fois, même si vous l'avez référencé deux fois. Comparez les plans, vous verrez qu'ils sont identiques. Si vous avez un cas plus complexe où vous voyez l'expression évaluée plusieurs fois, veuillez poster la requête plus complexe et les plans.

Voici 5 exemples de requêtes qui produisent toutes exactement le même plan d'exécution :

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;

Plan résultant pour les cinq requêtes :

Answer 2

Vous pouvez le faire en utilisant cross apply

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

Answer 3

Il est en fait possible de définir efficacement une variable qui peut être utilisée à la fois dans les clauses SELECT, WHERE et autres.

Une jointure croisée ne permet pas nécessairement une liaison appropriée avec les colonnes de la table référencée, alors que OUTER APPLY le permet - et traite les valeurs nulles de manière plus transparente.

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

Félicitations à Syed Mehroz Alam .

Answer 4

Une approche simple

SELECT *  FROM Invoices GROUP BY id
HAVING(InvoiceTotal - PaymentTotal - CreditTotal) > 0

Answer 5

Pour forcer l'évaluation de la clause SELECT avant la clause WHERE, vous pouvez placer la première dans une sous-requête, tandis que la seconde est conservée. dans la requête principale :

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