

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




SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, November 20, 2015 10:02 AM
Points: 4,369,
Visits: 9,604


tpinto (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




SSCDedicated
Group: Administrators
Last Login: Yesterday @ 5:12 PM
Points: 32,695,
Visits: 16,995





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





Grasshopper
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.




Old Hand
Group: General Forum Members
Last Login: Thursday, November 19, 2015 12:51 PM
Points: 354,
Visits: 1,637


"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




SSChampion
Group: General Forum Members
Last Login: Yesterday @ 12:38 PM
Points: 13,883,
Visits: 35,808


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
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




Old Hand
Group: General Forum Members
Last Login: Thursday, November 19, 2015 12:51 PM
Points: 354,
Visits: 1,637


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




SSC Rookie
Group: General Forum Members
Last Login: Monday, November 3, 2014 3:28 AM
Points: 26,
Visits: 88


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,




SSC Rookie
Group: General Forum Members
Last Login: Monday, November 3, 2014 3:28 AM
Points: 26,
Visits: 88


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_



