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 @ 8:06 AM Points: 4,377, Visits: 9,710
 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 opportunities 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: Today @ 8:56 AM Points: 34,373, Visits: 18,592
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 5, 2010 4:15 PM
 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.
Post #860911
 Posted Friday, October 7, 2011 4:30 PM
 Old Hand Group: General Forum Members Last Login: Tuesday, December 6, 2016 1:33 PM Points: 362, Visits: 1,705
 "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 8, 2011 8:20 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:07 AM Points: 14,556, Visits: 38,439
 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--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!
Post #1187494
 Posted Tuesday, October 18, 2011 9:25 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, December 6, 2016 1:33 PM Points: 362, Visits: 1,705
 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, December 25, 2015 2:32 AM Points: 26, Visits: 92
 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, December 25, 2015 2:32 AM Points: 26, Visits: 92
 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