Max

[PatrikAhlquist]

Using latest version of dotConnect for Oracle (8.3.146) and Entity Framework 6.1.



maxHylsplanBana should be (and was in earlier versions) 3, not 1.
Hylsplan contains 4 collections of 3 Hylsplan_Bana.

hylsplan.Max generates this SQL:

Code: Select all

SELECT 
"GroupBy2".A1 AS C1
FROM ( SELECT Max((SELECT Count(1) AS A1
		FROM PL.HYLSPLAN_BANA "Extent2"
		WHERE ("Extent1".ID = "Extent2".HYLSPLAN_ID) AND ROWNUM <= 1)) AS A1
	FROM PL.HYLSPLAN "Extent1"
	WHERE ("Extent1".STATUS = 'N') AND ("Extent1".MASKIN_NR = 1)
)  "GroupBy2";

Which gives the result

C1
1

hylsplan.ToList generates this:

Code: Select all

SELECT 
"Project2".C2 AS C1,
"Project2".ID,
"Project2".STATUS,
"Project2".ORDNINGS_NR,
"Project2".KORORDER_NR,
"Project2".KNIVSTALLNING_NR,
"Project2".HYLSKOD,
"Project2".PLANERAT_ANTAL_SET,
"Project2".PRODUCERAT_ANTAL_SET,
"Project2".C3 AS C2,
"Project2".TOLERANS,
"Project2".LANGDJUSTERING,
"Project2".UPD_DATUM,
"Project2".C5 AS C3,
"Project2".MASKIN_NR,
"Project2".HYLSPLAN_NR,
"Project2".BESTALLARE,
"Project2".UTGANGSHYLSA,
"Project2".SKP_DATUM,
"Project2".HYLSKAP_STATUS,
"Project2".C4,
"Project2".HYLSPLAN_ID,
"Project2".BANA_NR,
"Project2".LANGD,
"Project2".PLUGG,
"Project2".SKP_DATUM1,
"Project2".UPD_DATUM1
FROM ( SELECT 
	CASE WHEN "Project1".STATUS = 'P' THEN 1 ELSE 2 END AS C1,
	"Project1".ID,
	"Project1".MASKIN_NR,
	"Project1".HYLSPLAN_NR,
	"Project1".STATUS,
	"Project1".BESTALLARE,
	"Project1".KORORDER_NR,
	"Project1".KNIVSTALLNING_NR,
	"Project1".HYLSKOD,
	"Project1".UTGANGSHYLSA,
	"Project1".TOLERANS,
	"Project1".LANGDJUSTERING,
	"Project1".PLANERAT_ANTAL_SET,
	"Project1".PRODUCERAT_ANTAL_SET,
	"Project1".SKP_DATUM,
	"Project1".UPD_DATUM,
	"Project1".ORDNINGS_NR,
	"Project1".HYLSKAP_STATUS,
	1 AS C2,
	"Project1".PLANERAT_ANTAL_SET - "Project1".PRODUCERAT_ANTAL_SET AS C3,
	"Extent3".HYLSPLAN_ID,
	"Extent3".BANA_NR,
	"Extent3".LANGD,
	"Extent3".PLUGG,
	"Extent3".SKP_DATUM AS SKP_DATUM1,
	"Extent3".UPD_DATUM AS UPD_DATUM1,
	CASE WHEN "Extent3".HYLSPLAN_ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C4,
	"Project1".C1 AS C5
	FROM   (SELECT 
		"Extent1".ID,
		"Extent1".MASKIN_NR,
		"Extent1".HYLSPLAN_NR,
		"Extent1".STATUS,
		"Extent1".BESTALLARE,
		"Extent1".KORORDER_NR,
		"Extent1".KNIVSTALLNING_NR,
		"Extent1".HYLSKOD,
		"Extent1".UTGANGSHYLSA,
		"Extent1".TOLERANS,
		"Extent1".LANGDJUSTERING,
		"Extent1".PLANERAT_ANTAL_SET,
		"Extent1".PRODUCERAT_ANTAL_SET,
		"Extent1".SKP_DATUM,
		"Extent1".UPD_DATUM,
		"Extent1".ORDNINGS_NR,
		"Extent1".HYLSKAP_STATUS,
		(SELECT Sum("Extent2".LANGD) AS A1
FROM PL.HYLSPLAN_BANA "Extent2"
WHERE ("Extent1".ID = "Extent2".HYLSPLAN_ID) AND ROWNUM <= 1) AS C1
		FROM PL.HYLSPLAN "Extent1"
		WHERE ("Extent1".STATUS = 'N') AND ("Extent1".MASKIN_NR = 1) ) "Project1"
	LEFT OUTER JOIN PL.HYLSPLAN_BANA "Extent3" ON "Project1".ID = "Extent3".HYLSPLAN_ID
)  "Project2"
ORDER BY "Project2".C1 ASC, "Project2".ORDNINGS_NR ASC, "Project2".ID ASC, "Project2".C4 ASC;

Whick gives the result

Where I can see that there is 4 groups of hylsplan with 3 hylsplan_bana

Regards,
Patrik

[Shalex]

1. Please send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.
2. Specify the exact previous build (x.x.x) of dotConnect for Oracle which set maxHylsplanBana to 3.

[PatrikAhlquist]

Hi!

I will do.
Here's the result of my test.
Previously we used version 7.3.132 in which this worked.





Regards,
Patrik

[Kjell-Åke]

Same behaviour in 8.3.161. The produced SQL contains "...WHERE ROWNUM <= 1" which is causing the strange results.

[Shalex]

Please send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.

[PatrikAhlquist]

Hi!

I did so on friday last week.

Regards,
Patrik

[Shalex]

Sorry, but we did not receive your test project. We have sent additional questions to the email specified in your forum profile.

[PatrikAhlquist]

Now I've uploaded it again, attached it in a reply to your email and put it here: http://www.sendspace.com/file/cozl3u

That should do it!

Regards,
Patrik

[Shalex]

Thank you for your report. We have reproduced the issue and are investigating it.

[Shalex]

The bug with generating extra condition for limiting subselect by one record in generated subqueries with grouping is fixed in the new (8.4) version of dotConnect for Oracle.

It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.