Hello, First time poster! I'm fairly new to LLBL and have hit on a simple problem. Here is the original SQL:

 SELECT tbl1.Period, tbl1.PeriodID, tbl1.SortOrder 
                SQL += "FROM  ERC_HERITAGE_PERIOD AS tbl1, ERC_HERITAGE_PERIOD AS tbl2 
                SQL += "WHERE tbl1.PeriodID = tbl2.SortOrder  

So I'm trying to get this to work as below, however running into problems. Could some one please give me a steer as how to achieve this? Thanks, Alan

SortExpression sorter = new SortExpression(ErcHeritagePeriodFields.SortOrder | SortOperator.Ascending);
            RelationPredicateBucket B = new RelationPredicateBucket();
            IPredicateExpression A = new PredicateExpression();
            A.Add(ErcHeritagePeriodFields.PeriodId == ErcHeritagePeriodFields.SortOrder);
            B.PredicateExpression.Add(A);

            EntityCollection<ErcHeritagePeriodEntity> orgs = new EntityCollection<ErcHeritagePeriodEntity>();
            // fetch them using a DataAccessAdapter instance
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(orgs, B, 0, sorter);
            
            }

What kind of problems are you running in? Please be more specific, and read the forum guidelines.

And welcome to our forums .

Heres how the data looks (see below). The data is sorted in the correct order using the val in the 'SortOrder' column. So the SQL does this:

Reads the first number 91. fetches PeriodID 91 'Unknown' Reads 2nd number 68 - fetches where Period ID = 68 'prehistoric' Reads 3rd number 69 - fecthes where PeriodID = 69 - Antiquity

and so on and so on.

So thats what the SQL does and I'm trying to get LLBL to do the same thing? Thanks, Alan

PeriodID Period SortOrder 68 Prehistoric (Stone Age, Bronze age, etc) 91 69 Antiquity (Iron Age, Roman) 68 70 Middle & Dark Ages (5th century - 10th century) 69 71 Medieval (10th century - 15th century) 70 72 Early Modern (14th century - 18th century) 71 73 Modern & Industrial (Europe, 18th century - 20th century) 72 74 Victorian era (UK, 1837 - 1901) 95 75 Edwardian era (UK 1901 - 191 96 76 Interwar period (1918 - 1939) 84 77 World War II (1939 - 1945) 88 79 1940s 85 80 1950s 73 81 1960s 89 82 1970s 74 83 1980s 86 84 17th century 75 85 18th century 90 86 19th century 87 87 20th century 93 88 17th - 18th century 92 89 18th - 19th century 102 90 19th - 20th century 76 91 Unknown 77 92 21st century 79 93 20th - 21st century 80 94 17th - 20th century 81 95 15th century 82 96 16th century 83 102 World War I (1914-191 83 105 1990s 105

There is more than one thing here. First of all in the original sql you have posted, do all fields in the select list comes from the same table (instance), coz then I don't see how would the Period would be equal to the sortOrder.

If they SortOrder should come from the second instance of the table (should be joined be another alias), then you would have to use a DynamicList not an EntityCollection to fetch the resultSet.

In all cases when you join to the same table, you have to specify an alias for the joined instance at least, and you will need to use the same alias, when referring to fields from this specific instance whether in a filter or in a sort predicate.

Thanks for the feedback. Everything is contained in the same table. It just uses the entity SortOrder to store a value for each row. Running the SQL, you get the results below, sorted in order where the SortOrder number = the PeriodID.

I thought this might be straight forward, but the way its running at the moment it brings back '1990' because thats the only record where both numbers match! Thanks, Alan

[Period] [PeriodID] [SortOrder]  
Unknown 91  77
Prehistoric (Stone Age, Bronze age, etc)    68  91
Antiquity (Iron Age, Roman) 69  68
Middle & Dark Ages  (5th century - 10th century)    70  69
Medieval (10th century - 15th century)  71  70
Early Modern (14th century - 18th century)  72  71
15th century    95  82
16th century    96  83
17th century    84  75
17th - 18th century 88  92
18th century    85  90
Modern & Industrial (Europe, 18th century - 20th century)   73  72
18th - 19th century 89  102
Victorian era (UK, 1837 - 1901) 74  95
19th century    86  87
Edwardian era (UK 1901 - 1918)  75  96
19th - 20th century 90  76
20th century    87  93
20th - 21st century 93  80
21st century    92  79
World War I (1914-1918) 102 83
Interwar period (1918 - 1939)   76  84
World War II (1939 - 1945)  77  88
1940s   79  85
1950s   80  73
1960s   81  89
1970s   82  74
1980s   83  86
1980s   83  86
1990s   105 105

I'm not sure I'm following you, and I'm not sure you are following me too. In your Original SQL, you were joining to the same table, right?

Then you should follow up my previous post, on aliases.