September 16, 2008 at 7:30 pm
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
September 16, 2008 at 9:11 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 16, 2008 at 9:13 pm
Which results?
The alias ones? Use
where [10%] > 1000
September 16, 2008 at 9:25 pm
Thanks for the help.
February 5, 2010 at 4:15 pm
Thanks! This post helped out quite a bit.
October 7, 2011 at 4:30 pm
"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
October 8, 2011 at 8:20 am
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
October 18, 2011 at 9:25 am
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 🙂
April 16, 2012 at 5:28 am
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 CARI_KOD,
CC.DEFINITION_ ÜNVAN,
CASE(PT.MODULENR)
WHEN 3 THEN'SIPARIS'
WHEN 4 THEN'FATURA'
WHEN 5 THEN'CARI FIS'
WHEN 6 THEN'Çek - Senet'
WHEN 7 THEN'BANKA'
WHEN 10 THEN'KASA'
ELSE 'DIGER'
END 'TÜR',
--C.MODULENR, --Bu satir Pasif Yapilabilir.
CASE
WHEN (PT.MODULENR=4) AND (PT.TRCODE=2) THEN 'PER.SATIS.IADE.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 'SATIS SIPARISI'
END 'Fis 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 'Subat'
WHEN MONTH(PT.DISCDUEDATE)= '3' THEN 'Mart'
WHEN MONTH(PT.DISCDUEDATE)= '4' THEN 'Nisan'
WHEN MONTH(PT.DISCDUEDATE)= '5' THEN 'Mayis'
WHEN MONTH(PT.DISCDUEDATE)= '6' THEN 'Haziran'
WHEN MONTH(PT.DISCDUEDATE)= '7' THEN 'Temmuz'
WHEN MONTH(PT.DISCDUEDATE)= '8' THEN 'Agustos'
WHEN MONTH(PT.DISCDUEDATE)= '9' THEN 'Eylül'
WHEN MONTH(PT.DISCDUEDATE)= '10' THEN 'Ekim'
WHEN MONTH(PT.DISCDUEDATE)= '11' THEN 'Kasim'
WHEN MONTH(PT.DISCDUEDATE)= '12' THEN 'Aralik'
END AY,
PT.TRCODE FIS_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 ISLEM KONTROL ET'
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'MANUEL KAPATILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'FATURA KESILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID>0) THEN 'FATURA KES'
END FATURA_DURUMU,
PT.FICHEREF AS [Fis Referansi]
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 :
Msg 207, Level 16, State 1, Line 61
Invalid column name 'FATURA_DURUMU'.
Thanks,
April 16, 2012 at 6:29 am
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 KESILMIS - 1.Invoice attached.
2.FATURA KESILMIS - 2.Invoice attached.
3.FATURA KESILMIS - 3.Invoice attached.
4.FATURA KESILMIS - 4.Invoice attached.
5.FATURA KESILMIS - 5.Invoice attached.
6.FATURA KESILMIS - 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 CARI_KOD,
CC.DEFINITION_ ÜNVAN,
CASE(PT.MODULENR)
WHEN 3 THEN'SIPARIS'
WHEN 4 THEN'FATURA'
WHEN 5 THEN'CARI FIS'
WHEN 6 THEN'Çek - Senet'
WHEN 7 THEN'BANKA'
WHEN 10 THEN'KASA'
ELSE 'DIGER'
END 'TÜR',
--C.MODULENR, --Bu satir Pasif Yapilabilir.
CASE
WHEN (PT.MODULENR=4) AND (PT.TRCODE=2) THEN 'PER.SATIS.IADE.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 'SATIS SIPARISI'
END 'Fis 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 'Subat'
WHEN MONTH(PT.DISCDUEDATE)= '3' THEN 'Mart'
WHEN MONTH(PT.DISCDUEDATE)= '4' THEN 'Nisan'
WHEN MONTH(PT.DISCDUEDATE)= '5' THEN 'Mayis'
WHEN MONTH(PT.DISCDUEDATE)= '6' THEN 'Haziran'
WHEN MONTH(PT.DISCDUEDATE)= '7' THEN 'Temmuz'
WHEN MONTH(PT.DISCDUEDATE)= '8' THEN 'Agustos'
WHEN MONTH(PT.DISCDUEDATE)= '9' THEN 'Eylül'
WHEN MONTH(PT.DISCDUEDATE)= '10' THEN 'Ekim'
WHEN MONTH(PT.DISCDUEDATE)= '11' THEN 'Kasim'
WHEN MONTH(PT.DISCDUEDATE)= '12' THEN 'Aralik'
END AY,
PT.TRCODE FIS_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 ISLEM KONTROL ET'
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'MANUEL KAPATILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'FATURA KESILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID>0) THEN 'FATURAYI KES'
END FATURA_DURUMU,
PT.FICHEREF AS [Fis Referansi]
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_
April 16, 2012 at 8:15 am
lsuersoy (4/16/2012)
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 KESILMIS - 1.Invoice attached.
2.FATURA KESILMIS - 2.Invoice attached.
3.FATURA KESILMIS - 3.Invoice attached.
4.FATURA KESILMIS - 4.Invoice attached.
5.FATURA KESILMIS - 5.Invoice attached.
6.FATURA KESILMIS - 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 CARI_KOD,
CC.DEFINITION_ ÜNVAN,
CASE(PT.MODULENR)
WHEN 3 THEN'SIPARIS'
WHEN 4 THEN'FATURA'
WHEN 5 THEN'CARI FIS'
WHEN 6 THEN'Çek - Senet'
WHEN 7 THEN'BANKA'
WHEN 10 THEN'KASA'
ELSE 'DIGER'
END 'TÜR',
--C.MODULENR, --Bu satir Pasif Yapilabilir.
CASE
WHEN (PT.MODULENR=4) AND (PT.TRCODE=2) THEN 'PER.SATIS.IADE.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 'SATIS SIPARISI'
END 'Fis 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 'Subat'
WHEN MONTH(PT.DISCDUEDATE)= '3' THEN 'Mart'
WHEN MONTH(PT.DISCDUEDATE)= '4' THEN 'Nisan'
WHEN MONTH(PT.DISCDUEDATE)= '5' THEN 'Mayis'
WHEN MONTH(PT.DISCDUEDATE)= '6' THEN 'Haziran'
WHEN MONTH(PT.DISCDUEDATE)= '7' THEN 'Temmuz'
WHEN MONTH(PT.DISCDUEDATE)= '8' THEN 'Agustos'
WHEN MONTH(PT.DISCDUEDATE)= '9' THEN 'Eylül'
WHEN MONTH(PT.DISCDUEDATE)= '10' THEN 'Ekim'
WHEN MONTH(PT.DISCDUEDATE)= '11' THEN 'Kasim'
WHEN MONTH(PT.DISCDUEDATE)= '12' THEN 'Aralik'
END AY,
PT.TRCODE FIS_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 ISLEM KONTROL ET'
WHEN (PT.MODULENR<>3) AND (PT.TRCODE<>1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'MANUEL KAPATILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID=0) THEN 'FATURA KESILMIS'
WHEN (PT.MODULENR=3) AND (PT.TRCODE=1) AND (SUM(PT.TOTAL)-PT.PAID>0) THEN 'FATURAYI KES'
END FATURA_DURUMU,
PT.FICHEREF AS [Fis Referansi]
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_
I'm sorry, but are you posting a solution or a question? If a question, please start a new thread.
April 16, 2012 at 8:43 am
Hi ,
Is it possible to use the results of a Case where filter?
April 17, 2012 at 11:27 pm
I am not sure why you would not want to create the CASE statement equivalent in the where clause by using AND, OR, and () operators.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply