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

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

thanks but I can't get to those websites...

Our company websense filters that out as "personal"

|||

Sometimes you can get away with something like:

Select * From (tables)

WHERE

(Field1 = @.param1 OR @.param1 IS NULL) AND

(Field 2 = @.param2 OR @.param2 IS NULL) AND ...

|||

Then tell your IT department that they are actually work related and why and ask them to allow access to them.

Simple as that.

|||

Mainiac007,

You can't do conditional code in T-SQL (unlike PL/SQL). You can, however, do this:

Select*From(tables)

where

col1 =coalesce(@.param1, col1)

and col2 =coalesce(@.param2, col2)

and...

Ron

conditional update within value

I have a column of data in SQL Server 2000 that I need to replace
values within it with new values. I know how to use CASE statements to
do conditional updates but not how to do this. Here is an example, not
the real example as the values relevant to my company would mean little
to anyone.
If value contains "name", replace it with "fullname"
If value contains "address", replace it with "fulladdress"
and so on...
What I want to do in the field is the following:
Field value now: abc##name##123
Field after change: abc##fullname###123
Field value now: asdlfkjlsdkafjnameasldfjk123
Field after change: asdlfkjlsdkafjfullnameasldfjk123
Field value now: adlsfkjaddresslksdfj34
Field after change: adlsfkjfulladdresslksdfj34
And update all rows in the approriate column with the above logic.
Any ideas?
Thanks.
JRYou don't need a Case statement to do this, you can use
Update #t Set foo = Replace (Replace (foo, 'address', 'fulladdress'),
'name', 'fullname')
Where foo Like '%name%' Or foo Like '%address%'
You could also do it with a Case statement like
Update #t Set foo = Case
When foo Like '%name%' Then Replace (foo, 'name', 'fullname')
When foo Like '%address%' Then Replace (foo, 'address', 'fulladdress')
Else foo
End
Where foo Like '%name%' Or foo Like '%address%'
Please note, however, that depending on your data, those two statements may
do different things. If a row has both "name" and "address" in that column,
the first update statement will change both name and address, but the Case
statement version will update only name to fullname, but won't change
address in that row.
Tom
"JR" <jriker1@.yahoo.com> wrote in message
news:1142706489.831624.92670@.j33g2000cwa.googlegroups.com...
>I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>|||You might want to have a look at STUFF as well, although REPLACE may well do
the trick.
The thing about CASE expressions is that they are 'falling rock' ie for the
first WHEN condition it finds to be true, it will return the THEN bit and
exit the statement. So if your string has multiple bits that need to
replacing, you'll need to run the UPDATE multiple times.
Hope that helps.
Damien
"JR" wrote:

> I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>

Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!

Conditional SQL Statements?

Hi There

I am writing a shopping cart page and I need some help in 'doing the math' for an SQL statement.

I have a main cart table that adds all the products.prices together from the 'price' field in the database to give the total amount payable.

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * product_options.price AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I have however introduced a 'sale_price' that I would like the statement to select over the 'price' if the sale_price isn't 0.

Do I need to have another select statement in the mix to specifically select the sale_price if not zero, something like

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * (SELECT product_options.price AS sp WHERE po.sale_price <> 0) AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I am a bit confused. Can you point me in the right direction, maybe a link to a tutorial or something.

I appreciate any help.

CheersRewritten for viewing

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * po.price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"

Potential answer

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"|||thanks aschk.|||Does it work? What DBMS are you using? Can you provide a sample table layout? Do you have some sample data for a test? If you could provide this information I'm sure I could give you a better answer.|||Hi Aschk

Its not working. Thanks for the effort though.

I am using MySQL.

Tables

tblCart
cart_id,session_id,main_id,cart_quantity,timestamp

tblProduct_Options
product_options_id, product_options_text,price,sale_price,stock

tblMain
main_id,product_id,product_options_id

tblProducts
product_id,product_name,description

(some fields removed for clarity)

I am basically trying to do is mutliply cart_quantity with the sale_price if the sale_price is not zero, if it is zero then multiply by the price.

I have included the sql_dump with all the data.

Thanks for your time and effort|||SELECT SUM(TotPric) as TheTotal
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TotPric
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'
) ttt|||The original answer worked. Sorry aschk, I can't even copy and paste. time to hang up my coding hat and apply for that Burger King vacancy.

Thanks for your help and your time
:beer:
:)|||i was just looking through your table structure and had a question regarding a few columns you are using.

In your products table you are using a `text` column for your product name. I was wondering why? I suspect your names of products won't be larger than 255 variable characters so use a VARCHAR(255).

Also the same for your product_code and cart_finish columns in your cart and main tables. I can't see a reason why these need to be text columns either.

Maybe a rethink on those or explanation would be good ;) ?|||Thanks for pointing this out to me.

Definately a rethink.

I have recently moved from access to mysql and haven't really studied the benefits of choosing the right field type or best practises for mysql.

I am looking into it now.

Do you have any advice or know of any sites I can look at before I google it myself.

I am guessing that VARCHAR keeps the database size down & makes it quicker?|||In a word yes. I'm not 100% keyed up on the data types especially with regards to Text, however what I do is that it's a self altering column meaning that data could continually be placed in it and it could differ per row of the database. Unless you're dealing with text of an unknown size it's best to restrict fields to sensible values. e.g. usernames will probably be no longer than 30 characters and thus you column shouldn't need to be anything bigger than varchar(30).|||You know, using a subquery here is superfluous. This will return the same result with less obfuscation:
SELECT Sum((c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TheTotal
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'|||The IF thing is not SQL. A case expression has to be used:

SELECT SUM(c.cart_quantity * CASE po.sale_price > 0 THEN sale_price ELSE price END) AS theTotal
FROM ...|||The IF thing is not SQL.The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.|||First of all: thanks Blindman and Stolze for the time you took in posting to this thread.

My Db is MySQL and I am using classic ASP to write the 'other bits'

I am a newb to this so I am a little lost when it comes to 'The IF thing is not SQL'

What I am trying to do is write fast, correct, standard code and I appreciate your input.

As Blindman pointed out the original query supplied by Aschk works fine, what I would like to know is: Are there performance gains to be made by losing the subquery, is it more 'standard and correct' to use, as Stolze said, the CASE expression instead.

I can see myself coming across this situation again and again and it would be good for me to get it right inthe future.

Thanks again for your input guys. enlightening and educating.

:)|||There are certainly no performance GAINS to be had by using unnecessary subqueries. If you are a consultant being paid to code by the line, then that method may work out well for you, but otherwise it just makes it more difficult to debug.|||The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.

Here we discuss standardized SQL as defined in ISO/IEC 9075 (aka SQL:2003). That's why there is this sticky "Read This First" article: http://www.dbforums.com/announcement.php?f=11 So platform questions are usually irrelevant (except to state if a DBMS is conforming to SQL:2003 or not).

As for the question whether CASE expression or "the IF thing" is "more standard and correct", this is easy to answer: CASE expressions are defined in the SQL standard; so it is conforming to use that. "The IF thing" is not standard SQL and only a product-specific extension. If you want to write mostly portable SQL code (very hard btw), you shouldn't use it.|||Heh, if you're being paid line by line then space your work out, don't write subqueries ;)

Concurrent (completely independent) Statements.

Hi!

We are migrating an DB2 database to sql server 2005. We have used the 1.2 jdbc driver. It consumes lots of memory when we have several resultssets opened on different statments. Then we tried JTDS (http://jtds.sourceforge.net/), it worked fine. It's supports concurrent staments. We would like to stick with microsofts driver. My question is: will we se this supported in microsofts driver (soon)!

Joachim

Hi,

By any chance, are you setting the "responseBuffering" property to "adaptive"?

To keep back compatibility with our v1.1 driver behavior, our default behavior is to cache the entire resultset. So, in your scenario where you have multiple resultsets open at the same time, the driver will consume a lot of memory.

With responseBuffering=adaptive enabled, the driver will only consume as much memory as needed to retrieve the immediate data from the wire. Specifically, for CLOB and BLOB are streamed unless marked by the application.

I love to hear back if responseBuffering=adaptive resolves your scenario.

Jimmy

|||Hi!

Setting the property responseBuffering=adaptive did not help. When the first statment is opened, there is no increase in memory usage. As soon as we opens the next resultset the memory usage start to increase rapidly. When we use the jdts driver this does not happend. As i wrote before, we would like to use the microsoft driver (its the corporate strategy).
But we need to see the memory handling as in jdts.

Thanks for your rapid replay.

Joachim

Concurrent (completely independent) Statements.

Hi!

We are migrating an DB2 database to sql server 2005. We have used the 1.2 jdbc driver. It consumes lots of memory when we have several resultssets opened on different statments. Then we tried JTDS (http://jtds.sourceforge.net/), it worked fine. It's supports concurrent staments. We would like to stick with microsofts driver. My question is: will we se this supported in microsofts driver (soon)!

Joachim

Hi,

By any chance, are you setting the "responseBuffering" property to "adaptive"?

To keep back compatibility with our v1.1 driver behavior, our default behavior is to cache the entire resultset. So, in your scenario where you have multiple resultsets open at the same time, the driver will consume a lot of memory.

With responseBuffering=adaptive enabled, the driver will only consume as much memory as needed to retrieve the immediate data from the wire. Specifically, for CLOB and BLOB are streamed unless marked by the application.

I love to hear back if responseBuffering=adaptive resolves your scenario.

Jimmy

|||Hi!

Setting the property responseBuffering=adaptive did not help. When the first statment is opened, there is no increase in memory usage. As soon as we opens the next resultset the memory usage start to increase rapidly. When we use the jdts driver this does not happend. As i wrote before, we would like to use the microsoft driver (its the corporate strategy).
But we need to see the memory handling as in jdts.

Thanks for your rapid replay.

Joachim

Concatenating strings in a Select statment

I have this code:
declare @.var varchar(3000)
select @.var = @.var + column1 + ', '
from table1
select @.var
This statements give as a result all the values in column1 followed each by
a coma. My problem is that in a particular sever that doesn't work ok. If I
run the statements inside a proc it only returs the last value in the table,
but if I run it outside the proc (same query) it gives me the correct
results. Does anyone know why could that be?..
THanks>> Does anyone know why could that be?..
The SELECT statement you have is not a valid or supported in t-SQL. It is
simply a hack which seems to work in some cases, but breaks in a variety of
scenarios. Avoid such make-shift constructs due to its undocumented & risky
nature.
Anith

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
--
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
> > When executing the following statements:
> > declare @.x char(10), @.y char(10)
> > set @.x = 'abc'
> > set @.y = @.x + 'def'
> > select @.x
> > select @.y
> > the results are:
> > abc
> > abc
> > I expect @.y should be equal to 'abcdef'. If I change the var types to
> > int for example, then @.y is summed correctly. Can anyone tell me why,
> > or what I'm doing wrong? Thanks.
> > Dan
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.