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.
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
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.
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
I have a store procedure where i need to use conditionel where clause
View 2 Replies View RelatedI'm trying to use a conditional statement in the where clause.
Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000
What I'm trying to achieve:
If Amount = Amount2 for UID 2 then show UID 1
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 6 Replies View RelatedGreetings,
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
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
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.
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 10 Replies View Related
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
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 3 Replies View RelatedI am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
Hi,
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.
How Can I use Top Clause with GROUP BY clause?
Here is my simple problem.
I have two tables
Categories
Products
I want to know Top 5 Products in CategoryID 1,2,3,4,5
Resultset should contain 25 Rows ( 5 top products from each category )
I hope someone will help me soon.
Its urngent
thanks in advance
regards
Waqas
hi..
i have basic question like
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Thanks
SQL 7
Hi All !!
I want to know how I can create conditional FROM WHERE clauses like below ..
SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
Thoughts ?
Thanks
Is anything similar to this possible without writing whole query in every begin block or without dynamic sql:
proc(@arg1 bit)
select * from table where x=y
if @arg1 = 1 begin
and when a=b
end
order by date
What does "conditional" mean as a command? sp_who2 reports this forsome sessions, along with "insert" etc.Thanks,Jim Geissman
View 2 Replies View RelatedHi,
Is it possible to have a conditional sum based on an item type existance in a set of values?
Example if i have the following set:
A
A
A
A
B
I just wanna sum B else if B doens't exist sum A
Best Regards,
Luis Simoes
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 5 Replies View RelatedIm faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
I encounter a T-Sql problem related to if conditional processing:The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.Could anyone offer some advice? IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0) BeginINSERT INTO dbo.DefaultsSELECT FinancialPlannerID,ReportTitleFROM dbo.ReportPreferencesendGO
View 6 Replies View RelatedI have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View 6 Replies View RelatedHi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
(
@startRef int,
@endRef int
)
AS
BEGIN
SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
END
RETURN
I have an SqlDataSource that uses a value from the query string in the WHERE clause of the sql statement.
The sql is something like this:
SELECT * FROM myTable WHERE myfield = @myfield
and I have the QueryStringParameter setup like this:
<asp:QueryStringParameter Name="myfield" QueryStringField="myfield" />
What I need is for the sql statement to return all records in the case that "myfield" is not defined in the query string.
How would I implement this?
Thanks,
Joshua Foulk