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:

SELECT NewColumn=

CASE

WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'

END,

Table2.SelectCol2

FROM Table1

JOIN Table2 ON NewColumn = Table2.ColumnName

GROUP BY NewColumn, Table2.SelectCol2

ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,

DC Ross

View 5 Replies

Error In Using Alias Column Name As Function Parameter

May 16, 2012

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?

View 4 Replies View Related

How To Recall Column Name Defined In CASE Function

Aug 17, 2007

Hello all,

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

View 3 Replies View Related

Joining And Grouping Using SQL

Jul 20, 2005

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

View 2 Replies View Related

CASE Alias In WHERE

Jul 20, 2005

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

View 3 Replies View Related

Using A Field Alias For A CASE Statement In A GROUP BY Clause

May 20, 2004

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

Regards,

Dave

View 5 Replies View Related

SQL Server 2012 :: Case Statement - How To Get Values Into Alias Extension

Oct 6, 2014

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>

View 9 Replies View Related

Transact SQL :: How To Use A Between In Case Statement For Joining 2 Tables

Jul 27, 2015

below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.

Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber

[code]....

how to modify my code such that it works for my condition?

View 10 Replies View Related

Grouping (Case)

Nov 27, 2006

View 2 Replies View Related

Grouping With Case Statement?

Apr 2, 2008

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

I would like it to look like this

PT_UD_KeyPatternPrice CastingPrice MachiningPrice
FG-020006-000 750.00 1840.00 3945.00

What am I doing wrong?
Here is my query

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

View 3 Replies View Related

SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?

Feb 26, 2015

Is there a way to display a column alias as part of the result set column labels?

View 9 Replies View Related

Error Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name

Jan 15, 2008

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

View 2 Replies View Related

Transact SQL :: Grouping With Case Statement

Aug 9, 2015

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,

[Code] ....

View 4 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 9, 2008

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.

View 11 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 10, 2008

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 data

Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

View 14 Replies View Related

Joining Function - Query

Feb 27, 2008

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

can anyone tell me how to join functions in sql?

thanks.

View 4 Replies View Related

Transact SQL :: How To Add Subtotal / Grandtotal Using Case And Grouping Sets

Jun 15, 2015

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

[Code] .....

View 4 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

View 3 Replies View Related

Grouping Function

Mar 14, 2008

I have a table sys_building listed records as follows:

sys Buildings
-------------------------------
sys1 building1
sys1 building2
sys1 building3
sys2 building4
sys2 building5
sys3 building6
sys3 building7

Now, I need to write a query or a sp to return a list as follows:

sys1(3)
building1
building2
building3
sys2(2)
building4
building5
sys3(2)
building6
building7

I used grouping function with cube and got a result as follows:

building1
building2
building3
sys1(3)
building4
building5
sys2(2)
building6
building7
sys3(2)

The summary count rows are only shown by the end of each building group. How can I move sys subject to top of each building group?

Thanks very much in advance for you kind help!

Sue

View 8 Replies View Related

Several Alias For The Same Column

Mar 2, 2005

In SQLServer I can't change my SQL column name.

But I need to see another field name in query tool (Excel)

How can I do that (alias..., description ?

Thanks

View 1 Replies View Related

Column Alias

Aug 30, 2006

Hello everyone.

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.

View 14 Replies View Related

Order By Column Alias

Feb 12, 2007

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

View 4 Replies View Related

Column Alias In Views

Oct 31, 2005

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?

Thanks for your time.

Regards:
Prathmesh

View 8 Replies View Related

Using Column Alias In SELECT (was Asking)

Nov 25, 2006

Hi,

I have a question.

select name, count, 1 Aa c1, 2 as c2, c1+c2 As total
from table1

From this example, the program will give out error message,
c1, and c2 are invalid columns.

In MS Access, it works. But, SQL query Analayer doesn't work this statement.
So, does query analayzer handle this case?

Thanks.

View 3 Replies View Related

Column Name Alias Concatenation

Feb 20, 2004

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.com

View 4 Replies View Related

Use Column Alias In Another Calculation

Jul 20, 2005

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

View 6 Replies View Related

Column Alias As Variable

Jun 28, 2006

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @myVariable FROM Table1

View 4 Replies View Related

Transact SQL :: Summing With A Function In Grouping

Jun 23, 2015

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

declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))

[code]...

I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :

Breaks 00:02:03:40
Meetings 00:00:01:50
Running 15:21:07:16

I think this can be done with windows functions, but how I don't know.

View 10 Replies View Related

Counting Multiple Values From The Same Column And Grouping By A Another Column

Sep 16, 2004

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

View 2 Replies View Related

Multi Column Grouping Vs Page/Column Break

Mar 18, 2007

In my report i would have 2 groups.

The first group should cause a real page break, the secound group should cause a column break.

Any idea on how to realize this, i've been playing with quite some settings but .....

So, any help ...

View 1 Replies View Related

Multi Column Subquery? W/ Alias

Jun 12, 2008

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"

View 10 Replies View Related

Using Alias Column Names For Calculations

Oct 3, 2007

Hi,
I want to have a query where in i can use alias column names in the same query.
like eg
select 1 as 'a', 2 as 'b', a+b as 'c'

note that this query is getting big and is using sub queries.

Kindly help.

Thanks

View 5 Replies View Related

Select Alias -- Invalid Column Name

Jun 26, 2007

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

View 5 Replies View Related