Database

public class MyEntity {<br />
    int id; <br />
     int pid;            // id of the parent entity<br />
     ArrayList children;  // list of children<br />
     String name;<br />
     // other fields<br />
}

CLASS MyEntity TABLE MYENTITY<br />
      PRIMARY_KEY id<br />
      RELATIONSHIP children REFERENCES ChildrenCollection WITH id<br />
;<br />
COLLECTION_CLASS ChildrenCollection COLLECTION_TYPE ARRAYLIST ELEMENT_TYPE MyEntity<br />
     PRIMARY_KEY pid<br />
;

<br />
   String predicate = ....;  // WHERE clause for the top-level objects<br />
   ArrayList entities = njdx.query("MyEntity", predicate, ...);

PKey    Name                                    PKeyOfParent
------------------------------------------------------------
0       Root                                    NULL 
1       Child1                                  0
2       Child2                                  0
3       A GrandChild                            1
4       Another GrandChild                      2
5	A Great GrandChild                      3
6	A Great Great GrandChild                5
7 	One More GrandChild                     3

CREATE FUNCTION GetDescendants (@nID int)  
RETURNS @tDescReturn table (PID int)
AS  
begin
DECLARE @RowsAdded int
declare @tDesc   TABLE (PKey int,PKeyOfParent int, processed tinyint default 0) 

INSERT @tDesc
   SELECT PKey,PKeyOfParent,0
   FROM SelfRefTable 
   WHERE PKey = @nID 

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
   BEGIN
	UPDATE @tDesc
      		SET processed = 1
      		WHERE processed = 0

	INSERT @tDesc
	      SELECT e.PKey, e.PKeyOfParent,0
	      FROM SelfRefTable e, @tDesc r
	      WHERE e.PKeyOfParent =r.PKey and r.processed = 1

      UPDATE @tDesc
	      SET processed = 2
	      WHERE processed = 1

      SET @RowsAdded = @@rowcount

   END

 INSERT @tDescReturn
   SELECT PKey 
   FROM @tDesc
return 
end

CREATE FUNCTION GetAncestors (@nID int)  
RETURNS @tAscReturn table (PID int)
AS  
begin
DECLARE @RowsAdded int
declare @tAsc   TABLE (PKey int,PKeyOfParent int, processed tinyint default 0) 

INSERT @tAsc
   SELECT PKey,PKeyOfParent,0
   FROM SelfRefTable 
   WHERE PKey = @nID 

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
   BEGIN
	UPDATE @tAsc
      		SET processed = 1
      		WHERE processed = 0

	INSERT @tAsc
	      SELECT e.PKey, e.PKeyOfParent,0
	      FROM SelfRefTable e, @tAsc r
	      WHERE e.PKey = r.PKeyOfParent  and r.processed = 1
	
      	UPDATE @tAsc
	      SET processed = 2
	      WHERE processed = 1

	SET @RowsAdded = @@rowcount
   END

 INSERT @tAscReturn
   SELECT PKey 
   FROM @tAsc
return 
end

select *,case when parent_id is not null 
            then parent_id - .5 
            else id end as OrderByField
from x 
order by OrderByField

Brady Kelly wrote:

Why doesn't it complain earlier, and why does it choose the least efficient conversion, if any converison at all?

psasidisrcum wrote:

"SELECT dbo_Table_1.* FROM dbo_Table_1

Jerry Hammond wrote:

I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1?

Jerry Hammond wrote:

You also have a period after the first dbp_Table_1 which may be causing fits.

Colin Angus Mackay wrote:

He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.

Colin Angus Mackay wrote:

The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is:
[server].[database].[owner].[table].[column]

Jerry Hammond wrote:

Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?

psasidisrcum wrote:

Dim cnnSQL As New ADODB.Connection

Set cnnSQL = New ADODB.Connection

psasidisrcum wrote:

dataRS.Open "SELECT * FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic