Skip to Content

MIN function not working against virtual table

Hi folks,

When querying a very simple SQL from dev server to prod server via a virtual table it seems the MIN function is not working.  Are there limits to what functions can be used via virtual table or could it be due to the fact that our dev is rev 82 and our prod is rev 68?  For example the following should return a valid date (ie: in this case 20140803)

select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET"

                               where ebeln = '123456789'

                               group by ebeln, ebelp

Instead of a valid date (20140803) it returns the number 20.  Yet if I run this same exact SQL directly in the production server it returns the correct valid date 20140803.  Or if I remove the MIN function completely I get a list of 18 different dates for this same EBELN, EBELP purchase order and indeed I see the date 20140803 in the list.

Thanks,

-Patrick

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 05, 2015 at 09:17 PM

    Ok just to test my theory that it could be due to different rev's I tried a virtual connection from our dev (rev82) to stage (rev82) and not only does the MIN not work but I get an error; 

    '[129]: transaction rolled back by an internal error: exception 1000002: Allocation failed ; $size$=4918291432952823809; $name$=Pool/RowEngine/QueryExecution; $type$=pool; $inuse_count$=7; $allocated_size$=56896; $alignment$=16'

    If I remove the MIN function it works and I get a list of many dates....

    Add comment
    10|10000 characters needed characters exceeded

    • Ok the allocation error happens if the rows returned are not the same;  ie:

      select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET" where ebeln = '1000' group by ebeln, ebelp;

      EBELN    EBELP   EINDT

      1000   1         20140101

      1000   2         20140102

      RESULT = ALLOCATION ERROR

      If I choose an example where the first two fields are identical I don't get error but I get strange 20 result;

      select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET" where ebeln = '2000' group by ebeln, ebelp;

      EBELN    EBELP   EINDT

      2000   1         20140101

      2000   1         20140102

      EXPECTED RESULT = 20140101

      ACTUAL RESULT = 20

      If I do not use virtual table and perform the SQL directly against source table in source system I get correct result.

      PS: I get 20 no matter which value I query for EBELN.  This is same from rev82 virtual table from rev82 system and from rev68 virtual table to rev82 system.  The problem seems to be with using MIN on virtual table.