I am using firebird and the following SQL statement does what I want it to do in firebird.
SELECT
COMPANY_GRP_MEMBER.NAME AS CompanyGroupMemberName,
SUM(CUBE_DEAL_ITEM_USER.USER_LINE_SALE_VALUE) AS myVal
FROM
CUBE_DEAL_ITEM_USER INNER JOIN
COMPANY ON CUBE_DEAL_ITEM_USER.COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
COMPANY_GRP_MEMBER_COMP_MAP ON COMPANY.COMPANY_ID = COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_ID INNER JOIN
COMPANY_GRP_MEMBER ON
COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_GRP_MEMBER_ID = COMPANY_GRP_MEMBER.COMPANY_GRP_MEMBER_ID
WHERE
(CUBE_DEAL_ITEM_USER.DELETED = 0) AND
(CUBE_DEAL_ITEM_USER.DEAL_STATE_ID = 200) AND
(COMPANY_GRP_MEMBER.COMPANY_GROUP_ID = 2) AND
( CUBE_DEAL_ITEM_USER.CUBE_DEAL_ITEM_USER_ID
IN
(
SELECT
CUBE_DEAL_ITEM_USER.CUBE_DEAL_ITEM_USER_ID
FROM
CUBE_DEAL_ITEM_USER INNER JOIN
COMPANY ON CUBE_DEAL_ITEM_USER.COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
COMPANY_GRP_MEMBER_COMP_MAP ON
COMPANY.COMPANY_ID = COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_ID
WHERE
(COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_GRP_MEMBER_ID = 12)))
GROUP BY
COMPANY_GRP_MEMBER.NAME
ORDER BY
SUM(CUBE_DEAL_ITEM_USER.USER_LINE_SALE_VALUE) DESC
I can do all of it easily in LLBL except for the IN part of the WHERE clause
( CUBE_DEAL_ITEM_USER.CUBE_DEAL_ITEM_USER_ID
IN
(
SELECT
CUBE_DEAL_ITEM_USER.CUBE_DEAL_ITEM_USER_ID
FROM
CUBE_DEAL_ITEM_USER INNER JOIN
COMPANY ON CUBE_DEAL_ITEM_USER.COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
COMPANY_GRP_MEMBER_COMP_MAP ON
COMPANY.COMPANY_ID = COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_ID
WHERE
(COMPANY_GRP_MEMBER_COMP_MAP.COMPANY_GRP_MEMBER_ID = 12)))
How do I do this using LLBL?
As you can see I am trying to do a group by on the CUBE_DEAL_ITEM_USER table however I want to only do the group by on those records that exist in my WHERE IN clause.
If this is possible I would love to know how as my only alternative is to create a temp table, copy the records that I want to group by into the temp table and then perform the sum on the temp table - messy.
Or, would the best approach be to put this SQL into a stored procedure, and use LLBL to run the stored procedure and return the datatable from the stored procedure - can I create a datable from a stored procedure execution using LLBL?
