Joining On And Grouping By CASE Function Column Alias (URGENT)
Apr 14, 2004
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code: SELECT CASE WHEN <CONDITION> THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE)) ELSE 0 END AS CurrentDateAdj, Year(CurrentDateAdj) + '_' + 'some text and processing') FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'
If anyone knows a solution, please kindly let me know.
Thank you!
Here is the sample code:
ALTER PROCEDURE [dbo].[sp_premium_register] @PolicyType AS VARCHAR(10), @ReportFrom AS DATETIME, @ReportTo AS DATETIME, @Business AS VARCHAR(1), @Term AS VARCHAR(1)
SELECT ColumnA, ColumnB,
SLA = CASE WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L' WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S' END
FROM DNIssue D LEFT OUTER JOIN KILRIShare S ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo) LEFT OUTER JOIN KILPolicy P ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo) LEFT OUTER JOIN v_report_KILDNFund F ON (D.DebitNote = F.DebitNote) LEFT OUTER JOIN PolicyProfile R ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType
WHERE SLA = @Term
Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate
I have two tables... Table1 and table2 and I need to reconcile themwith each other.Table1 has the fields Product number, invoice number, price, vatamount and total.Table2 has the same data but in a slightly different format...It has Product Number, invoice number, Price and type.Type will say Vat or sale and amount will be the vat amount or saleamountWhat is on one row in Table1, will be spread accross 2 rows in Table2.It means that Invoice number is not unique in Table2.How do I either group the data in Table2, so I can join it with Table1or make Table2 the same format as Table1.If there is something else you can think of to help me, by all meanssuggest away.Regards,Ciarán
Hello,I found members of this group very helpful for my last queries.Have one problem with CASE. I can use the column name alias in Order By Clausebut unable to use it in WHERE CLAUSE.PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.QUERY:SELECTM.SECS =CASEWHEN NO_OF_SEC IS NULL THEN -1WHEN NO_OF_SEC =0 THEN 1ELSE NO_OF_SECENDFROM DOWNLOAD_MASTER MWHERE M.SECS < 100ORDER BY M.SECSHoping for a immediate reply.thanks in advance
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 = CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END , fieldname2 , fieldname3 FROM tablename1 GROUP BY CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.
Agent_ID Agent_ID2 11111 22222 <code> SELECT DISTINCT CASE WHEN [AGENT_ID] is not null then AGENT_ID when agent_id2 is not null then agent_id2 end as extension FROM [AA_Helper].[dbo].[tblEmpData] </code>
I am attempting to get one line with all the price info for three fields. The end result looks like this PT_UD_KeyPatternPrice CastingPrice MachiningPrice FG-020006-000 0.00 1840.00 0.00 FG-020006-000 750.00 0.00 0.00 FG-020006-000 0.00 0.00 3945.00
SELECT PT_UD_KEY, 'PatternPrice' = CASE WHEN PT_UD_NUMBER = 1 THEN TOTAL ELSE 0.00 END, 'CastingPrice' = CASE WHEN PT_UD_NUMBER = 15 THEN TOTAL ELSE 0.00 END, 'MachiningPrice' = CASE WHEN PT_UD_NUMBER = 3 THEN TOTAL ELSE 0.00 END FROM CAL.dbo.EXT00103 WHERE PT_WINDOW_ID='ITEM PRICE' AND LEFT(PT_UD_KEY,2)='FG' AND PT_UD_NUMBER IN (1,3,15) AND TOTAL >0.00 ORDER BY PT_UD_KEY
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End
--Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select case when @include_browser = 1 then browser_name end as browser_name, case when @include_browser_version = 1 then browser_version end as browser_version,
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60)) RETURNS @copdmailinglist TABLE ( list_ varchar(60) , title_ varchar(255) , desc_ varchar(255), message_id int , txt varchar(255) , cnt int , cnt_txt varchar(255) )
--Returns a result set that lists all the copds AS BEGIN WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS (select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc left outer join ListManager.dbo.get_mailingidcopd_('') gmc on gmc.name_ = osc.title_ where list_ = @list_ )
-- copy the required columns to the result of the function INSERT @copdmailinglist SELECT list_ , title_ , message_id , txt , cnt , cnt_txt FROM ListManager.dbo.[List Copd] RETURN END GO
i m getting error that Incorrect syntax near the keyword 'WITH'.
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT CustomerID, SalesPersonID, YEAR(OrderDate) AS 'OrderYear', SUM(TotalDue) AS 'TotalDue' FROM Sales.SalesOrderHeader
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
Hi All, I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.
e.g. Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;
In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.
My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?
Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1 FROM TheTable Where (Status = N'Shipped') Group By Customer
What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:
when pricelist = 1
when pricelist = 2
when pricelist = 3
pricelist -------- 1 2 3
Price -------- 912 -- (linked with 1) 234 -- (linked with 3) 56 -- (linked with 2) 3245 -- (linked with 3) 234 -- (linked with 1) 65 -- (linked with 2)
these 2 columns are in the same table^^
so what i want my query to generate is:
Price1 -------- 912 234
Price2 -------- 56 65
Price3 -------- 234 3245
Any help is apprecieated, thanks
if the above does not make sense to you maybe this will: "can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
Hi,I got 'Invalid Column Name NewCol1' when I query the following:Select col1, col2, (some calculation from the fields) as NewCol1,(some calculation from the fields) as NewCol2,NewCol1 = NewCol2 fromTable1 inner join Table2 inner join Table3....Where.....Basically, I want to find out if NewCol1 = NewCol2 after thecalculationAny advice?Thanks in advance. Your help would be greatly appreciated.Wanda
