need help
-
Hi I need to write a single select query to do something which so far being done through an UDF.
I have one table InvoiceMaster
It has 2 columns InvoiceID int not null and ParentInvoiceID int null.
Every invoice has one invoiceid and one parentinvoiceid.ParentInvoiceid is the invoiceid of invoice through which it has been generated.The first invoice generated for a customer has parentinvoiceid as null.
A typical snap of this table can be like this:
InvoiceID ParentInvoiceID
2 null
6 null
7 2
11 7
15 6
25 11
27 15
what i want is that given a particular invoiceid is it possible to select all the invoiceids in its hierarchy through a single select quesry?
Say if we are given invoiceid 27 is it possible to get 15 ,6 thru query?
I would be greatly thankful for your efforts.
Thanks!!
-
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" -
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
-
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) -
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" -
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) -
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?
-
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??
-
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply