MS SQL Server, tricky query?

tricky query?

Author	Message
Tom #1 / 6	tricky query? I'm trying to create an efficient query or procedure to order records that may contain a child record.. If you look at the data, you'll notice the 3rd record has a ItemRefID pointing to the 2nd record.. And I'll want to be able to put item 1 to item 4, or put the child item to another parent item, properly changing the order. I know I can do this by looping through a cursor, but I'm trying to find a faster way.. This is being using for an order system both internally and on the web with alot of traffic, so speed it critical, which is why I'm trying to avoid cursors.. If it matters, I'm using both sql 7 and 2k.. the columns are order number, guidItemID, guidItemRefID, and Order 756978 5BE197FB-258E-11D7-A1CC NULL 1 756978 528CAE5D-258E-11D7-A1CC NULL 2 756978 60CEED08-258E-11D7-A1CC 528CAE5D-258E-11D7-A1CC 3 756978 577EAD27-258E-11D7-A1CC NULL 4 thanks!!
Tue, 19 Jul 2005 23:30:10 GMT

John Bel #2 / 6	tricky query? Hi Tom The task is made trickier because you have not posted ddl or example output and your test data does not make it obvious what you are trying to do. It is also easier if you avoid using key words as column names! To order by Order SELECT [order number], guidItemID, guidItemRefID, Order FROM MyTable ORDER BY Order To order by parent: select [order number],guidItemID, guidItemRefID, [Order] from mytable ORDER BY COALESCE (guidItemRefID,guidItemID), [Order] DDL and test data. CREATE TABLE MYTable ( [order number] int, guidItemID varchar(255) , guidItemRefID varchar(255), [Order] INT ) INSERT INTO MyTable values ( 756978, '5BE197FB-258E-11D7-A1CC', NULL, 1 ) GO INSERT INTO MyTable values ( 756978, '528CAE5D-258E-11D7-A1CC', NULL, 2 ) GO INSERT INTO MyTable values ( 756978, '60CEED08-258E-11D7-A1CC', '528CAE5D-258E-11D7-A1CC' , 4 ) -- Changed GO INSERT INTO MyTable values ( 756978, '577EAD27-258E-11D7-A1CC', NULL, 3 ) GO John Quote: > I'm trying to create an efficient query or procedure to order records that > may contain a child record.. If you look at the data, you'll notice the 3rd > record has a ItemRefID pointing to the 2nd record.. And I'll want to be > able to put item 1 to item 4, or put the child item to another parent item, > properly changing the order. > I know I can do this by looping through a cursor, but I'm trying to find a > faster way.. This is being using for an order system both internally and on > the web with alot of traffic, so speed it critical, which is why I'm trying > to avoid cursors.. If it matters, I'm using both sql 7 and 2k.. > the columns are order number, guidItemID, guidItemRefID, and Order > 756978 5BE197FB-258E-11D7-A1CC NULL 1 > 756978 528CAE5D-258E-11D7-A1CC NULL 2 > 756978 60CEED08-258E-11D7-A1CC 528CAE5D-258E-11D7-A1CC 3 > 756978 577EAD27-258E-11D7-A1CC NULL 4 > thanks!!
Wed, 20 Jul 2005 00:34:25 GMT

Joe Celk #3 / 6	tricky query? Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. Quote: >> I'm trying to create an efficient query or procedure to order records [sic] that may contain a child record [sic].. << If I understand what you are doing, why are orders and details in the same table? Why are you using GUIDs? This model does not feel right .. --CELKO-- * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Wed, 20 Jul 2005 01:13:41 GMT

Tom #4 / 6	tricky query? sorry guys - I did leave out quite a bit of stuff, but I didnt' think it was necessary. I'm working with 2 tables.. Orders and LineItems, the LineItems table is what I showed a little snippet of.. There are more missing guid fields, such as the guidOrderID (FK in LineItems) - but this has no relevance. In LineItems - there are the 2 guid fields, guidItemID (PK) and guidItemRefID. I use guidItemRefID to point to a parent line item - parent as in, an add-on products.. For example: item 1 - plain chair item 2 - armed chair item 3 - fabric upgrade Item 3 would be a child record to the armed chair - but if I wanted to move it so that it was a child of the plain chair instead, I would change the guidItemRefID to the plain chair. Items can also have multiple child items, or sub-items, or whatever you wanna call em.. Maybe my terminology is what threw everyone off - I'm battling some nastiness sickenss at the moment, I'm just happy I found my way home today :-D Anyways - the order of lineitems (intPos is the actual field name for it) is to be used to help ensure the proper order the lineitems are displayed in. guid fields are ceonsecutive, so you can't order that way - and the order of lineitems is necessary - and also necessary for the user to be able to reorder stuff. I'm trying FIRST to accomplish this in a stored procedure since I'll have multiple platforms accessing the sp. I'd rather not replicate the ordering code in the local app and on the web site. MY save order routine is in a sp, and I'd like to control the order through there.. Lets forget the order number (intInvNum is the field name of that one) - here's the data again.. fields here are guidItemID, guidItemRefID and intPos (the position) 5BE197FB-258E-11D7-A1CC NULL 1 528CAE5D-258E-11D7-A1CC NULL 2 60CEED08-258E-11D7-A1CC 528CAE5D-258E-11D7-A1CC 3 577EAD27-258E-11D7-A1CC NULL 4 I would like to call a sp passing the guidItemID and the new position number.. If I pass -the first record shown above- along with a new position of 4 - then items in position 2-3-4 will get moved to 1-2-3 and 1 will become 4 - much like a bubble sort. The reason I titled it a tricky query - is because I want to avoid using a cursor (the worst from the performance standpoint) or by looping thru executing a variety of select and updates (too many statements to be executed there). Sorry for the long winded message - I just want to try to convey the right message, as I think I failed to do that right in the 1st message. thanks a bunch for your time guys - I appreciate it tremendously! Tom Quote: > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > >> I'm trying to create an efficient query or procedure to order records > [sic] that may contain a child record [sic].. << > If I understand what you are doing, why are orders and details in the > same table? Why are you using GUIDs? This model does not feel right > .. > --CELKO-- > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Wed, 20 Jul 2005 11:26:41 GMT

John Bel #5 / 6	tricky query? Hi Tom You have still not posted the DDL. Quote: > sorry guys - I did leave out quite a bit of stuff, but I didnt' think it was > necessary. > I'm working with 2 tables.. Orders and LineItems, the LineItems table is > what I showed a little snippet of.. > There are more missing guid fields, such as the guidOrderID (FK in > LineItems) - but this has no relevance. > In LineItems - there are the 2 guid fields, guidItemID (PK) and > guidItemRefID. I use guidItemRefID to point to a parent line item - parent > as in, an add-on products.. For example: > item 1 - plain chair > item 2 - armed chair > item 3 - fabric upgrade > Item 3 would be a child record to the armed chair - but if I wanted to move > it so that it was a child of the plain chair instead, I would change the > guidItemRefID to the plain chair. This seem wrong to have the structure of this in line items, I would expect there to be a product-hierarchy table that has as list of guidItemID, guidItemRefID. Joe alot of information regarding the implementation of hierarchies, just search google for postings. Quote: > Items can also have multiple child items, > or sub-items, or whatever you wanna call em.. Maybe my terminology is what > threw everyone off - I'm battling some nastiness sickenss at the moment, I'm > just happy I found my way home today :-D Maybe rest is the answer then, what can be so important that forces you off your sick bed? Quote: > Anyways - the order of lineitems (intPos is the actual field name for it) is > to be used to help ensure the proper order the lineitems are displayed in. I am still not sure what this is, if anything is different to my previous post. Quote: > guid fields are ceonsecutive, so you can't order that way - and the order of > lineitems is necessary - and also necessary for the user to be able to > reorder stuff. This is a problem with the hierarchy being mixed with the line items. I would expect hierarchy to be irrelevant in an invoice/po if you impose line numbering. - Hide quoted text - - Show quoted text - Quote: > I'm trying FIRST to accomplish this in a stored procedure > since I'll have multiple platforms accessing the sp. I'd rather not > replicate the ordering code in the local app and on the web site. MY save > order routine is in a sp, and I'd like to control the order through there.. > Lets forget the order number (intInvNum is the field name of that one) - > here's the data again.. > fields here are guidItemID, guidItemRefID and intPos (the position) > 5BE197FB-258E-11D7-A1CC NULL 1 > 528CAE5D-258E-11D7-A1CC NULL 2 > 60CEED08-258E-11D7-A1CC 528CAE5D-258E-11D7-A1CC 3 > 577EAD27-258E-11D7-A1CC NULL 4 > I would like to call a sp passing the guidItemID and the new position > number.. If I pass -the first record shown above- along with a new position > of 4 - then items in position 2-3-4 will get moved to 1-2-3 and 1 will > become 4 - much like a bubble sort. What about: BEGIN BEGIN UPDATE MyTable Set [Order] = [Order] - 1 UPDATE MyTable END ELSE BEGIN UPDATE MyTable Set [Order] = [Order] + 1 UPDATE MyTable END END Quote: > The reason I titled it a tricky query - is because I want to avoid using a > cursor (the worst from the performance standpoint) or by looping thru > executing a variety of select and updates (too many statements to be > executed there). ??? > Sorry for the long winded message - I just want to try to convey the right > message, as I think I failed to do that right in the 1st message. thanks a > bunch for your time guys - I appreciate it tremendously! I have a feeling that we are not really addressing your real problem, but I would hope you would look at the design of your database again. John - Hide quoted text - - Show quoted text - Quote: > Tom > > Please post DDL, so that people do not have to guess what the keys, > > constraints, Declarative Referential Integrity, datatypes, etc. in your > > schema are. > > >> I'm trying to create an efficient query or procedure to order records > > [sic] that may contain a child record [sic].. << > > If I understand what you are doing, why are orders and details in the > > same table? Why are you using GUIDs? This model does not feel right > > .. > > --CELKO-- > > * Sent via Developersdex http://www.developersdex.com * > > Don't just participate in USENET...get rewarded for it!
Wed, 20 Jul 2005 17:57:22 GMT

