Code: Select all
select
pos.cusip,
pos.acct_no,
core.comp_sec_type_code,
sum(usd_mkt_value) as usd_mkt_value,
sum(usd_accrued_int) as usd_accrued_int,
sum(case when core.comp_sec_type_code = 'TBA' and pos.position_ind = 'L' then pos.quantity else 0 end) as qtylong,
sum(case when core.comp_sec_type_code = 'TBA' and pos.position_ind = 'S' then pos.quantity else 0 end) as qtyshort
from PIMCO_OWN.spm_position pos
join TAPS_OWN.ssm_core core on pos.cusip = core.ssm_id
where pos.cusip in ('0000575F2' , '0000579A9')
group by pos.cusip, pos.acct_no, core.comp_sec_type_code
Here is my LINQ:
Code: Select all
var positionQuery = from pos in Context.SPM_POSITIONs
join core in Context.SSM_COREs on pos.CUSIP equals core.SSM_ID
group pos by new { CUSIP = pos.CUSIP, ACCT_NO = pos.ACCT_NO, FIRM_NO = pos.FIRM_NO, COMP_SEC_TYPE_CODE = core.COMP_SEC_TYPE_CODE} into grp
select new Position
{
Firm_No = grp.Key.FIRM_NO,
Acct_No = grp.Key.ACCT_NO,
CUSIP = grp.Key.CUSIP,
Broker_ID = null, // Not used
USD_Mkt_Value = grp.Sum(x => x.USD_MKT_VALUE),
USD_Accrued_Int = grp.Sum(x => x.USD_ACCRUED_INT),
Qty_Long = grp.Sum(x =>
x.QUANTITY > 0 ?
grp.Key.COMP_SEC_TYPE_CODE == "TBA" ?
x.QUANTITY
:
("LK").Contains(x.POSITION_IND) ?
x.QUANTITY
:
0
:
0),
Qty_Short = grp.Sum(x =>
x.QUANTITY
x.QUANTITY > 0 ?
grp.Key.COMP_SEC_TYPE_CODE == "TBA" ?
x.QUANTITY
:
("LK").Contains(x.POSITION_IND) ?
x.QUANTITY
:
0
:
0),
OFace_Short = grp.Sum(x =>
x.QUANTITY lst = positionQuery.Where( x => x.CUSIP == "0000575F2" || x.CUSIP == "0000579A9").ToList();
Here is the generated SQL
Code: Select all
SELECT
1 AS C1,
"Project4".FIRM_NO AS FIRM_NO,
"Project4".ACCT_NO AS ACCT_NO,
"Project4".CUSIP AS CUSIP,
TO_CHAR(NULL) AS C2,
"Project4".C1 AS C3,
"Project4".C2 AS C4,
"Project4".C3 AS C5,
"Project4".C4 AS C6,
"Project4".C5 AS C7,
"Project4".C6 AS C8
FROM ( SELECT
"Project3".C1 AS C1,
"Project3".C2 AS C2,
"Project3".FIRM_NO AS FIRM_NO,
"Project3".ACCT_NO AS ACCT_NO,
"Project3".CUSIP AS CUSIP,
"Project3".C3 AS C3,
"Project3".C4 AS C4,
"Project3".C5 AS C5,
(SELECT Sum(CASE WHEN "Extent9".QUANTITY 0 THEN "Extent9".QUANTITY ELSE 0 END ELSE 0 END) AS A1
FROM PIMCO_OWN.SPM_POSITION "Extent9"
INNER JOIN TAPS_OWN.SSM_CORE "Extent10" ON "Extent9".CUSIP = "Extent10".SSM_ID
WHERE ((("Project3".CUSIP = "Extent9".CUSIP) AND ("Project3".ACCT_NO = "Extent9".ACCT_NO)) AND ("Project3".FIRM_NO = "Extent9".FIRM_NO)) AND ("Project3".COMP_SEC_TYPE_CODE = "Extent10".COMP_SEC_TYPE_CODE)) AS C6
FROM ( SELECT
"Project2".C1 AS C1,
"Project2".C2 AS C2,
"Project2".FIRM_NO AS FIRM_NO,
"Project2".ACCT_NO AS ACCT_NO,
"Project2".CUSIP AS CUSIP,
"Project2".COMP_SEC_TYPE_CODE AS COMP_SEC_TYPE_CODE,
"Project2".C3 AS C3,
"Project2".C4 AS C4,
(SELECT Sum(CASE WHEN "Extent7".QUANTITY > 0 THEN CASE WHEN "Project2".COMP_SEC_TYPE_CODE = 'TBA' THEN "Extent7".QUANTITY WHEN (INSTR('LK', "Extent7".POSITION_IND)) > 0 THEN "Extent7".QUANTITY ELSE 0 END ELSE 0 END) AS A1
FROM PIMCO_OWN.SPM_POSITION "Extent7"
INNER JOIN TAPS_OWN.SSM_CORE "Extent8" ON "Extent7".CUSIP = "Extent8".SSM_ID
WHERE ((("Project2".CUSIP = "Extent7".CUSIP) AND ("Project2".ACCT_NO = "Extent7".ACCT_NO)) AND ("Project2".FIRM_NO = "Extent7".FIRM_NO)) AND ("Project2".COMP_SEC_TYPE_CODE = "Extent8".COMP_SEC_TYPE_CODE)) AS C5
FROM ( SELECT
"Project1".C1 AS C1,
"Project1".C2 AS C2,
"Project1".FIRM_NO AS FIRM_NO,
"Project1".ACCT_NO AS ACCT_NO,
"Project1".CUSIP AS CUSIP,
"Project1".COMP_SEC_TYPE_CODE AS COMP_SEC_TYPE_CODE,
"Project1".C3 AS C3,
(SELECT Sum(CASE WHEN "Extent5".QUANTITY 0 THEN "Extent5".QUANTITY ELSE 0 END ELSE 0 END) AS A1
FROM PIMCO_OWN.SPM_POSITION "Extent5"
INNER JOIN TAPS_OWN.SSM_CORE "Extent6" ON "Extent5".CUSIP = "Extent6".SSM_ID
WHERE ((("Project1".CUSIP = "Extent5".CUSIP) AND ("Project1".ACCT_NO = "Extent5".ACCT_NO)) AND ("Project1".FIRM_NO = "Extent5".FIRM_NO)) AND ("Project1".COMP_SEC_TYPE_CODE = "Extent6".COMP_SEC_TYPE_CODE)) AS C4
FROM ( SELECT
"GroupBy1".A1 AS C1,
"GroupBy1".A2 AS C2,
"GroupBy1".K1 AS FIRM_NO,
"GroupBy1".K2 AS ACCT_NO,
"GroupBy1".K3 AS CUSIP,
"GroupBy1".K4 AS COMP_SEC_TYPE_CODE,
(SELECT Sum(CASE WHEN "Extent3".QUANTITY > 0 THEN CASE WHEN "GroupBy1".K4 = 'TBA' THEN "Extent3".QUANTITY WHEN (INSTR('LK', "Extent3".POSITION_IND)) > 0 THEN "Extent3".QUANTITY ELSE 0 END ELSE 0 END) AS A1
FROM PIMCO_OWN.SPM_POSITION "Extent3"
INNER JOIN TAPS_OWN.SSM_CORE "Extent4" ON "Extent3".CUSIP = "Extent4".SSM_ID
WHERE ((("GroupBy1".K3 = "Extent3".CUSIP) AND ("GroupBy1".K2 = "Extent3".ACCT_NO)) AND ("GroupBy1".K1 = "Extent3".FIRM_NO)) AND ("GroupBy1".K4 = "Extent4".COMP_SEC_TYPE_CODE)) AS C3
FROM ( SELECT "Extent1".FIRM_NO AS K1, "Extent1".ACCT_NO AS K2, "Extent1".CUSIP AS K3, "Extent2".COMP_SEC_TYPE_CODE AS K4, Sum("Extent1".USD_MKT_VALUE) AS A1, Sum("Extent1".USD_ACCRUED_INT) AS A2
FROM PIMCO_OWN.SPM_POSITION "Extent1"
INNER JOIN TAPS_OWN.SSM_CORE "Extent2" ON "Extent1".CUSIP = "Extent2".SSM_ID
GROUP BY "Extent1".FIRM_NO, "Extent1".ACCT_NO, "Extent1".CUSIP, "Extent2".COMP_SEC_TYPE_CODE
) "GroupBy1"
) "Project1"
) "Project2"
) "Project3"
) "Project4"
It looks like each additional summed column adds another level of selects. So if I had Coulumn1 = sum(x).... Column30 = sum(x) I would get SELECT FROM ( SELECT FROM ( SELECT FROM...)) etc. approx 30 levels deep.
The sum(case...) in my desired sql is slightly different from the linq statement its just an example that shows I'm using a column in joined table as a qualifier.