Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
Aug 10, 2006
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 6 Replies
ADVERTISEMENT
Jan 11, 2007
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!
View 4 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Jan 22, 2007
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 1 Replies
View Related
Oct 3, 2007
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 5 Replies
View Related
Feb 21, 2008
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 3 Replies
View Related
Aug 13, 2003
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 10 Replies
View Related
Apr 7, 2006
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 3 Replies
View Related
Aug 10, 2004
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 3 Replies
View Related
Mar 15, 2007
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 3 Replies
View Related
Apr 17, 2008
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 3 Replies
View Related
May 8, 2008
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 6 Replies
View Related
Oct 13, 2006
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 9 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
May 16, 2006
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 3 Replies
View Related
Aug 8, 2006
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 1 Replies
View Related
Mar 13, 2008
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 14 Replies
View Related
Jun 5, 2015
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101)
select top 10 pt.pt_id, pt.last_name
fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id
and a.pt_id = pt.pt_id
and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
View 2 Replies
View Related
Sep 25, 2007
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 8 Replies
View Related
Jun 3, 2008
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 3 Replies
View Related
Jun 4, 2015
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)
WHEN c.TYPE = 'DEF' THEN (c.volume)
WHEN c.Type = 'GHI' THEN (c.volume)
Else 0
END),
CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
WHEN c.Type = 'ABC' THEN '123.1'
WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
from table1 a join table2 b
on a.Cust = b.Customer
join table3 c
on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
View 18 Replies
View Related
Mar 11, 2008
I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.
CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)
Any ideas how I construct this CASE statement to get the desired results.
Thanx,
View 13 Replies
View Related
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
Sep 19, 2014
I have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:
Company1 ''
Company1 'Individual1'
Company1 'Individual2'
Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:
CASE WHEN EXISTS
(Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' )
THEN ind_customer.cst_ind_full_name_dn
ELSE ''
END
AS 'FULL NAME'
View 7 Replies
View Related
Oct 5, 2007
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton
Dates Table
Date
4/1/2003
1/1/2006
4/2/2007
Fee Table
Date
Period
Class
Fee
1
Daily
True
329
1
Half Day
True
178
1
OT
True
49
1
Hourly
True
41
1
Daily
False
156
1
Half Day
False
86
1
OT
False
27
1
Hourly
False
19
2
Daily
True
355
2
Half Day
True
192
2
OT
True
50
2
Hourly
True
44
2
Daily
False
171
2
Half Day
False
92
2
OT
False
28
2
Hourly
False
21
3
Daily
True
364
3
Half Day
True
197
3
OT
True
51
3
Hourly
True
45
3
Daily
False
175
3
Half Day
False
94
3
OT
False
29
3
Hourly
False
21
View 3 Replies
View Related
Aug 21, 2014
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
View 5 Replies
View Related
Jul 14, 2014
Just wondering if it's possible to select distinct results from the where clause?
View 3 Replies
View Related
Oct 11, 2007
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN
dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
View 12 Replies
View Related
Jul 4, 2006
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 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Jun 2, 2014
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies
View Related
Jun 12, 2008
Hi Everyone,
I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:
IF @categoryID <> 12
AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)
View 4 Replies
View Related
Jan 17, 2008
Hi, i've got this stored procedure:
@LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN
IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END
IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END
CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END
WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END
WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement
View 4 Replies
View Related