Itzik Ben-Ga #6 / 6	tricky query? Tom, is this what you're after? CREATE TABLE T1 ( id VARCHAR(10) NOT NULL PRIMARY KEY, pos INT NOT NULL UNIQUE ) INSERT INTO T1 VALUES('A', 1) INSERT INTO T1 VALUES('B', 2) INSERT INTO T1 VALUES('C', 3) INSERT INTO T1 VALUES('D', 4) INSERT INTO T1 VALUES('E', 5) INSERT INTO T1 VALUES('F', 6) DECLARE SELECT T1.* ORDER BY CASE ELSE pos END id pos ---------- ----------- B 2 C 3 D 4 A 1 E 5 F 6 -- BG, SQL Server MVP Solid Quality Learning www.solidqualitylearning.com Quote: > sorry guys - I did leave out quite a bit of stuff, but I didnt' think it was > necessary. > I'm working with 2 tables.. Orders and LineItems, the LineItems table is > what I showed a little snippet of.. > There are more missing guid fields, such as the guidOrderID (FK in > LineItems) - but this has no relevance. > In LineItems - there are the 2 guid fields, guidItemID (PK) and > guidItemRefID. I use guidItemRefID to point to a parent line item - parent > as in, an add-on products.. For example: > item 1 - plain chair > item 2 - armed chair > item 3 - fabric upgrade > Item 3 would be a child record to the armed chair - but if I wanted to move > it so that it was a child of the plain chair instead, I would change the > guidItemRefID to the plain chair. Items can also have multiple child items, > or sub-items, or whatever you wanna call em.. Maybe my terminology is what > threw everyone off - I'm battling some nastiness sickenss at the moment, I'm > just happy I found my way home today :-D > Anyways - the order of lineitems (intPos is the actual field name for it) is > to be used to help ensure the proper order the lineitems are displayed in. > guid fields are ceonsecutive, so you can't order that way - and the order of > lineitems is necessary - and also necessary for the user to be able to > reorder stuff. I'm trying FIRST to accomplish this in a stored procedure > since I'll have multiple platforms accessing the sp. I'd rather not > replicate the ordering code in the local app and on the web site. MY save > order routine is in a sp, and I'd like to control the order through there.. > Lets forget the order number (intInvNum is the field name of that one) - > here's the data again.. > fields here are guidItemID, guidItemRefID and intPos (the position) > 5BE197FB-258E-11D7-A1CC NULL 1 > 528CAE5D-258E-11D7-A1CC NULL 2 > 60CEED08-258E-11D7-A1CC 528CAE5D-258E-11D7-A1CC 3 > 577EAD27-258E-11D7-A1CC NULL 4 > I would like to call a sp passing the guidItemID and the new position > number.. If I pass -the first record shown above- along with a new position > of 4 - then items in position 2-3-4 will get moved to 1-2-3 and 1 will > become 4 - much like a bubble sort. > The reason I titled it a tricky query - is because I want to avoid using a > cursor (the worst from the performance standpoint) or by looping thru > executing a variety of select and updates (too many statements to be > executed there). > Sorry for the long winded message - I just want to try to convey the right > message, as I think I failed to do that right in the 1st message. thanks a > bunch for your time guys - I appreciate it tremendously! > Tom > > Please post DDL, so that people do not have to guess what the keys, > > constraints, Declarative Referential Integrity, datatypes, etc. in your > > schema are. > > >> I'm trying to create an efficient query or procedure to order records > > [sic] that may contain a child record [sic].. << > > If I understand what you are doing, why are orders and details in the > > same table? Why are you using GUIDs? This model does not feel right > > .. > > --CELKO-- > > * Sent via Developersdex http://www.developersdex.com * > > Don't just participate in USENET...get rewarded for it!
Wed, 20 Jul 2005 18:08:35 GMT

Page 1 of 1

[ 6 post ]