Problem with group by query

Problem with group by query

Postby sam2 » Sat 10 Sep 2011 01:52

First, here is the desired SQL:
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 < 0 ?
                                            grp.Key.COMP_SEC_TYPE_CODE == "TBA" ?
                                                x.QUANTITY
                                            :
                                            ("LS").Contains(x.POSITION_IND) ?
                                                x.QUANTITY
                                            :
                                                0
                                        :
                                            0),


                                    OFace_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),





                                    OFace_Short = grp.Sum(x =>
                                        x.QUANTITY < 0 ?
                                            grp.Key.COMP_SEC_TYPE_CODE == "TBA" ?
                                                x.QUANTITY
                                            :
                                            ("LS").Contains(x.POSITION_IND) ?
                                                x.QUANTITY
                                            :
                                                0
                                        :
                                            0)

                                   

                                };

            List 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 CASE WHEN "Project3".COMP_SEC_TYPE_CODE = 'TBA' THEN "Extent9".QUANTITY WHEN (INSTR('LS', "Extent9".POSITION_IND)) > 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 CASE WHEN "Project1".COMP_SEC_TYPE_CODE = 'TBA' THEN "Extent5".QUANTITY WHEN (INSTR('LS', "Extent5".POSITION_IND)) > 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.
sam2
 
Posts: 9
Joined: Thu 01 Sep 2011 18:10

Postby sam2 » Mon 12 Sep 2011 21:56

Never mind. Looks like this is a linq feature.

The solution, for any poor soul who happens upon this, is to create a intermediate object containing all the columns you need to group by:

var rootQuery = from a in A join b in B select new { a, b}
var groupQery = from r in rootQuery group by new {a, b} into grp select {...}
sam2
 
Posts: 9
Joined: Thu 01 Sep 2011 18:10


Return to dotConnect for Oracle