Conditional Where Clause W/ Case Statement Possible?

Case Conditional In SQL Statement 2000
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.

View Replies !

---

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 !

---

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 !

---

Using A Field Alias For A CASE Statement In A GROUP BY Clause
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 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 !

---

Conditional WHERE Clause
Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company.  Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT    AdviserID,    AdviserName FROM    Advisers WHERE    (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    (CompanyID = @CompanyID) AND    (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    IF @CompanyID = 2 THEN      BEGIN         (CompanyID = @CompanyID)      END   ELSE      BEGIN         (CompanyID = @CompanyID) AND          (TypeID = 3)      END
I've also tried:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE @CompanyID       WHEN 2 THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND          (TypeID = 3)   END
and 
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND (TypeID = 3)   END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary

View Replies !

---

Conditional Where Clause
Hi all,
 
I have a table QT defined as
 
CREATE TABLE [dbo].[QT](
[Query] [nvarchar](50) NULL,
[Frequency] [int] NULL
) ON [PRIMARY]
 
Now based on a parameter I want to include a predicate in the select statement.
 
Basically I am trying to write something similar to the one below but possible only usinf one select statement.
 
if @queryString is null then

select query ,sum(frequency)

from qt

group by query
else
select query ,sum(frequency)

from qt

group by query
where query = @queryString.
 
Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.
 
Thanks
Aye.

View Replies !

---

Conditional Where Clause Possible?
Is it possible to use a conditional statements in a where clause?

 

IE:  I have 3 paramaters that may or may not be filled.

 

I would like to do something along the lines of...

 

 

Select * From (tables)

WHERE

    If @param1 has value

          Begin

                 'run this where statement

   if @Param2 has value

               'add this to the where clause

if @param3 has value

              'add this to the where cluase

 

 

View Replies !

---

Conditional If In Where Clause
 

Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement?  I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion.  Any advice is greatly appreciated.
 

View Replies !

---

How Can I Use A Conditional Where Clause In Sql Statment
I have a store procedure where i need to use conditionel where clause

View Replies !

---

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

---

Performance Issue Using Conditional WHERE Clause
Consider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared

View Replies !

---

Conditional Where Clause Depending On Input Parameter
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
 
My simple QueryA
 
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
 
My simple QueryB
 
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
 
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
 
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
 
I know it can be done using dynamic SQL construction. But any other ways ?
 
Also can someone also give in the solution in PL/SQL.
 
Thanks a bunch.
 
Jaffery.

View Replies !

---

Conditional Where Clause With Comma Delimited String And Link Table
 I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere   if @sports not null    user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!

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 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 !

---

Using Case In Where Clause With Nulls
For Example:SELECT Column1, Column2
FROM Table
WHERE
Column1 = (
SELECT CASE @Test4Nulls
WHEN 1 THEN NULL
ELSE Column1 END) Basically, I want to test for Nulls in a column if my variable @Test4Nulls is True, however, I never get any results back. I believe it is because I am Criteria = NULL instead of Criteria IS NULL, however, I cannot use IS in a case statement How do I test for nulls in a case statement 

View Replies !

---

CASE WHEN Equivalent In WHERE Clause?
Is there an equivalant construction to the CASE WHEN statement thatcan be used in the WHERE clause?For example, this works:SELECTFirstName = CASE WHEN c.FirstName = 'Bob' THEN'Robert'ELSEc.FirstNameEND,c.LastNameFROMContacts cWHERE(c.FirstName = 'Bob')OR(c.FirstName = 'Robert')But is there a way to build somehting resembling this (without usingExec-SQL)@FirstName nvarchar(35)SELECTc.FirstNamec.LastNameFROMContacts cWHERECASE WHEN @FirstName = 'Bob' THENc.BlueHair = 1ELSE CASE WHEN @FirstName = 'Frank' THENc.PastaEater = 1ELSEc.HatSize 5END END

View Replies !

---

Variables In A WHERE CASE Clause
I am using an INSERT statement which looks at a particular variable to determine data to place in a temp table, and am using the following format:

INSERT INTO #tblname
SELECT
column1, 2, etc.
FROM tblname2
WHERE parameter CASE @InputParm
WHEN 'xx' THEN 'xx'
WHEN 'yy' THEN 'yy'
ELSE (use both 'xx' AND 'yy')

In other words, I'd like it to look at the @InputParm, see if it's ONLY 'xx' or ONLY 'yy' and then use those appropriately, but if it's anything else, use BOTH 'xx' and 'yy'. I have to avoid using the EXEC(str) setup because this is a part of nested loops, and the table MUST exist beyond the loop execution.

I know this is possible (according to the MS site), but I can't get the syntax to work. Any assistance would be greatly appreciated.

With thanks,
Jack Cole
Magellan Healthcare
JKCole@magellanhealth.com

View Replies !

---

Dynamic Where Clause With If..else Or Case
Hello all...

I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select

i.e 

if...@where = @where + ' llll '

if...@where = @where + ' llll '

select @statement = @statement + @where

exec(@statement)

I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.

Is it possible to use either statement inside a where clause??

i.e

where if a = 1 then d=e

else contains(.....)

 

thanks

View Replies !

---

Where Clause To Use Case W/ Parameters
I have my where clause as follows, but it's not working:

WHERE     (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2)  = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)

I give the user two paramters.

The first parameter is to populate the first two characters of a string.  There is a "." then another two characters.  What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters.  If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.

View Replies !

---

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

---

Store Procedure (case In Where Clause)
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.
CREATE PROCEDURE dbo.GetUsers (@UserId uniqueidentifier,@Gender Int,@PageNumber Int,@UsersPerPage Int,@HowManyUsers INT OUTPUT)ASSET NOCOUNT ONDECLARE @Users TABLE(RowNumber Int,UserId uniqueidentifier,LastName Varchar(50),FirstName Varchar(50))
 
INSERT INTO @Users
SELECT ROW_NUMBER() OVER (ORDER BY P.UserId),  P.UserId, P.LastName, P.FirstNameFROM dbo.Profile PWHERE P.UserId <> @UserIdCASE WHEN @Gender =1 THEN 'AND P.GenderId = 1'     WHEN @Gender = 0 THEN 'AND P.GenderId = 0'END
 
SELECT @HowManyUsers = COUNT(UserId) FROM @UsersSELECT UserId, LastName, FirstNameFROM @UsersWHERE RowNumber > (@PageNumber - 1)*@UsersPerPageAND RowNumber <= @PageNumber * @UsersPerPage

View Replies !

---

Store Procedure (case In Where Clause)
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.
 
CREATE PROCEDURE dbo.GetUsers
(@UserId uniqueidentifier,
@Gender Int,
@PageNumber Int,
@UsersPerPage Int,
@HowManyUsers INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Users TABLE
(RowNumber Int,
UserId uniqueidentifier,
LastName Varchar(50),
FirstName Varchar(50))
 
INSERT INTO @Users
SELECT ROW_NUMBER() OVER (ORDER BY P.UserId),
  P.UserId, P.LastName, P.FirstName
FROM dbo.Profile P
WHERE P.UserId <> @UserId
CASE WHEN @Gender =1 THEN 'AND P.GenderId = 1'
     WHEN @Gender = 0 THEN 'AND P.GenderId = 0'
END
 
SELECT @HowManyUsers = COUNT(UserId) FROM @Users
SELECT UserId, LastName, FirstName
FROM @Users
WHERE RowNumber > (@PageNumber - 1)*@UsersPerPage
AND RowNumber <= @PageNumber * @UsersPerPage

View Replies !

---