Can this be changed to a CASE construct?

-- First, some DDL:
CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
/*
What I want to achieve is:
If there are no rows in #TEMP
OR
If any of the expiration dates are NULL
OR
If any of the expiration dates are <= TODAY
return the string 'NOT CLEARED'
ELSE return the string 'CLEARED'
The following works fine:
*/
DECLARE @.ClinicClearanceStatus varchar(12)
SET @.ClinicClearanceStatus = 'CLEARED'
IF
((SELECT COUNT(*) FROM #TEMP) = 0)
OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
OR
((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
BEGIN
SET @.ClinicClearanceStatus = 'NOT CLEARED'
END
SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
/*
I'm just curious if the preceding logic could be changed to a CASE
construct; something like
SELECT CASE WHEN < throw the IF statement in here somehow >
THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
As always, thanks in advance for all your help.
Carl
*/CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
SELECT
CASE
WHEN
(
((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
)
THEN
'NOT CLEARED'
ELSE
'CLEARED' END as status|||Thanks for your reply, Johnny. I checked it out, and it looks like it's
not testing for NULLs correctly; i.e., when I change all of the
expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
NULL dates intact, it returns CLEARED. I suspect it's a very minor
change but I don't know where to make it . . .
Thanks --
Carl
Johnny D wrote:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
>
> SELECT
> CASE
> WHEN
> (
> ((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
> ((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
> )
> THEN
> 'NOT CLEARED'
> ELSE
> 'CLEARED' END as status
>|||You can do it, as JohnyD pointed out, but IMO the if then logic is easier to
follow than a case statement in a select.
Of course it depends on where and how you plan on using this return value.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:uMy7B9CgGHA.1792@.TK2MSFTNGP03.phx.gbl...
> -- First, some DDL:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
> /*
> What I want to achieve is:
> If there are no rows in #TEMP
> OR
> If any of the expiration dates are NULL
> OR
> If any of the expiration dates are <= TODAY
> return the string 'NOT CLEARED'
> ELSE return the string 'CLEARED'
> The following works fine:
> */
> DECLARE @.ClinicClearanceStatus varchar(12)
> SET @.ClinicClearanceStatus = 'CLEARED'
> IF
> ((SELECT COUNT(*) FROM #TEMP) = 0)
> OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
> OR
> ((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
> BEGIN
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> END
> SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
> /*
> I'm just curious if the preceding logic could be changed to a CASE
> construct; something like
> SELECT CASE WHEN < throw the IF statement in here somehow >
> THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
> As always, thanks in advance for all your help.
> Carl
> */|||I think it is just a typo.
Change the last "IS NULL" in the case function to " > 0"
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply, Johnny. I checked it out, and it looks like it's
> not testing for NULLs correctly; i.e., when I change all of the
> expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
> NULL dates intact, it returns CLEARED. I suspect it's a very minor
> change but I don't know where to make it . . .
> Thanks --
> Carl
> Johnny D wrote:|||Thanks for the clarification Jim -- that did it.
I appreciate your and Johnny's time.
Carl
Jim Underwood wrote:
> I think it is just a typo.
> Change the last "IS NULL" in the case function to " > 0"
> "Carl Imthurn" <nospam@.all.thanks> wrote in message
> news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
>
>
>|||On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
(snip)
>What I want to achieve is:
>If there are no rows in #TEMP
>OR
>If any of the expiration dates are NULL
>OR
>If any of the expiration dates are <= TODAY
>return the string 'NOT CLEARED'
>ELSE return the string 'CLEARED'
Hi Carl,
This can be greatly simplified:
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
Or, if you really want a CASE:
SET @.ClinicClearanceStatus =
(SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
FROM #TEMP)
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Hugo --
First, thanks for posting back to me.
I tested your statement with COALESCE and it worked for conditions 2 and
3 ( NULL expiration dates and expiration dates < = today ) but failed on
condition 1 (empty table). In other words, if I have an empty table, the
SELECT statement returns CLEARED where it should return NOT CLEARED.
Here's some DDL if you have a chance (and desire) to pursue this further:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.TEST')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.TEST
CREATE TABLE TEST (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ1', '2006-05-31')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ2', '2006-05-29')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ3', '2007-05-01')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ4', '2006-05-25')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ5', '2006-05-26')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ6', '2005-01-01')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ7')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ8')
DECLARE @.ClinicClearanceStatus varchar(20)
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
Thanks for your help Hugo - I appreciate it.
Carl
Hugo Kornelis wrote:
> On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
> (snip)
>
>
> Hi Carl,
> This can be greatly simplified:
> IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> Or, if you really want a CASE:
> SET @.ClinicClearanceStatus =
> (SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
> CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
> FROM #TEMP)
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>|||Carl,
You could change the last section to this...
DECLARE @.ClinicClearanceStatus varchar(20)
IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
'') <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
HTH
Barry|||Barry --
That did it! Thanks much -- I appreciate your time.
Have a great day.
Carl
Barry wrote:
> Carl,
> You could change the last section to this...
>
> DECLARE @.ClinicClearanceStatus varchar(20)
> IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
> '') <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> SELECT @.ClinicClearanceStatus
>
> HTH
> Barry
>

can someone improve this

Case of study:
I have warehouse which stores items.
Wharehouse has defined different locations - location is represented with
locationID.
(location has also type and distance - that is the distance in
meters from start position, but this data is used just for order)
Each location has one or more palette. Palette has unique label: itemOZN.
Each palette has many items(articles), and item is marked with itemID.
Each combination of palette and item has different stockID
(on the same palette can be items with the same itemID but different
properties)
So, if you want to define one item in stock, you must have its stockID.
When I get request to export one ot more items(articles) with some quantity,
I must find
all the positions of the items in the warehouse
(the same article can be on different locations) and take the most
appropriate ones (already reserved are at the end of the choice, and also
closer articles has
the precedence over the more distantiated ones,
also if the production date is older, than the article must go out from
stock before the new ones, and so on..)
When I find the appropriate items, I must wite them into reservation table.
That is stockID and quantity.
I hope that it's clear enough. It's simple example and I solve it with 2
cursor.
It works perfect but with slow performance.
You have my example with all DDL and test data.
I would like to know, if it's possible to create the same solution without
cursor because of performance reason.
The working code:
declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
datetime,@.lokType int,@.stockID int
declare @.availableQ decimal(15,5)
BEGIN TRANSACTION
--first, I lookup for all required items to export from warehouse
declare cCur cursor local for select
itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
open cCur
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
while @.@.fetch_status=0
begin
--for each item I find all stocks and I order stocks from most appropriate
to the less appropriate(ORDER BY)
--if quality is gived I must find the items with required or better quality
else doesn't matter, the same is with production date
declare cCur1 cursor local for
select T1.stockID,T1.availableQ FROM
(select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
JOIN dbo.tblItems i1
ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
l.distance
from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
i.locationID=l.locationID
AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservations
r ON i.stockID=r.stockID
WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDate)
AND
(@.quality is null OR i.itemQuality>=@.quality)
GROUP BY i.stockID,i.itemQuantity,l.distance
having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
open cCur1
fetch next from cCur1 INTO @.stockID,@.availableQ
--I reserve items until reach the required quantity
while @.@.fetch_status=0
begin
if @.availableQ>=@.requiredQ
begin
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.requiredQ
SET @.requiredQ=0
BREAK
end
else
begin
SET @.requiredQ=@.requiredQ-@.availableQ
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.availableQ
end
fetch next from cCur1 INTO @.stockID,@.availableQ
end
close cCur1
deallocate cCur1
if @.requiredQ>0
begin
RAISERROR(60017,11,1)
break
end
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
end
close cCur
deallocate cCur
delete from dbo.tblRequiredItems
if @.@.error=0
COMMIT TRANSACTION
else
ROLLBACK TRANSACTION
The DDL with sample data:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblLocation]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblRequiredItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblReservations]
GO
CREATE TABLE [dbo].[tblItems] (
[stockID] [int] IDENTITY (1, 1) NOT NULL ,
[itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[itemQuantity] [decimal](15, 5) NOT NULL ,
[itemQuality] [int] NOT NULL ,
[itemProductionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLocation] (
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[lokType] [int] NULL ,
[distance] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblRequiredItems] (
[requireID] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[quality] [int] NULL ,
[quantity] [decimal](15, 5) NOT NULL ,
[productionDate] [datetime] NULL ,
[lokType] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblReservations] (
[reservationID] [int] IDENTITY (1, 1) NOT NULL ,
[stockID] [int] NULL,
[quantity] [decimal](15, 5) NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblItems] ADD
CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLocation] ADD
CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
(
[locationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRequiredItems] ADD
CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
(
[requireID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblReservations] ADD
CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
(
[reservationID]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('A001',1,100)
INSERT INTO [tblLocation]([locationID], [lokType], [distance])
VALUES('B001',1,200)
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('C001',2,150)
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0001','A001',20,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0002','A001',15,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0003','A001',50,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0001','B001',30,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0002','B001',60,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0003','B001',40,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0001','B001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0002','B001',10,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0003','B001',30,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0001','C001',20,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0002','C001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0003','C001',25,1,'20050
202')
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(1,5)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(5,10)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(9,12)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0001',1,40,'20050101',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0002',1,10,'20041212',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0003',null,10,null,2)>> can someone improve this
Nope.
"simon" wrote:

> Case of study:
> I have warehouse which stores items.
> Wharehouse has defined different locations - location is represented with
> locationID.
> (location has also type and distance - that is the distance in
> meters from start position, but this data is used just for order)
> Each location has one or more palette. Palette has unique label: itemOZN.
> Each palette has many items(articles), and item is marked with itemID.
> Each combination of palette and item has different stockID
> (on the same palette can be items with the same itemID but different
> properties)
> So, if you want to define one item in stock, you must have its stockID.
> When I get request to export one ot more items(articles) with some quantit
y,
> I must find
> all the positions of the items in the warehouse
> (the same article can be on different locations) and take the most
> appropriate ones (already reserved are at the end of the choice, and also
> closer articles has
> the precedence over the more distantiated ones,
> also if the production date is older, than the article must go out from
> stock before the new ones, and so on..)
> When I find the appropriate items, I must wite them into reservation table
.
> That is stockID and quantity.
> I hope that it's clear enough. It's simple example and I solve it with 2
> cursor.
> It works perfect but with slow performance.
> You have my example with all DDL and test data.
> I would like to know, if it's possible to create the same solution without
> cursor because of performance reason.
> The working code:
> declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
> datetime,@.lokType int,@.stockID int
> declare @.availableQ decimal(15,5)
> BEGIN TRANSACTION
> --first, I lookup for all required items to export from warehouse
> declare cCur cursor local for select
> itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
> open cCur
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> while @.@.fetch_status=0
> begin
> --for each item I find all stocks and I order stocks from most appropriate
> to the less appropriate(ORDER BY)
> --if quality is gived I must find the items with required or better qualit
y
> else doesn't matter, the same is with production date
> declare cCur1 cursor local for
> select T1.stockID,T1.availableQ FROM
> (select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
> reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
> JOIN dbo.tblItems i1
> ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
> l.distance
> from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
> i.locationID=l.locationID
> AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservation
s
> r ON i.stockID=r.stockID
> WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDat
e)
> AND
> (@.quality is null OR i.itemQuality>=@.quality)
> GROUP BY i.stockID,i.itemQuantity,l.distance
> having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
> ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
> open cCur1
> fetch next from cCur1 INTO @.stockID,@.availableQ
> --I reserve items until reach the required quantity
> while @.@.fetch_status=0
> begin
> if @.availableQ>=@.requiredQ
> begin
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.requiredQ
> SET @.requiredQ=0
> BREAK
> end
> else
> begin
> SET @.requiredQ=@.requiredQ-@.availableQ
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.availableQ
> end
> fetch next from cCur1 INTO @.stockID,@.availableQ
> end
> close cCur1
> deallocate cCur1
> if @.requiredQ>0
> begin
> RAISERROR(60017,11,1)
> break
> end
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> end
> close cCur
> deallocate cCur
> delete from dbo.tblRequiredItems
> if @.@.error=0
> COMMIT TRANSACTION
> else
> ROLLBACK TRANSACTION
> The DDL with sample data:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblLocation]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblRequiredItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tblReservations]
> GO
> CREATE TABLE [dbo].[tblItems] (
> [stockID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemQuantity] [decimal](15, 5) NOT NULL ,
> [itemQuality] [int] NOT NULL ,
> [itemProductionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblLocation] (
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [lokType] [int] NULL ,
> [distance] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblRequiredItems] (
> [requireID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [quality] [int] NULL ,
> [quantity] [decimal](15, 5) NOT NULL ,
> [productionDate] [datetime] NULL ,
> [lokType] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblReservations] (
> [reservationID] [int] IDENTITY (1, 1) NOT NULL ,
> [stockID] [int] NULL,
> [quantity] [decimal](15, 5) NOT NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblItems] ADD
> CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
> (
> [stockID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblLocation] ADD
> CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
> (
> [locationID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblRequiredItems] ADD
> CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
> (
> [requireID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblReservations] ADD
> CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
> (
> [reservationID]
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('A001',1,100)
> INSERT INTO [tblLocation]([locationID], [lokType], [distance])
> VALUES('B001',1,200)
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('C001',2,150)
>
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0001','A001',20,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0002','A001',15,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0003','A001',50,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0001','B001',30,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0002','B001',60,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0003','B001',40,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0001','B001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0002','B001',10,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0003','B001',30,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0001','C001',20,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0002','C001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0003','C001',25,1,'20050
202')
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(1,5)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(5,10)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(9,12)
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0001',1,40,'20050101',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0002',1,10,'20041212',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0003',null,10,null,2)
>
>|||Once you design actual tables and partially miss the business requirements,
there's not much anyone can do about it. Don't design any tables until you
know exactly what you need.
Good designs look good on paper first. Identify your entities, identify
relationships, and build a use case before you start designing tables.
ML

Can OPENXML insert rows with some duplicate rows?

Hi All,
I'd like to insert rows that maybe have some duplicate rows with OPENXML
from stored procedure. In normal case, I've found that it cannot do that.
Is there anybody know how to accomplish this?
Thanks in advance,
Thana N.
I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
wondor about the performance of it. What is the best way for performance?
Thana N.
"Thana N." wrote:

> Hi All,
> I'd like to insert rows that maybe have some duplicate rows with OPENXML
> from stored procedure. In normal case, I've found that it cannot do that.
> Is there anybody know how to accomplish this?
> Thanks in advance,
> Thana N.
>
|||In this case you will have to decide how to deal with the duplicates.
This depends a lot on your data. If you were just inserting account
numbers then you could do:
insert into x
select acct#
from openxml(...)
group by acct#
In some cases you may want to sum the data (using sum), get the max, or
get the min. It will depend on your data.
insert into x
select acct#, sum(charges), min(timeleft), max(lastcall)
from openxml(...)
group by acct#
|||If your query would execute OpenXML more than once with the same resulting
rowset or it is large enough to benefit from an index, you may want to
insert the data from OpenXML into a temp table (and define an index if it
seems more performing).
HTH
Michael
"Thana N." <ThanaN@.discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@.microsoft.com...[vbcol=seagreen]
> I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
> wondor about the performance of it. What is the best way for performance?
> Thana N.
> "Thana N." wrote:

Can one exec a stored procedure in a case statement in a stored procedure?

Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***No, although if you can convert the proc into a user-defined function, you
could use the UDF.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Colette Horter" <coletten@.gmail.com> wrote in message
news:e67fw6RlGHA.1340@.TK2MSFTNGP02.phx.gbl...
Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***|||>> Can one execute a stored procedure and get a value back from it in a CASE stat
ement [sic] in a stored procedure? <<
Learn the basics and life is so easy:
There is no CASE statement in SQL. There is a CASE expression. What
does an expression do? It returns a scalar value of a known data type.
Does a stored procedure return a scalar value of a known data type?
NO! But a scalar function call does!

>From you psuedo-code posting, it looks like you also need to learn is
that rows are not records; fields are not columns; tables are not
files. You might also want to learn Standard SQL's AS for alias
assignments, too. That will make your code portable and readable to
the next guy to maintain it.
I will ignore the remark about pulling out your hair :)|||>>> Does a stored procedure return a scalar value of a known data type?
YES !! The data type is INTEGER.
I mean - really, please read the manual for MICROSOFT SQL SERVER and be
aware of the group you are posting to which is for MICROSOFT SQL SERVER.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150904673.628439.120580@.g10g2000cwb.googlegroups.com...
> Learn the basics and life is so easy:
> There is no CASE statement in SQL. There is a CASE expression. What
> does an expression do? It returns a scalar value of a known data type.
> Does a stored procedure return a scalar value of a known data type?
> NO! But a scalar function call does!
>
> that rows are not records; fields are not columns; tables are not
> files. You might also want to learn Standard SQL's AS for alias
> assignments, too. That will make your code portable and readable to
> the next guy to maintain it.
> I will ignore the remark about pulling out your hair :)
>|||>> The data type is INTEGER [return from T-SQL stored procedure] <<
No, that is a completion flag and it is no more a result data type than
a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
-- because of internal representation, but they are VERY different
domains. Think abstract, not current dialect implementation.
You might not remember the C programming language, but all statements
returned such flags (it was part of the DEC PDP-11 hardware that the
language was based on). If you want a scalar result value, you use a
function.|||Thank you so much! I put it in a function and now my sproc actually
saves!!! Just need to finish it up now. c",)
*** Sent via Developersdex http://www.examnotes.net ***|||> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
WRONG! WILL YOU PLEASE READ THE MANUAL AND ACTUALLY USE THE PRODUCT!!!!!
You can use RETURN to pass back any INTEGER, e.g. RETURN( 1 ) etc...
AGAIN: CHECK THE MANUAL UNDER STORED PROCEDURE!!!!

> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
Yes, I remember - I also remember PL/1, C++ and PASCAL.....
But, have you actually used these languages outside of a book / class room?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150925049.724707.252820@.r2g2000cwb.googlegroups.com...
> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
>|||>> - I also remember PL/1, C++ and PASCAL.. But, have you actually used thes
e languages outside of a book / class room? <<
PL/I at Coca Cola World HQ in Atlanta, Pascal at Southern Califrnia
Edison and I nver learned C++.

Can not install SP4 for SQL Server 7.0 on Win2k

In my case this is a SP4 install on a reinstall of SQL 7.0 with no user data
bases.
I have followed all the tips about turning off all non-essential services, e
tc., per the kb article and various other docs, but the 7.0 sp4 installation
still fails with the message
"One or more of your user databases have been marked read only, offline or s
uspect (not recovered). Correct the problem and restart setup."
The log files seem to show everything is ok but the install still fails.
I did try one additional test -- running the scm and osql commands shown at
the end of the sqlsp.log file.
When I ran the osql command, I get the expected results of 0 but also get an
additional message for the ODBC driver manager: "The driver doesn't support
the version of ODBC behavior that the application requested (see SQLSetEnvA
ttr)."
I suspect that this additional message is causing the failure.
(Running on W2k / SP4).
-- Vikram Jayaram [MS] wrote: --
* Look at the sqlsp.log for error messages
* It's a good idea to turn off all 3rd party and non-essential services
before you prform the Service Pack upgrade.
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.How are you installing the setup for sp4. What does the sqlsp.log say? Are
you sure there are no other sql70 instances on the box?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.