Is this SQL possible in LLBL, or is it re-writable so that it is? I could do it with a CASE statement in SQL, but AFAIK that can't be done with the framework.

The key is that the SUM columns in the select list need to be filtered by the current row's project_id. I can't see a way of doing this in LLBL.

Seems like this should be easy to rewrite by having multiple references to the expense_report_data table in the FROM clause, but I can't work it out somehow.

DECLARE
    @periodStartID  int,
    @periodEndId    int

SELECT
    @periodStartID = 109,
    @periodEndId = 111  

SELECT DISTINCT
    p.project_id,
    p.project_name,
    c.client_name,
    (SELECT SUM(Report_Amount) FROM Expense_Report_Data e WHERE Billed_To_Client = 1 and project_id = p.project_id),
    (SELECT SUM(Report_Amount) FROM Expense_Report_Data e WHERE Billed_To_Client = 0 and project_id = p.project_id)
FROM
    project_master p
    INNER JOIN expense_report_data e ON p.project_id = e.project_id 
    INNER JOIN client_master c ON c.client_id = p.client_id
e2.Billed_To_Client = 0)
WHERE
    e.Time_Period_Id BETWEEN @periodStartID AND @periodEndID
    and project_office_id = 1
ORDER BY
    c.client_name

Any help would be appreciated.

Thanks,

Phil

Hmm, I think I have it. I had a typo in the query when I tried to reference the expense table multiple times.

Still, I'd like to know if this is possible:

The key is that the SUM columns in the select list need to be filtered by the current row's project_id. I can't see a way of doing this in LLBL.

For this query I would either use a database view, Or implement IExpression (once for each sum column): http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Yes that's possible (in v2.0), using a ScalarQueryExpression object as the field expression in a dyn. list

Otis wrote:

Yes that's possible (in v2.0), using a ScalarQueryExpression object as the field expression in a dyn. list

Ahhhhhhh, that worked. I actually tried this before posting but gave up quickly as I thought it wasn't possible (I had the alias code wrong).

Any new thoughts on adding CASE statement support?

Thanks!

Phil

Won't add it for now.