need help

Question

need help

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

Is there a maximum depth of levels?

If so, you can write the query with a lot of LEFT JOINs.

It not, no you can't do this with a single query in SQL Server 2000. Unless you write a table function, and select from the function.

Microsoft has an article about hierarchies. Please look it up.

N 56°04'39.16"
E 12°55'05.25"

sanjay-439180 Valued Member Points: 64 More actions · Answer 2

No there is no maximum level of depth.

Ive gone through Microsoft article and it seems theres no way to do it by a single query.

Thanks for your reply, bytheway

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

I agree... in SQL Server 2005, you could probably do it with a recursive CTE... But not in 2K. Might be able to do it using a recursive UDF, but recursion has a limit of 32 in SQL Server 2k.

I'm just curious, though... why would invoices have a hierarchical structure?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 4

If they are some kind of "suborders", it would be better to number them in sequence and still have "parent order" intact.

This would give you only two levels at any given time.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 5

Sure... I can see two levels like that... but N levels for invoices? The only way I can see something like that happening is in a WBS structure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

The only use I could see for that is to keep some sort of history. This order went out before that order, then that order...

But that would be usefull only when you can't use a date column or a sequential orderid.

Anyone can think of another scenario for this one?

sanjay-439180 Valued Member Points: 64 More actions · Answer 7

When a customer does a partial payment of an invoice we generate a second invoice for remaining amount.And if again he pays partially a third invoice is generated and process goes on this way until he had paid all the amount.That is why we need a hierarchical structure here.

Any better way??

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 8

All partial payments don't have to have previous payments as thier parents.

All of their parents can be the original invoice.

You still should be able to get what you need.