Problem with group by query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
sam2
Posts: 9
Joined: Thu 01 Sep 2011 18:10

Problem with group by query

Post by 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 
                                        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.

sam2
Posts: 9
Joined: Thu 01 Sep 2011 18:10

Post by 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 {...}

Post Reply