tricky query? 
Author Message
 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
 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
 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
 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
 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.

- 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

- 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
 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
 
 [ 6 post ] 

 Relevant Pages 

1. Tricky query

2. tricky query (for me atleast)

3. Tricky Query

4. Tricky query (for me anyways)

5. Tricky Query

6. tricky query

7. Tricky query question

8. Tricky Query Problem

9. Tricky Query help

10. Tricky Query

11. Tricky query

12. tricky query


 
Powered by phpBB® Forum Software