Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using column alias in my where filter Expand / Collapse
Author
Message
Posted Tuesday, September 16, 2008 7:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 25, 2011 7:44 AM
Points: 25, Visits: 51
Hello,

I have an alias for 2 calculations. I would like to filter only those results that are >1000.
Here is my statement.



Select InvoiceTotal,
InvoiceTotal*(.10) as "10%",
InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where "10%" and "Plus 10%" >1000
Post #570708
Posted Tuesday, September 16, 2008 9:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 4,390, Visits: 9,536
t-pinto (9/16/2008)
Hello,

I have an alias for 2 calculations. I would like to filter only those results that are >1000.
Here is my statement.



Select InvoiceTotal,
InvoiceTotal*(.10) as "10%",
InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where "10%" and "Plus 10%" >1000


You have three options:

1) Use the calculation in the where clause:

Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where InvoiceTotal*(.10) > 1000
And InvoiceTotal+(InvoiceTotal*(.10)) > 1000;

2) Use a derived table:

Select InvoiceTotal
,[10%]
,[Plus 10%]
From (Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices) t
Where t.[10%] > 1000
And t.[Plus 10%] > 1000;

3) Use a common table expression (CTE):

With cte (InvoiceTotal, 10Percent, Plus10Percent)
As (Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices)
Select *
From cte
Where 10Percent > 1000
And Plus10Percent > 1000;



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #570721
Posted Tuesday, September 16, 2008 9:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:02 PM
Points: 33,276, Visits: 15,445
Which results?

The alias ones? Use

where [10%] > 1000







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #570722
Posted Tuesday, September 16, 2008 9:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 25, 2011 7:44 AM
Points: 25, Visits: 51
Thanks for the help.
Post #570726
Posted Friday, February 5, 2010 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:24 PM
Points: 10, Visits: 56
Thanks! This post helped out quite a bit.
Post #860911
Posted Friday, October 7, 2011 4:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 323, Visits: 1,463
"Use where [10%] > 1000 "

That's what I recall, that you can use aliased names in a Where or Group By clause. But this returns an error in SQL 2008.


Select InvoiceTotal,
InvoiceTotal*(.10) as "10%",
InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where [10%] > 1000


Ken
Post #1187418
Posted Saturday, October 8, 2011 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 12,918, Visits: 32,088
Steve Jones - SSC Editor (9/16/2008)
Which results?

The alias ones? Use

where [10%] > 1000


Jeffry Williams nailed the right techniques;
you can reference an alias in the ORDER BY clause, but not the where or group by clauses, so you have to abstract it out to a subquery/cte to use the assigned alias.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1187494
Posted Tuesday, October 18, 2011 9:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 323, Visits: 1,463
Think I got it now after seeing it taught at PASS last week. The order in which a Select statement is deconstructed:

from
on/join
where
group by
having
select
distinct/order by/top

Which is why we can only refer to the alias in the order by clause. One of those light bulb comes on moments for me
Post #1192209
Posted Monday, April 16, 2012 5:28 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:12 AM
Points: 26, Visits: 87
Thank you for your reply, but instead of a textual description of a test or calculation, you can propose a solution how to make equality comparison.Sample query follows.


SELECT
CONVERT(VARCHAR,PT.DATE_,104)'Vade Tarihi',
CC.CODE CARİ_KOD,
CC.DEFINITION_ ÜNVAN,

CASE(PT.MODULENR)
WHEN 3 THEN 'SİPARİŞ'
WHEN 4 THEN 'FATURA'
WHEN 5 THEN 'CARİ FİŞ'
WHEN 6 THEN 'Çek - Senet'
WHEN 7 THEN 'BANKA'
WHEN 10 THEN 'KASA'
ELSE 'DİĞER'
END 'TÜR',
--C.MODULENR, --Bu satır Pasif Yapılabilir.
CASE
WHEN (PT.MODULENR=4) AND (PT.TRCODE=2) THEN 'PER.SATIŞ.İADE.FTR'
WHEN (PT.MODULENR=5) AND (PT.TRCODE=3) THEN 'BORÇ DEKONTU'
WHEN (PT.MODULENR=7) AND (PT.TRCODE=3) THEN 'Gelen Havale'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) THEN 'SATIŞ SİPARİŞİ'
END 'Fiş Türü',
PT.DOCODE [Belge No],
PT.MODULENR MODÜL,
PT.TRCODE 'TRCODE',

CASE
WHEN PT.SIGN ='1' THEN 'ALACAK'
WHEN PT.SIGN ='0' THEN 'BORÇ'
END DURUM,

CASE
WHEN MONTH(PT.DISCDUEDATE)= '1' THEN 'Ocak'
WHEN MONTH(PT.DISCDUEDATE)= '2' THEN 'Şubat'
WHEN MONTH(PT.DISCDUEDATE)= '3' THEN 'Mart'
WHEN MONTH(PT.DISCDUEDATE)= '4' THEN 'Nisan'
WHEN MONTH(PT.DISCDUEDATE)= '5' THEN 'Mayıs'
WHEN MONTH(PT.DISCDUEDATE)= '6' THEN 'Haziran'
WHEN MONTH(PT.DISCDUEDATE)= '7' THEN 'Temmuz'
WHEN MONTH(PT.DISCDUEDATE)= '8' THEN 'Ağustos'
WHEN MONTH(PT.DISCDUEDATE)= '9' THEN 'Eylül'
WHEN MONTH(PT.DISCDUEDATE)= '10' THEN 'Ekim'
WHEN MONTH(PT.DISCDUEDATE)= '11' THEN 'Kasım'
WHEN MONTH(PT.DISCDUEDATE)= '12' THEN 'Aralık'
END AY,

PT.TRCODE FİŞ_TÜRÜ,
SUM(PT.TOTAL) TUTAR,
PT.PAID AS KAPANAN,
SUM(PT.TOTAL)-PT.PAID AS FARK,
CASE
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID<>0) THEN 'AÇIK İŞLEM KONTROL ET'
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'MANUEL KAPATILMIŞ'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'FATURA KESİLMİŞ'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID>0) THEN 'FATURA KES'
END FATURA_DURUMU,
PT.FICHEREF AS [Fiş Referansı]
FROM LG_011_01_PAYTRANS AS PT
INNER JOIN LG_011_CLCARD AS CC
ON PT.CARDREF = CC.LOGICALREF
WHERE CC.DEFINITION_ LIKE 'XXXXXXX' AND [FATURA_DURUMU] ='FATURA KES' -- I think this line second and got to do a modification and syntax.
GROUP BY
CC.CODE,
PT.TRCODE,
PT.DISCDUEDATE,
PT.TOTAL,
CC.DEFINITION_,
PT.MODULENR,
PT.SIGN,
PT.DATE_,
PT.DOCODE,
PT.PAID,
PT.FICHEREF
ORDER BY PT.DATE_


Print results :

[color='red']Msg 207, Level 16, State 1, Line 61
Invalid column name 'FATURA_DURUMU'.
[/color]

Thanks,
Post #1284022
Posted Monday, April 16, 2012 6:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:12 AM
Points: 26, Visits: 87
In this topic, you have an additional question to my post earlier.

One prepaid order exists.

Is there a payment plan installment of order 8.

that is, when the order and INVOICE SEPARATELY SHOULD be due for 8 pieces.

Prepaid order as you add, add up the INVOICES in reference to PAYTRANS table.

However, the customer's payment tools have ...Credit card, money transfer, etc.

Come into our bank account, we know that an instalment order of each of the said remittances.

This is further; I write a CURSOR to exist in each order instalment I got to.

FATURA_DURUMU column :

1.FATURA KESİLMİŞ - 1.Invoice attached.
2.FATURA KESİLMİŞ - 2.Invoice attached.
3.FATURA KESİLMİŞ - 3.Invoice attached.
4.FATURA KESİLMİŞ - 4.Invoice attached.
5.FATURA KESİLMİŞ - 5.Invoice attached.
6.FATURA KESİLMİŞ - 6.Invoice attached.
7.FATURAYI KES - 7. Add invoice.
8.FATURAYI KES - 8. Add invoice.


USE test1
DECLARE @FATURA_SAY INT
SELECT
CONVERT(VARCHAR,PT.DATE_,104)'Vade Tarihi',
CC.CODE CARİ_KOD,
CC.DEFINITION_ ÜNVAN,

CASE(PT.MODULENR)
WHEN 3 THEN 'SİPARİŞ'
WHEN 4 THEN 'FATURA'
WHEN 5 THEN 'CARİ FİŞ'
WHEN 6 THEN 'Çek - Senet'
WHEN 7 THEN 'BANKA'
WHEN 10 THEN 'KASA'
ELSE 'DİĞER'
END 'TÜR',
--C.MODULENR, --Bu satır Pasif Yapılabilir.
CASE
WHEN (PT.MODULENR=4) AND (PT.TRCODE=2) THEN 'PER.SATIŞ.İADE.FTR'
WHEN (PT.MODULENR=5) AND (PT.TRCODE=3) THEN 'BORÇ DEKONTU'
WHEN (PT.MODULENR=7) AND (PT.TRCODE=3) THEN 'Gelen Havale'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) THEN 'SATIŞ SİPARİŞİ'
END 'Fiş Türü',
PT.DOCODE [Belge No],
PT.MODULENR MODÜL,
PT.TRCODE 'TRCODE',

CASE
WHEN PT.SIGN ='1' THEN 'ALACAK'
WHEN PT.SIGN ='0' THEN 'BORÇ'
END DURUM,

CASE
WHEN MONTH(PT.DISCDUEDATE)= '1' THEN 'Ocak'
WHEN MONTH(PT.DISCDUEDATE)= '2' THEN 'Şubat'
WHEN MONTH(PT.DISCDUEDATE)= '3' THEN 'Mart'
WHEN MONTH(PT.DISCDUEDATE)= '4' THEN 'Nisan'
WHEN MONTH(PT.DISCDUEDATE)= '5' THEN 'Mayıs'
WHEN MONTH(PT.DISCDUEDATE)= '6' THEN 'Haziran'
WHEN MONTH(PT.DISCDUEDATE)= '7' THEN 'Temmuz'
WHEN MONTH(PT.DISCDUEDATE)= '8' THEN 'Ağustos'
WHEN MONTH(PT.DISCDUEDATE)= '9' THEN 'Eylül'
WHEN MONTH(PT.DISCDUEDATE)= '10' THEN 'Ekim'
WHEN MONTH(PT.DISCDUEDATE)= '11' THEN 'Kasım'
WHEN MONTH(PT.DISCDUEDATE)= '12' THEN 'Aralık'
END AY,

PT.TRCODE FİŞ_TÜRÜ,
SUM(PT.TOTAL) TUTAR,
PT.PAID AS KAPANAN,
SUM(PT.TOTAL)-PT.PAID AS FARK,
CASE
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID<>0) THEN 'AÇIK İŞLEM KONTROL ET'
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'MANUEL KAPATILMIŞ'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'FATURA KESİLMİŞ'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID>0) THEN 'FATURAYI KES'
END FATURA_DURUMU,
PT.FICHEREF AS [Fiş Referansı]
FROM LG_011_01_PAYTRANS AS PT
INNER JOIN LG_011_CLCARD AS CC
ON PT.CARDREF = CC.LOGICALREF
WHERE CC.DEFINITION_ LIKE 'Ersoy AYDIN'
GROUP BY
CC.CODE,
PT.TRCODE,
PT.DISCDUEDATE,
PT.TOTAL,
CC.DEFINITION_,
PT.MODULENR,
PT.SIGN,
PT.DATE_,
PT.DOCODE,
PT.PAID,
PT.FICHEREF
ORDER BY PT.DATE_


Post #1284051
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse