Filter result using alias-CodePudding

I have a query like this

SELECT 
    Type = TXL.Descript, Jurisdiction = CASE WHEN TX.State = 'KD' THEN 'FD' 
    WHEN TX.County > '' AND TX.County IS NOT NULL THEN 'Local' ELSE 'Country' END,
    State = CASE WHEN TX.State = 'FD' OR (TX.County > '' AND TX.County IS NOT NULL) THEN '' ELSE (select top 1 istIntlStateDesc from IntStateM ISM where ISM.istIntlStateCode = TX.MtcState) END,
    Local = CASE WHEN TX.County > '' AND TX.County IS NOT NULL THEN TX.MtcCounty ELSE '' END,
    TaxCode = TX.TaxCode, TaxDescription = TX.TaxCodeDesc, EffectiveDate = ET.EtxEffectiveDate,
    Taxable = ''
FROM 
    ETaX ET WITH (NOLOCK) 
    INNER JOIN TxCast TX on ET.Code = TX.TaxCode 
    INNER JOIN TxLMast TXL on TXL.TCode = TX.Tax
WHERE 
    ET.TDate <= GETDATE() 
    AND ET.SDate > GETDATE()

In the UI I have a table and I want to filer each column by passing the column name and what my code is doing is adding an AND condition to the WHERE clause while filtering but if I am passing the alias name (Ex. Jurisdiction) I am getting an error like - Invalid column name 'Jurisdiction', I also can't pass the actual column name as I am using case statement in the query, for example alias Jurisdiction value is coming by combing 2 column name so, is there any way it will work even passing the alias name or any better way to do it like dynamic SQL or any other workaround?

CodePudding user response：

If I understand correctly, you want to filter by your "aliased" fields. To do so, you have 2 options:

Use subquery writing a SELECT FROM your entire query and then filtering by Jurisdiction:

SELECT * FROM
    (SELECT 
    Type = TXL.Descript, Jurisdiction = CASE WHEN TX.State = 'KD' THEN 'FD' 
    WHEN TX.County > '' AND TX.County IS NOT NULL THEN 'Local' ELSE 'Country' END,
    State = CASE WHEN TX.State = 'FD' OR (TX.County > '' AND TX.County IS NOT NULL) THEN '' ELSE (select top 1 istIntlStateDesc from IntStateM ISM where ISM.istIntlStateCode = TX.MtcState) END,
    Local = CASE WHEN TX.County > '' AND TX.County IS NOT NULL THEN TX.MtcCounty ELSE '' END,
    TaxCode = TX.TaxCode, TaxDescription = TX.TaxCodeDesc, EffectiveDate = ET.EtxEffectiveDate,
    Taxable = ''
FROM 
    ETaX ET WITH (NOLOCK) 
    INNER JOIN TxCast TX on ET.Code = TX.TaxCode 
    INNER JOIN TxLMast TXL on TXL.TCode = TX.Tax
WHERE 
    ET.TDate <= GETDATE() 
    AND ET.SDate > GETDATE()) AS AUX_QUERY
    WHERE
    Jurisdiction = -your condition here-

(Note now Jurisdiction is a result field from AUX_QUERY)

Repeat the full CASE WHEN TX.State = 'KD' THEN 'FD' WHEN TX.County > '' AND TX.County IS NOT NULL THEN 'Local' ELSE 'Country' END that you called Jurisdiction in the WHERE clause (or wherever you want to use those calculated fields:

    SELECT 
            Type = TXL.Descript, Jurisdiction = CASE WHEN TX.State = 'KD' THEN 'FD' 
            WHEN TX.County > '' AND TX.County IS NOT NULL THEN 'Local' ELSE 'Country' END,
            State = CASE WHEN TX.State = 'FD' OR (TX.County > '' AND TX.County IS NOT NULL) THEN '' ELSE (select top 1 istIntlStateDesc from IntStateM ISM where ISM.istIntlStateCode = TX.MtcState) END,
            Local = CASE WHEN TX.County > '' AND TX.County IS NOT NULL THEN TX.MtcCounty ELSE '' END,
            TaxCode = TX.TaxCode, TaxDescription = TX.TaxCodeDesc, EffectiveDate = ET.EtxEffectiveDate,
            Taxable = ''
        FROM 
            ETaX ET WITH (NOLOCK) 
            INNER JOIN TxCast TX on ET.Code = TX.TaxCode 
            INNER JOIN TxLMast TXL on TXL.TCode = TX.Tax
        WHERE 
            ET.TDate <= GETDATE() 
            AND ET.SDate > GETDATE()) AS AUX_QUERY
            AND
            CASE WHEN TX.State = 'KD' THEN 'FD' 
            WHEN TX.County > '' AND TX.County IS NOT NULL THEN 'Local' ELSE 'Country' END = -your condition here-

CodePudding user response：

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt
FROM tbl_name
WHERE cnt > 0
GROUP BY id;

CodePudding user response：

Aliases cannot be used like that, look at this example

declare @table1 table (id int, text1 varchar(50), text2 varchar(50))
insert into @table1 values (1, 'one', 'two')

select t1.id,
       fullname = t1.text1   ' '   t1.text2
from   @table1 t1
where  fullname = 'one two'

This will throw this exception

Invalid column name 'fullname'

The most simple solution (imho) is this

select t.id,
       t.fullname
from   ( select t1.id,
                fullname = t1.text1   ' '   t1.text2
         from   @table1 t1
       ) t
where  t.fullname = 'one two'

This allows the database to fully evaluate the aliased columns, and therefore it will known them so you can use the aliased columns just like other columns