Hi there at the forum,

I am trying (with no success ) to create the following query

First the Table

 CREATE TABLE [Booking] (
    [Position] [int] NOT NULL,
    [Subposition] [int] NOT NULL,
    [ArticelNr] [varchar] (15) NOT NULL,
    [Lot] [varchar] (15),
    [Type],
    [Input] [float],
    [Output] [float],
                [BookingDate][datetime]
                )

The table describes the outputs and the inputs related to a lot of an article.

I have the following query that I would like to program in my code. Is this possible:

SELECT Booking.ArticleNr, Booking.Lot, Booking.Input, Booking.Output FROM Booking 
INNER JOIN 
(   SELECT ArticleNr, Lot, SUM(Input) - SUM(Output) AS Saldo
    FROM Booking GROUP BY ArticleNr, Lot 
    HAVING SUM(Input) - SUM(Output) > 0 
    AND ArtikelNR = @ArticleNr
)
LotAvail ON
Booking.ArticleNR = LotAvail.ArticleNR AND
Booking.Lot = LotAvail.Lot
WHERE Booking.ArticelNR = @ArticleNr 

So what I need are all bookings were the sum of the inputs is larger than the sum of the outputs, for a certain article.

Now Of course I have an SP that performs this query, the problem is that I need to have LLBLGen objects to work with and not the DataTable that an SP call would return. As far as I know it is not possible to construct LLBLGen Objects out of a datatable.

Is it possible to perform such a query using LLBLGen constructs?

Thank you very much for any suggestions on this!

You need a subquery instead of the 'dynamic table' approach you're using now. So a subquery which delivers the data for the filter.

You can instantiate entities from a datatable though: first fetch the datatable, then get a reference to a factory for the entity (or instantiate a new entity each time directly in code), and use teh column name to index into the Fields object of the new entity, and set the value, using entity.Fields[name].ForcedCurrentValueWrite(value, value); you can also use entity.SetNewFieldValue() of course, but that will set IsDirty flags.

Dear Otis,

thank you for the reply!

The alternative with the data table sounds like a very interesting one for complex queries!

Best regards