Question for isNull again.

I wanna get data from two tables, PurchaseOrderDetail n PurchaseOrder, by using DataTable. I dont knw how to hande the isNull (SQL) function in LLBLGen, e.g: isNull(Qty, 0), return 0 to the TableFileds.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1760 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I hv visited the above links, but...... Can I hv another way to do so?

//-----

Code Segment:

C#: ResultsetFields purchaseOrderTableFields = new ResultsetFields(10); purchaseOrderTableFields.DefineField(PurchaseOrderDetailFields.OrderNo, 0); purchaseOrderTableFields.DefineField(PurchaseOrderFields.VendorNo, 1 ); ...... purchaseOrderTableFields.DefineField(PurchaseOrderDetailFields.QtyReceived, 7); purchaseOrderTableFields.DefineField(PurchaseOrderDetailFields.ItemNo, 8, "QtyBalance"); purchaseOrderTableFields[8].ExpressionToApply = (PurchaseOrderDetailFields.Qty - PurchaseOrderDetailFields.QtyReceived); ......

SQL: SELECT isNull(QtyReceived, 0) AS 'QtyReceived', isNull(Qty,0) - isNull(QtyReceived,0) AS 'QtyBalance' ...... FROM ...... //---- adapter designer: 2.0.0.0 Beta June 8th, 2006 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.0.0.60609

V2 supports DB Function calls.

I'll give an example:

So you should do: instead of:

purchaseOrderTableFields.DefineField(PurchaseOrderDetailFields.ItemNo, 8, "QtyBalance");
purchaseOrderTableFields[8].ExpressionToApply = (PurchaseOrderDetailFields.Qty - PurchaseOrderDetailFields.QtyReceived);

you should do:

purchaseOrderTableFields.DefineField(
    new EntityField2("QtyBalance", new DBFunctionCall("ISNULL", 
        new object[] { (PurchaseOrderDetailFields.Qty - PurchaseOrderDetailFields.QtyReceived), 0 })), 8, "QtyBalance");

This creates a new EntityField2 field, which is an Expression field, the expression is a DBFunctionCall to ISNULL, which has 2 parameters: an expression and a value: 0.

Now, with ISNULL, you shouldn't do this in the DB in most cases, especially in the QtyReceived case at position 7, as it's a gui related matter, which should be solved there, not in the DB. Though above shows how you can call a DB function, like ISNULL. I typed it from my bare head, so I could have made a small detail mistake here and there.

Thank You for your reply, but my problem cannot be solved.

SELECT
    isNull(QtyReceived, 0) AS 'QtyReceived',
[b]isNull(Qty,0) - isNull(QtyReceived,0)[/b] AS 'QtyBalance'
......
FROM

purchaseOrderTableFields.DefineField(
    new EntityField2("QtyBalance", new DBFunctionCall("ISNULL", 
        new object[] { ([b]PurchaseOrderDetailFields.Qty - PurchaseOrderDetailFields.QtyReceived[/b]), 0 })), 8, "QtyBalance");

IsNull is used for Qty AND QtyReceived.

DbFunctionCall dbFunQty = new DbFunctionCall("ISNULL", new object[] { (PurchaseOrderDetailFields.Qty ), 0 });
EntityField2 eQty = new EntityField2("Qty", dbFunQty);
purchaseOrderTableFields.DefineField(eQty, 6,"Qty");

DbFunctionCall dbFunQtyReceived = new DbFunctionCall("ISNULL", new object[] { (PurchaseOrderDetailFields.QtyReceived), 0 });
EntityField2 eQtyReceived = new EntityField2("QtyReceived", dbFunQtyReceived);
purchaseOrderTableFields.DefineField(eQtyReceived, 7,"QtyReceived");

purchaseOrderTableFields[8].CurrentValue = (decimal)purchaseOrderTableFields[6].CurrentValue - (decimal)purchaseOrderTableFields[7].CurrentValue;

All the CurrentValues are NULL. How to calc the QtyBalacne, col: [8]?

//=========================== SOLVED! Otis, do you hv any other soln?

// Add Col:QtyBalance 
            purchaseOrderTable.Columns.Add("QtyBalance", typeof(decimal)); // col[9]
            purchaseOrderTable.Columns[9].Expression = "Qty - QtyReceived";

QtyBalance is an expression of 2 expressions. A DBFunctionCall is an expression so you can simply create an expression of two DbFunctionCalls substracted from eachother.