Hello there,
Happy New Year !
I was wondering if there are any SQL guru's out there ... :-)
We are currently moving our Ax from Oracle to SQL2005. I have encountered a
piece of code that reads a table and generate a "from-to" list instead based
on intervals.
Example:
This table holds 17 records:
ITEMID POSITION PREFIX
POSNUMBER
------------------------- ------------------------------ ----------
----------
VS60001558 P2 P
2
VS60001558 P4 P
4
VS60001558 P5 P
5
VS60001558 P7 P
7
VS60001558 P9 P
9
VS60001558 P10 P
10
VS60001558 P11 P
11
VS60001558 P14 P
14
VS60001558 P15 P
15
VS60001558 P16 P
16
VS60001558 P17 P
17
VS60001558 P18 P
18
VS60001558 P19 P
19
VS60001558 P20 P
20
VS60001558 P21 P
21
VS60001558 P22 P
22
VS60001558 P23 P
23
After manipulating through code, this is output (stored in a temp table):
PREFIX MIN(X.POSNUMBER) MAX(X.POSNUMBER)
---------- ---------------- ----------------
P 2 2
P 4 5
P 7 7
P 9 11
P 14 23
As you can see, the intervals are grouped. The "Prefix" can be any
character.
The code that actually does the job is Oracle-specific and does not work on
SQL2005.
This is the procedure:
public void FillTmpBOMPosition()
{
ResultSet SQLAnswer;
Notes sqlStr;
Connection con = new Connection();
Statement sqlStmt = Con.createStatement();
TmpBOMPosition _TmpBOMPosition; // This is a temp table
str prefix;
// * arranges positions in groups
sqlstr = strfmt("SELECT x.prefix, min(x.posnumber),
max(x.posnumber)\n"+
" FROM (SELECT BOMPositionTable.prefix\n"+
" , BOMPositionTable.posnumber\n"+
" , ROW_NUMBER()\n"+
" OVER (PARTITION BY
BOMPositionTable.prefix\n"+
" ORDER BY
BOMPositionTable.posnumber) r\n"+
" FROM BOMPositionTable\n"+
" WHERE DataAreaId = '" +CurExt()+"'\n"+
" AND RefRecID = '"
+Num2str(_BOM.RecId,0,0,0,0)+"'\n"+
" ) x\n"+
" GROUP BY x.prefix\n"+
" , x.posnumber - x.r\n"+
" ORDER BY x.prefix\n"+
" , min(x.posnumber)");
SQLAnswer = sqlStmt.executeQuery(sqlStr);
ttsbegin;
while (SQLAnswer.next())
{
_TmpBOMPosition.Userid = Curuserid();
_TmpBOMPosition.BOMId = _BOM.BOMId;
_TmpBOMPosition.ItemId = _BOM.ItemId;
_TmpBOMPosition.Linenum = _BOM.LineNum;
_TmpBOMPosition.RefRecId = _BOM.RecId;
_TmpBOMPosition.Prefix = SQLAnswer.getString(1);
_TmpBOMPosition.FromNumber = SQLAnswer.getint(2);
_TmpBOMPosition.ToNumber = SQLAnswer.getint(3);
_TmpBOMPosition.insert();
}
ttscommit;
}
When run, SQL complains about the "ROW_NUMBER" part that is not recognized;
probably other things as well.
My question is: Any pointers as to 1) change the above code in order to run
on SQL or 2) change the above code to do the job entirely in X++ ?
Thanks in advance.
/Jens
