Using column alias in my where filter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Using column alias in my where filter

13 posts, Page 1 of 2

1 2 »»

Using column alias in my where filter

Rate Topic

Display Mode

Topic Options

Author

Message

t-pinto

Posted Tuesday, September 16, 2008 7:30 PM

SSC 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

Jeffrey Williams 3188

Posted Tuesday, September 16, 2008 9:11 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

t-pinto (9/16/2008)

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

Steve Jones - SSC Editor

Posted Tuesday, September 16, 2008 9:13 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

t-pinto

Posted Tuesday, September 16, 2008 9:25 PM

SSC Rookie

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

Thanks for the help.

Post #570726

Bryan Lanning

Posted Friday, February 05, 2010 4:15 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 17, 2012 2:25 PM
Points: 10, Visits: 55

Thanks! This post helped out quite a bit.

Post #860911

ken.trock

Posted Friday, October 07, 2011 4:30 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 317, Visits: 1,367

"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

Lowell

Posted Saturday, October 08, 2011 8:20 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 11,791, Visits: 28,070

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

ken.trock

Posted Tuesday, October 18, 2011 9:25 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 317, Visits: 1,367

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 Smile

Post #1192209

lsuersoy

Posted Monday, April 16, 2012 5:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 7:12 AM
Points: 26, Visits: 80

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

lsuersoy

Posted Monday, April 16, 2012 6:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 7:12 AM
Points: 26, Visits: 80

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions