April 14, 2004 at 3:03 pm
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
April 15, 2004 at 1:26 am
Hello,
I suppose this is just a simplified version of your real query, so I won't ask what it is supposed to do... Anyway, IMHO the problem is that you use the NewColumn in GROUP BY. Try replacing it with the CASE statement - like that:
SELECT
CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END as NewColumn,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END, Table2.SelectCol2
ORDER BY Table2.SelectCol2
It is untested, but I use the Group By with CASE quite often in queries and I know that it works only if the CASE statement is repeated in GROUP BY clause.
Hope that helps!
April 15, 2004 at 4:17 am
or use a nested table expression :
SELECT T1.NewColumn, Table2.SelectCol2, count(*) as Counter
from ( select *
CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
else Table1.Name
END as NewColumn
FROM Table1) T1
inner JOIN Table2 ON T1.NewColumn = Table2.ColumnName
GROUP BY T1.NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2
or
-- designed with standard pubs-db
SELECT CASE WHEN A.au_lname LIKE '%ng%' THEN '172-32-1176'
else A.au_id
END as NewColumn
, TA.title_id
, count(*) as counter
FROM [Pubs].[dbo].[authors] A
inner join [Pubs].[dbo].[titleauthor] TA
on CASE WHEN A.au_lname LIKE '%ng%' THEN '172-32-1176'
else A.au_id
END = TA.au_id
-- where au_lname like '%ng%' --just for test
group by CASE WHEN A.au_lname LIKE '%ng%' THEN '172-32-1176' else A.au_id END
, TA.title_id
order by TA.title_id
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2004 at 4:29 pm
I am having somewhat of a similar problem. I am using a CASE statement to differentiate between records that have been tagged or not tagged (tblEquipInfo.AssetTag). The user has the option to pull either tagged, non-tagged, or all records based upon the an input parameter. When trying to evaluate for the requested records in the HAVING statement I am failing to pull records either flagged as T or U. I have tried this various ways but have run dry on ideas. As in the previous post I had my parameter as typed as CHAR and did not use the CASE statement in the output so I tried fixing those two things but no soap. Stuck in a rut. Hoping someone has done this before an can help. Thanks,
Alter Procedure qryFixedAssets @U_Untagged_T_Tagged_PctSign_All AS varchar (2)As SELECT tblInvInfo.POID, tblInvInfo.InvID, tblDeptInfo.DeptName,tblDeptInfo .ContactName, tblDeptInfo.ContactPhone,tblEquipInfo .Type, tblEquipInfo.Make, tblEquipInfo.Model,tblEquipInfo .Serial#, tblEquipInfo.AssetTag,tblInvInfo .PV#, tblRCInfo.RcvdDate, tblEquipInfo.EquipID,tblEquipInfo .Value,CASEWHEN tblEquipInfo.AssetTag IS NULL THEN 'U'ELSE 'T'END AS 'AssetTagSelect'FROM dbo.tblDeptInfo INNER JOINdbo .tblInvInfo INNER JOINdbo .tblEquipInfo ONdbo .tblInvInfo.InvID = dbo.tblEquipInfo.InvId INNER JOINdbo .tblRCInfo ONdbo .tblInvInfo.InvID = dbo.tblRCInfo.InvID ANDdbo .tblInvInfo.POID = dbo.tblRCInfo.POID ONdbo .tblDeptInfo.DeptID = dbo.tblEquipInfo.DeptIDWHERE ((tblEquipInfo.Capital) = '1')GROUP BY dbo.tblInvInfo.POID, dbo.tblInvInfo.InvID,dbo .tblDeptInfo.DeptName, dbo.tblDeptInfo.ContactName,dbo .tblDeptInfo.ContactPhone, dbo.tblEquipInfo.Type,dbo .tblEquipInfo.Make, dbo.tblEquipInfo.Model,dbo .tblEquipInfo.Serial#, dbo.tblEquipInfo.AssetTag,dbo .tblInvInfo.PV#, dbo.tblRCInfo.RcvdDate,dbo .tblEquipInfo.EquipID, dbo.tblEquipInfo.Value, CASE WHEN tblEquipInfo.AssetTag IS NULL THEN 'U' ELSE 'T' ENDHAVING 'AssetTagSelect' LIKE @U_Untagged_T_Tagged_PctSign_All /*This does not evaluate for U or T. Value not being stored or referenced? */ |
April 15, 2004 at 4:51 pm
SELECT
NewColumn= CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END, Table2.SelectCol2into #ResultSet1FROM Table1JOIN Table2 ON NewColumn = Table2.ColumnName --------------------------------------- query result set as required
-------------------------------------Select NewColumn , SelectCol2 from #ResultSet1GROUP BY NewColumn, SelectCol2ORDER BY SelectCol2 --------------------------------------- clean up-------------------------------------drop table #ResultSet1 just my 2 cents worth All the best.Tony.April 16, 2004 at 12:06 am
I don't know if your Grouping is realy needed (all selected columns are involved), but here's me shot :
-- @U_Untagged_T_Tagged_PctSign_All = '%' for ALL rows
-- 'U' for Untagged
-- 'T' for Tagged
Alter Procedure qryFixedAssets @U_Untagged_T_Tagged_PctSign_All AS char (1) = '%'
As
SELECT II.POID, II.InvID, DI.DeptName,
DI.ContactName, DI.ContactPhone,
EI.Type, EI.Make, EI.Model,
EI.Serial#, EI.AssetTag,
II.PV#, RI.RcvdDate, EI.EquipID,
EI.Value,
CASE
WHEN EI.AssetTag IS NULL THEN 'U'
ELSE 'T'
END AS 'AssetTagSelect'
FROM dbo.tblDeptInfo DI
INNER JOIN dbo.tblInvInfo II
INNER JOIN dbo.tblEquipInfo EI
ON II.InvID = EI.InvId
INNER JOIN dbo.tblRCInfo RI
ON II.InvID = RI.InvID
AND II.POID = RI.POID
ON DI.DeptID = EI.DeptID
WHERE ((EI.Capital) = '1')
and CASE WHEN EI.AssetTag IS NULL THEN 'U' ELSE 'T' END like @U_Untagged_T_Tagged_PctSign_All
GROUP BY II.POID, II.InvID,
DI.DeptName, DI.ContactName,
DI.ContactPhone, EI.Type,
EI.Make, EI.Model,
EI.Serial#, EI.AssetTag,
II.PV#, RI.RcvdDate,
EI.EquipID, EI.Value,
CASE WHEN EI.AssetTag IS NULL THEN 'U' ELSE 'T' END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 16, 2004 at 10:28 am
Thank you ALZDBA. Your modifications were right on. You were right about the group by, it was not needed. As is typical I inherited this from someone else and trying not to break what they had working. Regards, PhoenixDBA |
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply