|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can I use the 'IF' clause in a 'WHERE' clause ?I am developing a website using MS Visual Studio 2005 and SQL 2005 Express. I'm using SQL queries (not stored procedures yet). Problem: I have a query that I can't make work. I found the COALESCE function and it solves part of the problem but not all. I have a variable '@filter_by_date' which is bound to a dropdownlist on the webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent the number of days to subtract from today's date. If a user selects the NULL option the query should return all rows which is how my query is: SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, created_date)) The problem arises when the user selects any of the other options. If the user selects -7, for example, then this value should be subtracted from the current date and used in the query to only show jobs created after that date. The query looks like this: SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date) Is it possible to merge the two queries? I thought of trying to use an 'IF' in the 'WHERE' clause but I can't get it to work. Thank you for your time. Hope you can help. You'll probably get better performance with two separate queries, each in
its own stored proc. However, you can try: SELECT * FROM t_jobs WHERE @filter_by_date IS NULL OR (@filter_by_date IS NOT NULL AND created_date >= GETDATE() -@filter_by_date)) (assumes @filter_by_date is a positive number.) -- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@microsoft.com... > Background: > I am developing a website using MS Visual Studio 2005 and SQL 2005 > Express. > I'm using SQL queries (not stored procedures yet). > > Problem: > I have a query that I can't make work. I found the COALESCE function and > it > solves part of the problem but not all. > > I have a variable '@filter_by_date' which is bound to a dropdownlist on > the > webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent > the > number of days to subtract from today's date. > > If a user selects the NULL option the query should return all rows which > is > how my query is: > > SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, > created_date)) > > The problem arises when the user selects any of the other options. If the > user selects -7, for example, then this value should be subtracted from > the > current date and used in the query to only show jobs created after that > date. > The query looks like this: > > SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date) > > Is it possible to merge the two queries? I thought of trying to use an > 'IF' > in the 'WHERE' clause but I can't get it to work. > > Thank you for your time. Hope you can help. > Thanks for the help. I implemented your querry into mine but when I go out of
and back into the Query Builder the query has changed. My other comparisons in the WHERE clause have been duplicated for each side of the OR operator. Is this normal? Show quote "Tom Moreau" wrote: > You'll probably get better performance with two separate queries, each in > its own stored proc. However, you can try: > > SELECT > * > FROM > t_jobs > WHERE > @filter_by_date IS NULL > OR (@filter_by_date IS NOT NULL > AND created_date >= GETDATE() -@filter_by_date)) > > (assumes @filter_by_date is a positive number.) > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > > "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message > news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@microsoft.com... > > Background: > > I am developing a website using MS Visual Studio 2005 and SQL 2005 > > Express. > > I'm using SQL queries (not stored procedures yet). > > > > Problem: > > I have a query that I can't make work. I found the COALESCE function and > > it > > solves part of the problem but not all. > > > > I have a variable '@filter_by_date' which is bound to a dropdownlist on > > the > > webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent > > the > > number of days to subtract from today's date. > > > > If a user selects the NULL option the query should return all rows which > > is > > how my query is: > > > > SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, > > created_date)) > > > > The problem arises when the user selects any of the other options. If the > > user selects -7, for example, then this value should be subtracted from > > the > > current date and used in the query to only show jobs created after that > > date. > > The query looks like this: > > > > SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date) > > > > Is it possible to merge the two queries? I thought of trying to use an > > 'IF' > > in the 'WHERE' clause but I can't get it to work. > > > > Thank you for your time. Hope you can help. > > > > > Don't use the query builder. Use Query Analyzer (QA).
-- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada t**@cips.ca www.pinpub.com "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message news:A944E1F3-C855-441C-8F90-F4ED600E33F1@microsoft.com... > Thanks for the help. I implemented your querry into mine but when I go out > of > and back into the Query Builder the query has changed. My other > comparisons > in the WHERE clause have been duplicated for each side of the OR operator. > Is > this normal? > > "Tom Moreau" wrote: > >> You'll probably get better performance with two separate queries, each in >> its own stored proc. However, you can try: >> >> SELECT >> * >> FROM >> t_jobs >> WHERE >> @filter_by_date IS NULL >> OR (@filter_by_date IS NOT NULL >> AND created_date >= GETDATE() -@filter_by_date)) >> >> (assumes @filter_by_date is a positive number.) >> >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> >> "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message >> news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@microsoft.com... >> > Background: >> > I am developing a website using MS Visual Studio 2005 and SQL 2005 >> > Express. >> > I'm using SQL queries (not stored procedures yet). >> > >> > Problem: >> > I have a query that I can't make work. I found the COALESCE function >> > and >> > it >> > solves part of the problem but not all. >> > >> > I have a variable '@filter_by_date' which is bound to a dropdownlist on >> > the >> > webpage. The dropdownlist's values are NULL, -7, -14, -30 which >> > represent >> > the >> > number of days to subtract from today's date. >> > >> > If a user selects the NULL option the query should return all rows >> > which >> > is >> > how my query is: >> > >> > SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, >> > created_date)) >> > >> > The problem arises when the user selects any of the other options. If >> > the >> > user selects -7, for example, then this value should be subtracted from >> > the >> > current date and used in the query to only show jobs created after that >> > date. >> > The query looks like this: >> > >> > SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date) >> > >> > Is it possible to merge the two queries? I thought of trying to use an >> > 'IF' >> > in the 'WHERE' clause but I can't get it to work. >> > >> > Thank you for your time. Hope you can help. >> > >> >> >> Thank you for your prompt replies, much appreciated.
Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now) before I dable into yet another app. Mat Show quote "Tom Moreau" wrote: > Don't use the query builder. Use Query Analyzer (QA). > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada t**@cips.ca > www.pinpub.com > > "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message > news:A944E1F3-C855-441C-8F90-F4ED600E33F1@microsoft.com... > > Thanks for the help. I implemented your querry into mine but when I go out > > of > > and back into the Query Builder the query has changed. My other > > comparisons > > in the WHERE clause have been duplicated for each side of the OR operator. > > Is > > this normal? > > > > "Tom Moreau" wrote: > > > >> You'll probably get better performance with two separate queries, each in > >> its own stored proc. However, you can try: > >> > >> SELECT > >> * > >> FROM > >> t_jobs > >> WHERE > >> @filter_by_date IS NULL > >> OR (@filter_by_date IS NOT NULL > >> AND created_date >= GETDATE() -@filter_by_date)) > >> > >> (assumes @filter_by_date is a positive number.) > >> > >> > >> -- > >> Tom > >> > >> ---------------------------------------------------- > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > >> SQL Server MVP > >> Columnist, SQL Server Professional > >> Toronto, ON Canada > >> www.pinpub.com > >> > >> "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message > >> news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@microsoft.com... > >> > Background: > >> > I am developing a website using MS Visual Studio 2005 and SQL 2005 > >> > Express. > >> > I'm using SQL queries (not stored procedures yet). > >> > > >> > Problem: > >> > I have a query that I can't make work. I found the COALESCE function > >> > and > >> > it > >> > solves part of the problem but not all. > >> > > >> > I have a variable '@filter_by_date' which is bound to a dropdownlist on > >> > the > >> > webpage. The dropdownlist's values are NULL, -7, -14, -30 which > >> > represent > >> > the > >> > number of days to subtract from today's date. > >> > > >> > If a user selects the NULL option the query should return all rows > >> > which > >> > is > >> > how my query is: > >> > > >> > SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, > >> > created_date)) > >> > > >> > The problem arises when the user selects any of the other options. If > >> > the > >> > user selects -7, for example, then this value should be subtracted from > >> > the > >> > current date and used in the query to only show jobs created after that > >> > date. > >> > The query looks like this: > >> > > >> > SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date) > >> > > >> > Is it possible to merge the two queries? I thought of trying to use an > >> > 'IF' > >> > in the 'WHERE' clause but I can't get it to work. > >> > > >> > Thank you for your time. Hope you can help. > >> > > >> > >> > >> > > > If you have SQL Server 2000, that's part of the tools that ship with it. If
you have SQL Server 2005, then you use SQL Server Management Studio (SSMS), that ships with SQL Server 2005. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message Thank you for your prompt replies, much appreciated.news:476B52A3-1188-46DE-AF3F-B905475B1A1D@microsoft.com... Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now) before I dable into yet another app. Mat Show quote "Tom Moreau" wrote: > Don't use the query builder. Use Query Analyzer (QA). > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada t**@cips.ca > www.pinpub.com > > "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message > news:A944E1F3-C855-441C-8F90-F4ED600E33F1@microsoft.com... > > Thanks for the help. I implemented your querry into mine but when I go > > out > > of > > and back into the Query Builder the query has changed. My other > > comparisons > > in the WHERE clause have been duplicated for each side of the OR > > operator. > > Is > > this normal? > > > > "Tom Moreau" wrote: > > > >> You'll probably get better performance with two separate queries, each > >> in > >> its own stored proc. However, you can try: > >> > >> SELECT > >> * > >> FROM > >> t_jobs > >> WHERE > >> @filter_by_date IS NULL > >> OR (@filter_by_date IS NOT NULL > >> AND created_date >= GETDATE() -@filter_by_date)) > >> > >> (assumes @filter_by_date is a positive number.) > >> > >> > >> -- > >> Tom > >> > >> ---------------------------------------------------- > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > >> SQL Server MVP > >> Columnist, SQL Server Professional > >> Toronto, ON Canada > >> www.pinpub.com > >> > >> "Matthew Hill" <MatthewH***@discussions.microsoft.com> wrote in message > >> news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@microsoft.com... > >> > Background: > >> > I am developing a website using MS Visual Studio 2005 and SQL 2005 > >> > Express. > >> > I'm using SQL queries (not stored procedures yet). > >> > > >> > Problem: > >> > I have a query that I can't make work. I found the COALESCE function > >> > and > >> > it > >> > solves part of the problem but not all. > >> > > >> > I have a variable '@filter_by_date' which is bound to a dropdownlist > >> > on > >> > the > >> > webpage. The dropdownlist's values are NULL, -7, -14, -30 which > >> > represent > >> > the > >> > number of days to subtract from today's date. > >> > > >> > If a user selects the NULL option the query should return all rows > >> > which > >> > is > >> > how my query is: > >> > > >> > SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, > >> > created_date)) > >> > > >> > The problem arises when the user selects any of the other options. If > >> > the > >> > user selects -7, for example, then this value should be subtracted > >> > from > >> > the > >> > current date and used in the query to only show jobs created after > >> > that > >> > date. > >> > The query looks like this: > >> > > >> > SELECT * FROM t_jobs WHERE (created_date >= > >> > GETDATE() -@filter_by_date) > >> > > >> > Is it possible to merge the two queries? I thought of trying to use > >> > an > >> > 'IF' > >> > in the 'WHERE' clause but I can't get it to work. > >> > > >> > Thank you for your time. Hope you can help. > >> > > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics