Nested queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested queries

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2010-02-16 : 03:44:15

Hi,

I'm having some trouble grasping how to nest queries properly. It's fairly simple if I only had to do something like
select * from table
where table.id = (select id)

But...now I have to construct a query that will do the following:
1. Find next available ID from one table:

SELECT top(1) ord.order_id, @intranet_user, getdate()
FROM o_order AS ord LEFT OUTER JOIN bo_order_reservation ON ord.order_id = bo_order_reservation.order_id
WHERE ((ord.status IN (10)) and ((bo_order_reservation.id is null) OR (DateDiff(hh, reservation_time, getdate()) > 1)))

2. Insert this into the reservation table:

INSET INTO reservation
(id , intranet_user, time)

3. If this exists from before (but has expired), delete that old row or change it

???

My statement until now is like this (the last part was a "hint" from my boss, but since I have no clue what it does I cannot implement it properly

DECLARE @id int;
INSET INTO reservation
(id , intranet_user, time)
(SELECT top(1) ord.order_id, @intranet_user, getdate()
FROM o_order AS ord LEFT OUTER JOIN
      bo_order_reservation ON ord.order_id = bo_order_reservation.order_id
WHERE ((ord.status IN (10)) and ((bo_order_reservation.id is null) OR (DateDiff(hh, reservation_time, getdate()) > 1))))
SELECT @id=SCOPE_IDENTIY()
SELECT *
from reserveration
INNER JOIN o_order ON bla bla
WHERE reservation.id = @id

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 03:47:37

what will determine if its expired? is it just presence of a record for user with older date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tiwas
Starting Member

37 Posts

Posted - 2010-02-16 : 04:16:10

The reservation should expire after one hour, so this seems to do the main objectives:

DECLARE @id int;
INSERT INTO bo_order_reservation
(order_id, intranet_user, reservation_time)
( SELECT top(1) ord.order_id, @intranet_user, getdate()
FROM o_order AS ord LEFT OUTER JOIN
      bo_order_reservation ON ord.order_id = bo_order_reservation.order_id
WHERE ((ord.status IN (10)) and ((bo_order_reservation.id is null) OR (DateDiff(hh, reservation_time, getdate()) > 1))))
set @id=SCOPE_IDENTITY()


SELECT order_id
from bo_order_reservation
WHERE id = @id

What it doesn't do, however, is delete/change the record if it's already in there. This query could create several rows for one case in the event that person 1 reserves a case, goes to lunch. Person 2 reserves the case, gets hit by a car. Person 3...you get the point without me finding more ways a person can end up letting a reservation expire ;)

Thanks :)

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 04:22:00

"without me finding more ways a person can end up letting a reservation expire ;"

Remind me to only ever reserve a case and then go to lunch !!

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 04:28:26

[code]
INSERT INTO bo_order_reservation(order_id, intranet_user, reservation_time)
SELECT order_id, intranet_user, reservation_time
FROM
(
SELECT top(1) ord.order_id, @intranet_user AS intranet_user, getdate() AS reservation_time
FROM o_order AS ord
LEFT OUTER JOIN bo_order_reservation
ON ord.order_id = bo_order_reservation.order_id
WHERE ord.status IN (10)
and
(
bo_order_reservation.id is null
OR DateDiff(hh, reservation_time, getdate()) > 1
)
) AS X
WHERE NOT EXISTS (SELECT * FROM bo_order_reservation AS O WHERE O.order_id = X.order_id)
SELECT @MyRowCount = @@ROWCOUNT, @id=SCOPE_IDENTITY()

IF @MyRowCount = 0
BEGIN
... Insert did not happen, row exists ...
END
[/code]
Problem I have is knowing what the ord.order_id was when the row already exists (i.e. within the scope of this transaction so that the following Delete/Update is atomic)

Are you using SQL 2008?

tiwas
Starting Member

37 Posts

Posted - 2010-02-16 : 04:37:10

quote:
Originally posted by Kristen

"without me finding more ways a person can end up letting a reservation expire ;"

Remind me to only ever reserve a case and then go to lunch !!

hehe...I'm a BOFH at heart. Stems from several years as an admin before getting back into development. I will gladly remind anyone I like to just go to lunch and have a good time. It's when I tell people they "should go to lunch" people should start worrying :p

tiwas
Starting Member

37 Posts

Posted - 2010-02-16 : 04:39:55

quote:
Originally posted by Kristen
[brProblem I have is knowing what the ord.order_id was when the row already exists (i.e. within the scope of this transaction so that the following Delete/Update is atomic)

Are you using SQL 2008?

What do you mean? This is a manual process where only a handfull of people are reserving cases manually. This function is part of "save and open next case" (save old case, find next available, reserve it, open it for edits - in one button).

I'm using MSSQL 2005, by the way. Does it matter to the solution?

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 05:39:59

"This function is part of "save and open next case" (save old case, find next available, reserve it, open it for edits - in one button)"

Yup, but we need to make the transaction ATOMIC - so that two users can update the database simultaneously and "Next ID" is constant/consistent throughout the transaction.

Otherwise we can do:

Attempt INSERT of next number - but it already exists.
(another user does an insert here)
DELETE record matching highest number (Oops! we just deleted the second user's record

)

"MSSQL 2005"

OK - SQL 2008 has a useful command that would have made this easier.

I'm no good at forcing record locks, we specifically avoid that in our application, so someone else will have to help with that.

Its probably something like


BEGIN TRANSACTION

SELECT @order_id = ord.order_id,
       @bln_bo_order_reservationExists = CASE WHEN bo_order_reservation.order_id IS NULL THEN 0 ELSE 1 END
FROM o_order AS ord 
        LEFT OUTER JOIN bo_order_reservation WITH (Update Intent Lock)
            ON ord.order_id = bo_order_reservation.order_id
    WHERE ord.status IN (10)
          and
          (
              bo_order_reservation.id is null
              OR DateDiff(hh, reservation_time, getdate()) > 1
          )

IF @bln_bo_order_reservationExists = 1
BEGIN
    DELETE bo_order_reservationExists
    WHERE order_id = @order_id
END
ELSE
BEGIN
    INSERT INTO bo_order_reservation(order_id, intranet_user, reservation_time)
    SELECT @order_id, @intranet_user, getdate()
END
... Check if any errors, if so ROLLBACK, otherwise:
COMMIT

tiwas
Starting Member

37 Posts

Posted - 2010-02-16 : 06:47:53

Thanks!

But...couldn't I do something like the following? Just trying to learn some while I'm at it ;)

INSERT INTO bo_order_reservation(order_id, intranet_user, reservation_time)
SELECT order_id, intranet_user, reservation_time
FROM
(
    SELECT top(1) ord.order_id, @intranet_user AS intranet_user, getdate() AS reservation_time
    FROM o_order AS ord 
        LEFT OUTER JOIN bo_order_reservation with (xlock)
            ON ord.order_id = bo_order_reservation.order_id
    WHERE ord.status IN (10)
          and
          (
              bo_order_reservation.id is null
              OR DateDiff(hh, reservation_time, getdate()) > 1
          )
) AS X
WHERE NOT EXISTS (SELECT * FROM bo_order_reservation AS O WHERE O.order_id = X.order_id)
SELECT @MyRowCount = @@ROWCOUNT, @id=SCOPE_IDENTITY()

IF @MyRowCount = 0
BEGIN
   ... Insert did not happen, row exists ...
END

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 07:28:21

Probably ... but the Locking Hints is not something I know about.

Actually I don't think you want the EXISTS - its the same as the LEFT JOIN so adding


AND bo_order_reservation.order_id IS NULL

to the (inner) WHERE clause will do I think?

Note that you will get Zero Rows returned if no matching order can be found (as well as a matching order with a row already existing in bo_order_reservation)

tiwas
Starting Member

37 Posts

Posted - 2010-02-19 : 04:11:45

Thanks for the help!

I ended up using the following:

declare @order_id int 

set @order_id = 0

 

insert into dbo.bo_order_reservation (order_id, intranet_user, reservation_time)

 

SELECT     TOP (1) ord.order_id, @user_id AS Expr1, GETDATE() AS Expr2

FROM         o_order AS ord INNER JOIN

                      o_external_status_log AS lo ON ord.status_logistics = lo.id LEFT OUTER JOIN

                      bo_order_reservation AS bo ON ord.order_id = bo.order_id

WHERE     (ord.status IN (10)) and ((GETDATE() > DATEADD(hour, 1, bo.reservation_time)) or (bo.reservation_time is null))

ORDER BY NEWID()

if @@rowcount > 0

      begin

            -- Fjern null-bestillinger

            if (@order_id = 0) or (@order_id is null)

                  begin

                        delete from dbo.bo_order_reservation where order_id = 0

                  end

            select order_id

            from dbo.bo_order_reservation

            where intranet_user = @user_id and reservation_time = (select max(reservation_time) from dbo.bo_order_reservation where intranet_user = @user_id)

      end

else

      select 0

That should hopefully not require any sort of locking (yes, there's probably a microsecond in there where something might occur), and my boss was very specific about not locking the db for anything ;)

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources