Max

Max

Postby PatrikAhlquist » Thu 08 May 2014 10:13

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

Image

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
Image
Where I can see that there is 4 groups of hylsplan with 3 hylsplan_bana

Regards,
Patrik
PatrikAhlquist
 
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Postby Shalex » Fri 09 May 2014 13:29

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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Max

Postby PatrikAhlquist » Fri 16 May 2014 13:10

Hi!

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

Image

Image

Regards,
Patrik
PatrikAhlquist
 
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Postby Kjell-Åke » Mon 19 May 2014 12:27

Same behaviour in 8.3.161. The produced SQL contains "...WHERE ROWNUM <= 1" which is causing the strange results.
Kjell-Åke
 
Posts: 1
Joined: Mon 19 May 2014 12:22

Re: Max

Postby Shalex » Mon 19 May 2014 15:11

Please send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Max

Postby PatrikAhlquist » Mon 19 May 2014 15:41

Hi!

I did so on friday last week.

Regards,
Patrik
PatrikAhlquist
 
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Postby Shalex » Tue 20 May 2014 11:03

Sorry, but we did not receive your test project. We have sent additional questions to the email specified in your forum profile.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Max

Postby PatrikAhlquist » Tue 20 May 2014 11:19

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
PatrikAhlquist
 
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Postby Shalex » Tue 20 May 2014 14:46

Thank you for your report. We have reproduced the issue and are investigating it.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Max

Postby Shalex » Thu 29 May 2014 14:23

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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle