Cursor
-
I want my syntax to go through the GS_BOOK table and retieve the Booking ID
- Use a select statement against the G_Detail to retrieve the number of records ..(let's say i get back four records)
- Insert those four records into a #temp_table which has an additional incrementing field ..(Z_ID)
- Move the #temp_table information into tevents tables which will now have
Z_ID A_Name Booking
1 Hero 112
2 Disturb 112
3 Winner 112
4 Reality 112
then delete the temp table and run the process and... because GS_BOOK has about 100 Booking ID records.
my cursor only goes through once.
Use Events
Declare @booking int
Declare booking_cur Cursor FOR
Select Booking
From GS_Book
Where Booking Is Not Null
Group By Booking
Order By Booking
Create Table #temp_details(
Z_Id int Not Null Identity(1,1),
A_Name varchar(254),
Booking int)
Open booking_cur
Fetch booking_cur into @booking
Insert Into #temp_details(A_Name, Booking)
Select A_Name, Booking
From GS_Detail Where Booking = @booking;
Insert Into tevents(Z_Id, A_Name, Booking)
Select Z_Id, A_Name, Booking From #temp_details
Drop table #temp_details
-
You will need to put a loop around the fetch, create and insert statements to do each row selected.
But why are you using a cursor, this should do the same
Create Table #temp_details(
row_Id int Not Null Identity(1,1),
A_Name varchar(254),
Booking int,
Z_Id int)
Insert Into #temp_details(A_Name, Booking, Z_Id)
Select d.A_Name, d.Booking, 0
From GS_Book b
Inner Join GS_Detail d
On d.Booking = b.Booking
Order By d.Booking
UPDATE t
SET t.Z_Id = (t.row_Id - m.MinRowID) + 1
FROM #temp_details t
INNER JOIN (SELECT Booking,MIN(row_Id) AS 'MinRowID'
FROM #temp_details
GROUP BY Booking) m
ON m.Booking = t.Booking
Insert Into tevents(Z_Id, A_Name, Booking)
Select Z_Id, A_Name, Booking
From #temp_details
Drop table #temp_details
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks!!! That worked!!!!
-
i hate to be a pest, but i was wondering is there a way to sort the
...
UPDATE t
SET t.Z_Id = (t.row_Id - m.MinRowID) + 1
FROM #temp_details t
INNER JOIN (SELECT Booking,MIN(row_Id) AS 'MinRowID'
FROM #temp_details
GROUP BY Booking) m
ON m.Booking = t.Booking
....
in your solution
-
All the update does is to set Z_Id to an incrementing number starting at 1 for each Booking. The order of the data is done by the INSERT statement, add columns to the ORDER BY.
Far away is close at hand in the images of elsewhere.
Anon. -
thanks for the reply.
i just want to explain my dilema
i have three tables
BOOKING
bookId int uniqueidentifier
bookOp varchar(255)
BOOKING_DETAILS
bookid int
z_id nvarchar(255)
detail nvarchar(255)
BOOKING_STAFF
bookId int
staff nvarchar(255)
z_id_row.
I can create the relationship between
BOOKING.bookId = BOOKING_DETAILS.bookid
AND BOOKING.bookId = BOOKING_STAFF.bookid
But instead of getting results like
booking.bookOP booking_details.detail booking_staff.staff
JOHN DOE CAFETERIA JAMES WOOD
JANE DOE LIBRARY DORTHY HARRIS
I'm getting
booking.bookOP booking_details.detail booking_staff.staff
JOHN DOE CAFETERIA JAMES WOOD
JANE DOE LIBRARY DORTHY HARRIS
JOHN DOE CAFETERIA DORTHY HARRIS
JANE DOE LIBRARY JAMES WOOD
But in BOOKING_STAFF there is a BOOKING.z_id_row
and in BOOKING_DETAILS.detail there is BOOKING_DETAILS.z_id
BOOKING.z_id_row booking_details.z_id booking_staff.staff
1 789:12 JAMES WOOD
2 789:13 DORTHY HARRIS
This DOS program apparently uses the BOOKING.z_id_row and booking_details.z_id as relating information based on order. That's why I asked if I could sort on the update based on field, because i'd sort on booking_details.z_id
Thanks for your help.
ps. this isn't my database.
-
Firstly your relationship is incompete, what you are asking for is all DETAILS for bookId and all STAFF for a bookId and that is what you got. You need to clarify the relationship between DETAILS and STAFF more accurately to remove unwanted multiples. If z_id_row can be matched to z_id then your query should look like this
SELECT b.bookOp, bd.detail, staff
FROM BOOKING b
INNER JOIN BOOKING_DETAILS bd
ON bd.bookId = b.bookId
INNER JOIN BOOKING_STAFF bs
ON bs.bookId = bd.bookId
AND bs.z_id_row = z_id
but your posted info
BOOKING.z_id_row booking_details.z_id booking_staff.staff
1 789:12 JAMES WOOD
2 789:13 DORTHY HARRIS
states that is not the case but your statement 'This DOS program apparently uses the BOOKING.z_id_row and booking_details.z_id as relating information based on order.' indicates that there is a relationship.
What and how is the relationship made?
In your first post you wanted to generate z_id as incremented numbers (starting from 1 for each booking), there seems to be some disparity between the requirements or both your posts.
Can you post some sample data for each table and what data you expect in the events table, if in fact that is the purpose.
Far away is close at hand in the images of elsewhere.
Anon. -
thanks!
All together I should get 5 records because the order of the GS_DETAIL.Z_ID And the GS_STAFF.Z_ASSOC_ROW don't relate, but for 3354:12 the S_Name is 'Group Sales'
Create Table GS_BOOK
(
Booking int NOT NULL IDENTITY (1, 1),
BOOK_OP varchar(50) NULL
 
ON [PRIMARY]INSERT INTO GS_BOOK (BOOK_OP) VALUES ('JOHND')
INSERT INTO GS_BOOK (BOOK_OP) VALUES ('SALLYM')
Create table GS_DETAIL
(Z_ID varchar(254),
G_NAME varchar(254),
BOOKING int)
INSERT INTO GS_DETAIL (Z_ID,G_NAME, BOOKING) VALUES ('3354:12','UPENN',1)
INSERT INTO GS_DETAIL (Z_ID,G_NAME, BOOKING) VALUES ('3354:13','UPENN',1)
INSERT INTO GS_DETAIL (Z_ID,G_NAME, BOOKING) VALUES ('3354:14','UPENN',1)
INSERT INTO GS_DETAIL (Z_ID,G_NAME, BOOKING) VALUES ('3354:45','PMA',2)
INSERT INTO GS_DETAIL (Z_ID,G_NAME, BOOKING) VALUES ('3354:46','PMA',2)
Create table GS_STAFF
(BOOKING int,
S_NAME varchar(254),
Z_ASSOC_ROW numeric(9,0))
INSERT INTO GS_STAFF (BOOKING, S_NAME, Z_ASSOC_ROW) VALUES (1,'GROUP SALES',1)
INSERT INTO GS_STAFF (BOOKING, S_NAME, Z_ASSOC_ROW) VALUES (1,'DEVELOPMENT',2)
INSERT INTO GS_STAFF (BOOKING, S_NAME, Z_ASSOC_ROW) VALUES (1,'HUMAN RESOURCES',3)
INSERT INTO GS_STAFF (BOOKING, S_NAME, Z_ASSOC_ROW) VALUES (2,'MEMBERSHIP',1)
INSERT INTO GS_STAFF (BOOKING, S_NAME, Z_ASSOC_ROW) VALUES (2,'MARKETING',2)
Select * from GS_BOOK
Select * from GS_DETAIL
Select * from GS_Staff
THANKS!
-
OK try this
CREATE TABLE #assoc (rowid int IDENTITY(1,1), BOOKING int, Z_ASSOC_ROW numeric(9,0) ,Z_ID varchar(254))
INSERT INTO #assoc (BOOKING, Z_ASSOC_ROW, Z_ID)
SELECT BOOKING, 0, Z_ID
FROM GS_DETAIL
ORDER BY BOOKING, Z_ID
UPDATE t
SET t.Z_ASSOC_ROW = (t.rowid - m.MinRowID) + 1
FROM #assoc t
INNER JOIN (SELECT Booking,MIN(rowid) AS 'MinRowID'
FROM #assoc
GROUP BY Booking) m
ON m.Booking = t.Booking
SELECT b.BOOK_OP, bd.G_NAME, bs.S_NAME
FROM GS_BOOK b
INNER JOIN GS_DETAIL bd
ON bd.Booking = b.Booking
INNER JOIN #assoc a
ON a.BOOKING = bd.BOOKING
AND a.Z_ID = bd.Z_ID
INNER JOIN GS_STAFF bs
ON bs.Booking = a.Booking
AND bs.Z_ASSOC_ROW = a.Z_ASSOC_ROW
DROP TABLE #assoc
it should give you the result
BOOK_OP G_NAME S_NAME
------- ------ ------
JOHND UPENN GROUP SALES
JOHND UPENN DEVELOPMENT
JOHND UPENN HUMAN RESOURCES
SALLYM PMA MEMBERSHIP
SALLYM PMA MARKETING
Far away is close at hand in the images of elsewhere.
Anon. -
WOW!!!
THANK YOU for you time and assistance!!!!!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply