Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Using column alias in my where filter Rate Topic Display Mode Topic Options
Author
 Message
 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 InvoicesWhere "10%" and "Plus 10%" >1000
Post #570708
 Posted Tuesday, September 16, 2008 9:11 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 12:08 PM Points: 4,358, Visits: 9,367
 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 InvoicesWhere "10%" and "Plus 10%" >1000You 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 WilliamsProblems are opportunites brilliantly disguised as insurmountable obstacles.How to post questions to get better answers fasterManaging Transaction Logs
Post #570721
 Posted Tuesday, September 16, 2008 9:13 PM
 SSC-Dedicated Group: Administrators Last Login: Yesterday @ 10:12 AM Points: 32,273, Visits: 14,526
Post #570722
 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
 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
 Posted Friday, October 07, 2011 4:30 PM
 Old Hand Group: General Forum Members Last Login: Monday, November 18, 2013 6:32 AM Points: 321, Visits: 1,413
 "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 InvoicesWhere [10%] > 1000Ken
Post #1187418
 Posted Saturday, October 08, 2011 8:20 AM
 SSChampion Group: General Forum Members Last Login: Today @ 12:00 AM Points: 12,230, Visits: 29,349
 Steve Jones - SSC Editor (9/16/2008)Which results?The alias ones? Usewhere [10%] > 1000Jeffry 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 Group: General Forum Members Last Login: Monday, November 18, 2013 6:32 AM Points: 321, Visits: 1,413
 Think I got it now after seeing it taught at PASS last week. The order in which a Select statement is deconstructed:fromon/joinwheregroup byhavingselectdistinct/order by/topWhich 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 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.LOGICALREFWHERE 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.FICHEREFORDER BY PT.DATE_`Print results : [color='red']Msg 207, Level 16, State 1, Line 61Invalid column name 'FATURA_DURUMU'.[/color]Thanks,
Post #1284022
 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 test1DECLARE @FATURA_SAY INTSELECT 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.LOGICALREFWHERE 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.FICHEREFORDER BY PT.DATE_`
Post #1284051

 Permissions