Case statement 'Operator' in Where clause

i have a sp like this
--
@.operator int --(1=smaller,2=bigger)
@.price decimal(18,2)
select id,price
from company
where (price case @.operator when 1 then <@.price when 2 then >@.price end )
how can i accomplish this?
thnxdeclare @.operator int,
@.price decimal(18,2)
select ID, Price
from Company
where (price < @.price and @.operator = 1 )
and (price > @.price and @.operator = 2)
But if you have this in a Store procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price < @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price > @.price
end
Also with the logic that you have if the price is the same you will
never see it.
So you need to do this if was in procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price <= @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price >= @.price
end|||On Wed, 25 Jan 2006 13:38:01 -0800, henk wrote:

>i have a sp like this
>--
>@.operator int --(1=smaller,2=bigger)
>@.price decimal(18,2)
>select id,price
>from company
>where (price case @.operator when 1 then <@.price when 2 then >@.price end )
>--
>how can i accomplish this?
>thnx
Hi Henk,
IF @.operator = 1
SELECT id, price
FROM company
WHERE price < @.price
ELSE
SELECT id, price
FROM company
WHERE price > @.price
Hugo Kornelis, SQL Server MVP|||You can use either options as follows
declare @.operator int --(1=smaller,2=bigger)
declare @.price decimal(18,2)
set @.operator = 1 -- 2
set @.price = 10.05
-- Option A
declare @.cmd varchar(8000)
set @.cmd = 'select id, price from company where price '+ case @.operator when
1 then '< @.price' else '> @.price' end
exec (@.cmd)
-- Option B
if @.operator = 1
select id
,price
from company
where price < @.price
if @.operator = 2
select id
,price
from company
where price >@.price
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||thanks for your reply
i realy would like to do this in one statment without making use of if-else.
the reason behind this is that i have multiple operators for multiple
columns and it realy gets complex with 4 or 5 operators.
is it possible or do i have to use the if-else senario?
"Amiller" wrote:

> declare @.operator int,
> @.price decimal(18,2)
>
> select ID, Price
> from Company
> where (price < @.price and @.operator = 1 )
> and (price > @.price and @.operator = 2)
> But if you have this in a Store procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price < @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price > @.price
> end
>
> Also with the logic that you have if the price is the same you will
> never see it.
> So you need to do this if was in procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price <= @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price >= @.price
> end
>|||DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price - @.price > 0 THEN 2
WHEN price - @.price <= 0 THEN 1
END
= @.operator
The following was tested on Northwind
USE Northwind
GO
DECLARE @.operator int
DECLARE @.price money
SET @.operator = 1
SET @.price = 17.95
select productid, unitprice
FROM Products
WHERE
CASE
WHEN UnitPrice > @.price THEN 2
WHEN UnitPrice < @.price THEN 1
END = @.operator
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||I am not sure if the first statement will work i have never tried
something like that. I would go with the if else and comment all layers
so you know what one does what.|||thank you all for your helpfull replys.
i think i go with the Mark 's solution. that is exactly what i wanted.
thank you once again.
"Mark Williams" wrote:
> DECLARE @.operator int
> DECLARE @.price decimal (18,2)
> SET @.price = 17.99
> --To find items that are less than @.price, set @.operator to 1
> --To find items that are more than @.price, set @.operator to 2
> SELECT id, price
> FROM company
> WHERE
> CASE
> WHEN price - @.price > 0 THEN 2
> WHEN price - @.price <= 0 THEN 1
> END
> = @.operator
>
> The following was tested on Northwind
> USE Northwind
> GO
> DECLARE @.operator int
> DECLARE @.price money
> SET @.operator = 1
> SET @.price = 17.95
> select productid, unitprice
> FROM Products
> WHERE
> CASE
> WHEN UnitPrice > @.price THEN 2
> WHEN UnitPrice < @.price THEN 1
> END = @.operator
> --
>
> "henk" wrote:
>|||Just to clean things up
DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price > @.price THEN 2
WHEN price <= @.price THEN 1
END
= @.operator
Not sure why I did the whole price - @.price > 0 thing when the above is much
cleaner.
"henk" wrote:

> thank you all for your helpfull replys.
> i think i go with the Mark 's solution. that is exactly what i wanted.
> thank you once again.
>|||yeah i got it,
your the man man!!!!:)sql

case statement in where clause

I need a SQL statement that selects a specific year (@.yr type int) in the "createddate" column...if this @.yr is equal to 0 then I want to select ALL columns regardless of the year...

This is what I have so far, but it doesnt work...

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE
WHEN @.yr<>'0' THEN @.yr
ELSE NOT NULL
END

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE WHEN (@.yr= 0) THEN year(CreatedDate) ELSE @.yr END

Cascaded SELECT for multiple fields

Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
I can do a Cascaded Select on 1 field:
SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
or
SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
and I can rewrite it as a INNER JOIN, no problem. So far so good.
But now what happens when it is _BOTH_ pair of fields I wish to check
whether they exist/not exist in Table 2?
For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
Table2)
I have to write this is a INNER JOIN which will work. At least there is a
solution here.
But consider the 4th case. I am stuffed here regardless of doing Cascaded
Select or Join. You want to say
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FROM
Table2)
And I cannot rewrite this as an inner join because rows are only considered
when both fields, _jointly_ are absent from Table2.
There must be a solution here for multiple fields.
In real life I do have 2 tables. And I can gurantee that fld1 is present on
both tables. But I am interested in listing the pairs of fields (fld1, fld2)
present on one table but absent on the other.
Question1) Can it be done with Cascade Selects? How?
Question2) Can it be done with Joins? How?
Thanks
Stephen HoweThis should work:
SELECT Table1.fld1, Table1.fld2
FROM Table1 Table1
LEFT JOIN Table2 Table2
ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
WHERE Table2.fld1 IS NULL
If the pair exists in table2, then Table2.fld1 won't be NULL.|||> This should work:
> SELECT Table1.fld1, Table1.fld2
> FROM Table1 Table1
> LEFT JOIN Table2 Table2
> ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
> WHERE Table2.fld1 IS NULL
> If the pair exists in table2, then Table2.fld1 won't be NULL.
I was not clear in my labeling of cases. They are
Case 1: SELECTing on single fields present on Table 1 that should be
present on Table 2
Case 2: SELECTing on single fields present on Table 1 that should not be
present on Table 2
Case 3: SELECTing on multiple fields present on Table 1 that should be
present on Table 2
Case 4: SELECTing on multiple fields present on Table 1 that should not be
present on Table 2
As far as I know, JOINs can handle Cases 1-3 but not case 4
As far as I know, cascaded Selects can handle Cases 1-2 but not cases 3-4
Hrrrmmm after examination, you are right. Thanks
Stephen Howe|||Does this do what you want?:
select t1.fld1, t1.fld2
from Table1 t1
left outer join Table2 t2
on t1.fld1 = t2.fld1
and t1.fld2 = t2.fld2
where t2.fld1 is null;
HTH
Vern
"Stephen Howe" wrote:

> Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
> I have to write this is a INNER JOIN which will work. At least there is a
> solution here.
> But consider the 4th case. I am stuffed here regardless of doing Cascaded
> Select or Join. You want to say
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FR
OM
> Table2)
> And I cannot rewrite this as an inner join because rows are only considere
d
> when both fields, _jointly_ are absent from Table2.
> There must be a solution here for multiple fields.
> In real life I do have 2 tables. And I can gurantee that fld1 is present o
n
> both tables. But I am interested in listing the pairs of fields (fld1, fld
2)
> present on one table but absent on the other.
> Question1) Can it be done with Cascade Selects? How?
> Question2) Can it be done with Joins? How?
> Thanks
> Stephen Howe
>
>
>|||Stephen Howe (stephenPOINThoweATtns-globalPOINTcom) writes:
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
There is in ANSI SQL I believe, but ont implemented in SQL Server.
Anyway, all you need to learn is the EXISTS clause:
SELECT fld1, fld2
FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE a.fld1 = b.fld1
AND a.fld2 = b.fld2)
For the corresponding to NOT IN, use NOT EXISTS.
EXISTS/NOT EXISTS is in my opinion superior to IN/NOT IN. There is a
trap with nullable columns that you easily can fall into with NOT IN.
Does not happen with NOT EXISTS.
Performancewise, IN/NOT IN queries are usually rewritten internally
to EXISTS /NOT EXISTS, so there is no difference.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Performancewise, IN/NOT IN queries are usually rewritten internally
> to EXISTS /NOT EXISTS, so there is no difference.
Thanks Erland and everyone else.
My mind has been opened.
Stephen Howe

Capturing Data Type Mismatch

Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @.@.error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.It looks like a little data checking is needed somewhere. Here is one example:

Create Table dbo.tb_mismatch
(x int)


Create Procedure dbo.proc_mismatch @.var varchar(50), @.err int OUTPUT
as

if isnumeric(@.var) = 1
BEGIN
insert into dbo.tb_mismatch (x) values(@.Var)
END
ELSE
BEGIN
set @.err = -1
END


if @.err<>0
begin
print 'encountered error'
end
else
begin
print 'successfully exited'
end



declare @.var varchar(50), @.errreturn int
set @.var = 's'
set @.errreturn = 0

exec dbo.proc_mismatch @.var, @.errreturn OUTPUT --executing the proc
select @.errreturn

drop proc dbo.proc_mismatch
drop table dbo.tb_mismatch|||Thanks for your quick response but my requirement is:
I have several update and insert statements in my actual procedure which fetches the data from an oracle DB and updates the sql database.. during these updates and inserts Business wants me to capture all the system related errors and when i am trying to capture the data mismatch error(manually placing a varchar value in a float field) the cursor is directly moving to the end of procedure,instead of populating the log file.
I dont think placing isnumeric for all int and float fields is the feasible solution,
any other ways??

Many Thanks
Pavan.|||No

And this sounds like a batch process...

I woul unload the data from oracle, bcp the data in to sql server, perform my audits, then load the data in|||Sounds good but it doesn't help my requirement as i have lots of validations to be done before performing any transactions and even need to Rollback transactions in some cases..
Do we have any exception handling mechanism to handle this ..other than raiseerror as it didn't worked out.. or is this a bug in sqlserver?? like we have when VALUE_ERROR exception in oracle|||SQL Server error handling is kludgey in 2000. SQL 2005, takes for steps to address that, but I haven't looked in to it.

So why can't you do basic aduitng in batches in a set based manner? What's the difficulty. You will need some staging tables, bit so what?

You need to divorce yourself from sequential cursor processing that you're accostomed too in Oracle...even in Oracle, it is over used a lot of times.

Good Luck.

If you continue to do it this way, create a second stored procedure that gets called...like a nested stored procedure...when the nested proc fails, it will rais out to the calling stored procedure, and the driver can then handle the error...but that's the long way around the mountain|||Brett,
thanks for ur concern.
I have tried the second option but it hasn't helped me out.
My code goes something like this..
gets the jobnumber and its related info from the oracle job master table..checks for its existance in sql db and then creating 2 cursors for diff tables checks and then lots of if's and else's,calculations..and once it goes through all the validations we will start inserting the details into some tables,move data to history and then update the main job table..if it fails in any of the case just rollback the whole operations..now the turn of next job comes into picture..
As of now it works fine until we dont get varied data from oracledb which has the similar db structure of sql server.
My scope is till its developed.|||I still don't know why you can't do something like this

USE Northwind
GO

-- Set up the situation
SET NOCOUNT ON
CREATE TABLE ORACLE_TABLE(Col1 varchar(10))
CREATE TABLE SQL_TABLE(Col1 int)
GO

-- Create some sample Data

INSERT INTO ORACLE_TABLE(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'
GO

-- Report On Bad Data

SELECT * FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 0

-- Place the good data in to SQL
INSERT INTO SQL_TABLE(Col1)
SELECT (Col1) FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 1
GO

SELECT * FROM SQL_TABLE
GO

SET NOCOUNT OFF
DROP TABLE ORACLE_TABLE, SQL_TABLE
GO|||Try doing the same with a small change,changing the datatype from varchar to int,as this is my current structure,without using isnumeric option as my table has lots of columns and there are lots of insert and update statements.
CREATE TABLE ORACLE_TABLE(Col1 int)

Came to know that this error cannot be captured by sqlserver 2000 which is resolved in the next version sqlserver 2005 using the try catch block.