What's SQL Server version? If it's SQL 2000 or earlier than it cannot be done with view for sure.

>I have 2 invoice transaction files (header and detail). Once the transaction has been marked as posted, you can not edit it. But you can perform a "distribution change" on the original invoice. To change the account code it goes against for example. Each row can have one dist change against it. But a dist change can have another dist change performed against it.
>
>The way the data model works is that each row in the detail table has 2 id columns, detail_id and ref_id and a flag column, dist_change.
>
>An invoice with no dist change against it will look like:
>detail_id: 100
>ref_id: null
>dist_change: Y
>
>An invoice with a dist change, and the dist change row:
>invoice: detail_id: 101
>ref_id: null
>dist_change: N
>
>dist change row: detail_id: 102
>ref_id: 101
>dist_change: Y
>
>So the ref_id of the dist change refers to the detail_id of the original invoice line. If a dist change was performed against this dist change, it's ref_id would have 102, and the dist_change flag of the first dist_change would get set to N, meaning no more dist change allowed for this row.
>
>For 1099 tax purposes, there is one more step. First only rows with a dist change of "Y" are selected. Next, the transaction table is related into the checks table by detail_id to only get transactions that have a row in the checks table (they are paid).
>
>Is it possible to create a view that can traverse the tree. So when it encounters a dist change row, it can find the detail_id of the invoice it applies to, using that id to join to the checks table?
>
>Someone gave me a Stored Proc I was able to use for this, but this is for a Java program that expects a view. So I will have to change the program to use the new Stored Proc if I can't use a view.
>
>Thanks.