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

Using Case Statement With WHERE Clause...
Hi,
I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following:
SELECT ...
FROM ...
WHERE  (condition)
AND       (condition)
AND       (condition)
AND       (
                    CASE Table.Category
                          WHEN 'Drinks' THEN
                               Table1.Field1 = 1 -- Problem line
                          ELSE
                               Table1.Field1 = 1 AND Table1.Field2 = 1 -- Problem line
                   END
             )
In the above case, my WHERE Clause is dependent on one of the fields in a table. If it has a certain value then only Table1.Field1 is used otherwise Table1.Field1 & Field2 come into action.
I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working...
Thanks in advance...

View Replies !

---

If/Case Statement In Having/Where Clause - Please Help Me.
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int
AS
DECLARE @date DATETIME
SET @date = DATEADD(day,@days,GETDATE())
IF @username IS NOT NULL AND @search_text IS NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%')
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NOT NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND
(dbo.aspnet_Users.UserName = @username)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int,
@page int,
@page_size int
AS
WITH Results As
(
//QUERY)

)
SELECT * FROM Results
WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1))
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
RETURN  I will be grateful :-) 

View Replies !

---

Case Statement In Where Clause
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND

View Replies !

---

Case Statement In Where Clause
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!

View Replies !

---

CASE Statement In Where Clause?
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com

View Replies !

---

Case Statement In Where Clause
Hello

I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this

Thanks very much

declare @param varchar (100)
select @param = 'mytext

select
colA
,colB
,colC
from
mytable
where
(case
when @param is null then colA = 'group'
else colA = 'single'
end)

View Replies !

---

HAVING Clause Is A Case Statement???
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?

View Replies !

---

Case Statement In Where Clause
can i use case statement in where clause.
The scenario is as follow

declare @param int
select
*
from
table1
where
column1 = 'asdf'
column2= @param

In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.


can i use case in where clause so that if @param is '' then i will not include in the where clause

View Replies !

---

Case Statement In Where Clause?
Can I do the following? Keep getting an error stopping at the first < of the where clause.


declare @mon as int, @yr as int, @myDate as varChar(20)
set @yr=2006
set @mon=1
set @mydate='01/31/2006 23:59:59'

select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v,
(select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1
where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and
(case when @mon=1 then dateDiff(month,dateService,@myDate)< 216
when @mon=2 then dateDiff(month,dateService,@myDate)<244
when @mon=3 then dateDiff(month,dateService,@myDate)<275
when @mon=4 then dateDiff(month,dateService,@myDate)<305
when @mon=5 then dateDiff(month,dateService,@myDate)<336
when @mon=6 then dateDiff(month,dateService,@myDate)<366
when @mon=7 then dateDiff(month,dateService,@myDate)<32
when @mon=8 then dateDiff(month,dateService,@myDate)<63
when @mon=9 then dateDiff(month,dateService,@myDate)<93
when @mon=10 then dateDiff(month,dateService,@myDate)<124
when @mon=11 then dateDiff(month,dateService,@myDate)<154
when @mon=12 then dateDiff(month,dateService,@myDate)<185
end) group by v.chartID, rs1.procedureKey

View Replies !

---

Using CASE Statement In A WHERE Clause
Is it possible to use CASE within a WHERE?

I have a query which is something like this, but it returns an error:

 

SELECT * FROM tablex

WHERE

CASE WHEN 'sexec' IS NOT NULL THEN

dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE

dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END

GROUP BY dm_marque

 

In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else.  I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.

Many thanks

Karen

 

View Replies !

---

CASE Statement On A WHERE Clause?
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:

Line 15: Incorrect syntax near '='.

this is my query that lies on a PROC:


SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end

View Replies !

---

CASE Statement On A WHERE Clause?
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated:


WHERE
--r.pBOM_ID=d.fEngr_BOM_ID and
r.fItem_ID=a.pEngr_Item_ID and
r.level<=@v_level+1 and
case when @v_showrootlevel=1 then r.level>1 else r.level>1 end
order by r.pID

View Replies !

---

Returning An IN Clause From A CASE Statement
Hi all,

I am passing in a variable into a CASE statement. Based on the value, I want to return a set of values in an IN clause. Here is an example:

(The where clause will use a field called 'Location')


DECLARE @strTest as Varchar(50)
SET @strTest = 'HI'

SELECT * FROM [SomeTable]
WHERE
CASE @strTest
WHEN 'HI' THEN Location IN('1', '2', '3')
END
ORDER BY Location


Is this possible to do?

Sanctos

View Replies !

---

'Case' Statement Inside 'Where' Clause
Hi

I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following?

DECLARE @searchCriteria Int
SET @searchCriteria = 2

SELECT column1, column2 FROM TABLE WHERE
CASE @searchCriteria
WHEN 1 THEN (column3 = 1000100)
WHEN 2 THEN (column3 = 1000101)
END CASE



...cheers

View Replies !

---

Conditional Where Clause W/ Case Statement Possible?
Greetings,
 
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
 
I need to execute a query that changes the returned data based upon a parameter's value.  In my example below, the lob field contains both text values and nulls.
 

SELECT uniqueID, lob, xdate
FROM mytable
WHERE

CASE WHEN @myparam = 'ALL'

THEN

xdate >= '2007-09-01'
ELSE

xdate >= '2007-09-01' or
lob = @myparm
END
 
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
 
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field).  The permutations are a little too much.
 
Any ideas?
 
Rob
 

View Replies !

---

Case Statement On Where Clause If Parameter =NULL
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:


SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END

View Replies !

---

Group By Case Statement
Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated


SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]


Here is the current result displayed:


Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL



Instead I want the result to show something like this:


Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400

View Replies !

---

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning.  I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me.  Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate 
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table.  Any help would be greatly appreciated.

Thanks!

 

View Replies !

---

Transact-SQL Help - CASE Statement And Group By
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View Replies !

---

SQL Statement: Group By And Where Clause Do Not Display Null Rows
 
The following is a simplified version of my SQL statement.  I am attempting to do a simple count(*) with two groupings and a where clause.  This is Select command for a GridView.  However, I am unable to display zeros.  The rows are completely missing and I would greatly appreciate someone's help.  I have already tried Group By All, but that, unfortunately, does not work.  Here is my SQL statement:
 
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"

 

Thanks for the help in advance!

View Replies !

---

Error In Image Field When Using CASE Statement
I've this Stored procedure on a SQLserver 2000 SP3:


SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
---------------------------
OK
---------------------------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??

View Replies !

---

Include ID Field In GROUP BY Statement
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
 The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName      CostApple             0.63Pear              0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID      FruitName      Cost2       Apple             0.634       Pear              0.89
 Thanks.

View Replies !

---

Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
 
Say I have a table called myTable with columns col1,col2,col3,
 
Here's what I'm trying to do
 
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then


case when @COLTOSORTBY='col1' then col1 asc
      when @COLTOSORTBY='col2' then col2 asc
      else col3 asc
end
else

case when @COLTOSORTBY='col1' then col1 desc
      when @COLTOSORTBY='col2' then col2 desc
      else col3 desc
end
end
from myTable
)

 
 
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
 
Thanks.

View Replies !

---

Referencing An Alias In A Where Clause
 I have the following data set - there is more to it than whats below, I just made it easier to read and highlight my problem!

 

SELECT     LEFT(actv.ProjID, 4) AS proj, actv.Activity, actv.TotalExpensesLB,  actv.PADM 
  FROM         dbo.xtbl_MERActv actv
  WHERE     LEFT(actv.projID,4) = @project OR  actv.PADM = @PADM

 

What I want to do is have the user enter a 4 digit number (@project) which will correspond to LEFT(actv.ProjID, 4). The way it is now, if the user enters a 4 digit number, no records are returned. If the user enters a 6 digit number ( the real length of the projID), then it runs correctly and I get the records I want.

 

I have tried to use the alias 'proj' in the where statement, but I get an error message that it is an invalid column name.

 

Where am I going wrong?

 

Thanks in advance!

View Replies !

---

CASE Alias In WHERE
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 Replies !

---

Group By Alias Causing Problem...
Hi,

I am getting odd result while executing the below query.

SELECT COUNT(DISTINCT(D.image_id)),
(CASE WHEN D.stage_id IN (SELECT SS_STAGE.stage_id FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID )
THEN (SELECT SS_STAGE.STAGE_ID FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID)
ELSE D.stage_id
END) stage_id
FROM deadline D, OCCURRENCE O
WHERE O.image_id = D.image_id
AND (D.APPROVED_STAGE IS NULL OR D.CONFLICT = 1)
AND D.LOGON = 'pbitest2'
AND O.delete_ind = ' '
GROUP BY stage_id


My actual requirement is to group by using the alias name.
This query getting the results by grouping the STAGE_ID from DEADLINE table!!!.

Please help me on this...Thanks in Advance.


Sudheer

View Replies !

---

Adding A Group By Clause And Getting A Count Of A Group
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View Replies !

---

Joining On And Grouping By CASE Function Column Alias (URGENT)
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 Replies !

---

GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View Replies !

---

Cannot Use Alias For A Field Name In Views!!!!!!!!
HI Friends,
Can anybody help in this prob with SQL server Enterprise Manager

Prob:

I cannot use alias name spelled same as the table field-name, in a view. For eg:
SELECT Name AS name
FROM dbo.test
if I take this view in the design view in Enterprise Manager, it will changed to:

SELECT Name
FROM dbo.test
Can anybody help me How can I avoid this???? I need the same alias name with different case....

Nish

View Replies !

---

Can You Reference A Dataset Field As An Alias In MDX.
If I have this MDX query from Adventure Works how do I  access dim1 as a value in the dataset value field. Or is there a way to use an alias in dataset field for MDX.

 

="SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON EMPTY { ([" & Parameters!dim_1_table.Value & "].[Country].[Country].ALLMEMBERS ) as dim1 } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Reseller].[Reseller].[All Resellers] } ) ON COLUMNS FROM [Adventure Works]) WHERE ( [Reseller].[Reseller].[All Resellers] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

 

View Replies !

---

Trying To Display Alias Field Names
Hello All,

I have the following code:


USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'


I am trying to get the description which is flong and I get the following error message:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.

What am I doing wrong?

TIA

Kurt

View Replies !

---

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

---

Using Alias In Update Statement
Hi,

I am using MsSql2005 and wondering how to create aliases inside update statements. I need this, for a table variable inside a procedure.

UPDATE  @TempTable
    SET Field1 = (
    SELECT
        RealTable.Field2   
    FROM
        RealTable
    WHERE
        RealTable.Field1 = @TempTable.Field1)
This statement doesn't work because I need to use an alias for @TempTable.

How?

Thanx a lot

View Replies !

---

Changing Alias Name Based On Record's Field Value
I have the following code, which works fine. I would like to change the alias name AMOUNT to something else, depending on the value of the transaction type. So if transaction_type = I then alias = AMOUNT_I; if transaction_type = R then alias = AMOUNT_R.

Is this possible? Is there a better way to structure this query?

I also tried using 2 sub selects - one for each transaction type - but there are about 8 other WHERE criteria on the T table (not shown) that had to be repeated, and it was just messy.


Code:


SELECT T.ACCT_CODE, T.TRANSACTION_TYPE,
T.JUL_CURR_CREDITS + T.JUL_CURR_DEBITS +
T.AUG_CURR_CREDITS + T.AUG_CURR_DEBITS +
T.SEP_CURR_CREDITS + T.SEP_CURR_DEBITS +
T.OCT_CURR_CREDITS + T.OCT_CURR_DEBITS +
T.NOV_CURR_CREDITS + T.NOV_CURR_DEBITS +
T.DEC_CURR_CREDITS + T.DEC_CURR_DEBITS +
T.JAN_CURR_CREDITS + T.JAN_CURR_DEBITS +
T.FEB_CURR_CREDITS + T.FEB_CURR_DEBITS +
T.MAR_CURR_CREDITS + T.MAR_CURR_DEBITS +
T.APR_CURR_CREDITS + T.APR_CURR_DEBITS +
T.MAY_CURR_CREDITS + T.MAY_CURR_DEBITS +
T.JUN_CURR_CREDITS + T.JUN_CURR_DEBITS
AS AMOUNT
FROM DBO.TOTALS T, DBO.ACCOUNTS A
WHERE (T.TRANSACTION_TYPE='I' OR T.TRANSACTION_TYPE='R')

View Replies !

---

How To Reference A Previous Field Alias In TSQL As In Jet SQL?
My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT        1 AS A, 2 AS B, A+B AS C
FROM            SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A  B  C
1  2  3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

View Replies !

---

Problem Using Result From CASE In Another CASE Statement
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:
 



Code Block...beginning of SQL view...
  shipment_status =
  CASE
    [logic for condition 1]
    THEN 1
    WHEN     [logic for condition 2]
    THEN 2   
    WHEN     [logic for condition 3]
    THEN 3
    WHEN     [logic for condition 4]
    THEN 4
    ELSE 0
  END,
  shipment_status_text =
  CASE
    [logic for condition 1]
    THEN 'Condition 1 text'
    WHEN     [logic for condition 2]
    THEN  'Condition 2 text'
    WHEN     [logic for condition 3]
    THEN  'Condition 3 text'
    WHEN     [logic for condition 4]
    THEN  'Condition 4 text'
    ELSE 'Error'
  END,
...remainder of SQL view...
 



 

This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
 
This is what I'd like to do:
 


Code Block
...beginning of SQL view...
  shipment_status =
  CASE
    [logic for condition 1]
    THEN 1
    WHEN     [logic for condition 2]
    THEN 2   
    WHEN     [logic for condition 3]
    THEN 3
    WHEN     [logic for condition 4]
    THEN 4
    ELSE 0
  END,
 

  shipment_status_text =

    CASE shipment_status

    WHEN 1 THEN 'Condition 1 text'

    WHEN 2 THEN 'Condition 2 text'

    WHEN 3 THEN 'Condition 3 text'

    WHEN 4 THEN 'Condition 4 text'

    ELSE 'Error'

  END,
...remainder of SQL view...
 

This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
 
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View Replies !

---

Using One Alias For Mutiple Columns In A SELECT Statement
Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT    RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],         LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],        LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],        LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],        LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],         LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],        RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst

View Replies !

---

Using One Alias For Mutiple Columns In A SELECT Statement
Hi all,

Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?


Code:


SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],
LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],
LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],
LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],
LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],
LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],
RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]



Thanks

Tryst

View Replies !

---

Using Case In Having Clause
I am stumped trying to use case/if type conditions in having clause. Not sure if it's possible or my syntax or both.

Trying to do something like this:

CASE WHEN (dbo.t_COT_Summary.TCD >= dbo.ReportDate(CONVERT(nvarchar(30), GETDATE(), 101))) THEN

HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NOT NULL

ELSE

HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NULL

END

dbo.t_COT_Summary.TCD = Target Completion Date
dbo.ReportDate = Previous Business Day Function
dbo.t_COT_AP_Exclude.Primary_ID = A left joined ID value I wish to exclude or include in the main query's having.

I hope this makes sense... any suggestions on a better way to do this would be greatly appreciated!

View Replies !

---

Case In A Where Clause
A deveoper just asked me if there is a way to use a case in a where clause. Is this feasible or will we have to do some dynamic sql

where (cr.cb_routine = 1 or cr.cb_urgent = 1 or cr.cb_emergency_room = 1
or cr.cb_on_site_clinic = 1 or cr.cb_retro_request = 1 or cr.cb_initial = 1
or cr.cb_followup = 1 or cr.cb_in_person = 1 or cr.cb_telemed = 1
or cr.df_within is not null or cr.df_provider is not null
or cr.df_proc_test_spec is not null or cr.df_provider_area is not null)
and p.privacy_level = 10
and pe.Location_ID = @Location and
case when @status = 'Pended' then cr.cb_supp_info_need1 = 1
case when @status = 'Criteria for service not met' then cr.cb_criteria_not_1 = 1
case when @status = 'Other' then cr.cb_other_1 = 1
case when @status = 'All' then

View Replies !

---

Using CASE In The WHERE Clause
This is a CASE statement that I am using in the WHERE clause of my query.
SQL Syntax checker keeps returning an error (Incorrect syntax near '=').
Can anyone help me figure out what I need to do to get this to work?

Case When (@Weekday=-1 and @Saturday=0 and @Sunday=0) Then (L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1)
When (@Weekday=0 and @Saturday=-1 and @Sunday=0) Then (L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1)
When (@Weekday=0 and @Saturday=0 and @Sunday=-1) Then (L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1)
When (@Weekday=-1 and @Saturday=-1 and @Sunday=-0) Then (L1.Sunday=0 AND L2.Sunday=0 AND L3.Sunday=0 AND L4.Sunday=0)
When (@Weekday=-1 and @Saturday=0 and @Sunday=-1) Then (L1.Saturday=0 AND L2.Saturday=0 AND L3.Saturday=0 AND L4.Saturday=0)
When (@Weekday=0 and @Saturday=-1 and @Sunday=-1) Then (L1.[Weekday]=0 AND L2.[Weekday]=0 AND L3.[Weekday]=0 AND L4.[Weekday]=0)
Else
((L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1) OR
(L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1) OR
(L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1))
End

View Replies !

---

CASE WHERE Clause Help
Code:


WHERE
weekdayname(weekday(sfa_admin_sbaccount.add_time)) =
case
when "Monday"
then (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-3 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date()))
else (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-1 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date())));
end



This keeps telling me I'm missing an operator. What I want the query to do is to evaluate the current day's date, then use that to determine whether it needs to set a WHERE clause that goes back 3 days (if it's a Monday) or one day (if it's not a Monday)

This is in Access. Any suggestions?

View Replies !

---

Using CASE In The WHERE Clause
I am attempting to write a stored procedure that will accept a column name in the form of an nvarchar parameter along with a corresponding value to use to filter the returned results.  Obviously I can write a CASE statement with the query repeated for each case with the applicable WHERE clause, but after some research I did find that according to some sites, using the CASE statement in the WHERE clause is perfectly legal.  However, I have tried the following code, which is basically a cut & paste version of what I found described, but SQL Server keeps generating errors at the first WHEN clause.  I would appreciate anyone's guidance getting this right.

SELECT *
FROM   tblTest
WHERE  CASE @FilterKey
         WHEN 'Description' THEN [Description]=@FilterValue
         WHEN 'UpdateTime' THEN [UpdateTime]=@FilterValue
END

In this case, the table [tblTest] has two columns: [Description] and [UpdateTime] and the parameters @FilterKey and @FilterValue would be defined in the sproc definition - or inline with DECLARE/SET statements in Query Analyzer.

Keep in mind that the goal is to NOT have a separate parameter for each property so the (@p is null OR p=@p) method is not appropriate.

Oh, I have also tried to bring the CASE condition inline with the WHEN clause, for instance, WHEN @FilterKey='Description' THEN..., with no change in the results.

Thanks in advance for the feedback.

View Replies !

---

Case When In Where Clause
is this correct?(where clause only)

where budget = case when price > 2.0 then 'True' else 'False' end

Funnyfrog

View Replies !

---

Case In Where Clause
hello,

Can anybody see why this is failing at line 19
incorrect syntax near =
?


declare @Date datetime
declare @type int
declare @isnew int
declare @isreturn int
declare @isold int
SET @Date = '2008-03-04'
SET @type = 1
SET @isnew=1

SELECT [date],
SUM(amount) as s_amount
FROM values
WHERE convert(char(10),[date],23) = @Date
AND status > 0 AND

CASE WHEN @isnew=1 THEN
(loan.isnew=1)
WHEN @isreturn=1 THEN
((loan.isreturn=1) and loan.isold=0)
WHEN @isold=1 THEN
loan.isold=1
END
AND type = @type
GROUP BY [date]

kind regards,
jamie

View Replies !

---