Error loading grid
Massimo Sommaruga
Hi,I've tried to fill a grid in a new folder in the production order form;
everything works fine if the grid is based on a simple query, but when I run the following query:
SET NOCOUNT ON
DECLARE
@lvl int,
@line varchar(200),
@qt numeric(19,6),
@qtot numeric(19,6),
@sortcol nvarchar(100),
@current varchar(20)
SET @current='GM250484'
CREATE TABLE #stack (item varchar(200) COLLATE database_default, lvl int, quantity numeric(19,6), sortcol nvarchar(100))
CREATE TABLE #tree (item varchar(200) COLLATE database_default, lvl int, quantity numeric(19,6), sortcol nvarchar(100))
INSERT INTO #stack VALUES (@current, 1,1, Convert(nvarchar(100),'0'))
SET @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item,
@qt = quantity,
@sortcol= sortcol
FROM #stack
WHERE lvl = @lvl
SELECT @line = @current,
@qtot= @qt
INSERT INTO #Tree VALUES (@line, @lvl,@qtot,@sortcol)
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
AND sortcol=@sortcol
INSERT #stack
SELECT
Db.code,
@lvl + 1,
Db.quantity,
Convert(nvarchar(100),@sortcol+cast(1000+ ChildNum as nvarchar(90)))
FROM ITT1 Db INNER JOIN
OITM P ON Db.Code=P.ItemCode
WHERE Db.father = @current and P.PrcrmntMtd='M' and IsNull(P.MinLevel,0)>0
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END
SELECT
T.item as ItemCode,
T.lvl-1 as Livello,
P.ItemName AS Descrizione,
P.InvntryUom as UM,
T.Quantity,
IsNull(P.MinLevel,0) as MinStock,
P.OnHand as Giac,
P.IsCommited as Impegnato,
P.OnOrder as Ordinato,
(P.OnHand - P.IsCommited + P.OnOrder) as Disponibile,
(case when T.quantity>(P.OnHand - P.IsCommited + P.OnOrder) then '!Attenzione!' else 'OK' end) as Warning,
Isnull(P.CardCode,'') as 'Fornitore',
(case when P.PrcrmntMtd='M' then 'Produrre' else 'Acquistare' end) as Tipo
FROM
#tree T INNER JOIN
OITM P ON T.item =P.ItemCode
ORDER BY T.sortcol
DROP TABLE #tree
DROP TABLE #stack
the grid isn't filled....
I'm working on a system with SBO2005A SP01 PL39 and MS SQL Server 2000, coresuite framework 2.50 and customize 1.53
Also in the Query Analyzer the query works fine (so it's not an SQL error).
In the coresuite debug log file I found exceptions like:
"customize C# rule 00000002 runtime error: The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) (Inner)-Trace: at SAPbouiCOM.DataTableClass.ExecuteQuery(String Query) at SwissAddonFramework.UI.Components.GridSAP.ExecuteQuery(String query) at SwissAddonFramework.UI.Components.Grid.ExecuteQuery(String query) at COR_Utility.Helper.Run_FO_COR_CUS_00000002(CustomizeEvent pVal)"
or
"Error occured in Item eventSystem.Runtime.InteropServices.COMException (0x80010105): The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) at SAPbouiCOM.DataTableClass.ExecuteQuery(String Query) at SwissAddonFramework.UI.Components.GridSAP.ExecuteQuery(String query) at SwissAddonFramework.UI.Components.Grid.ExecuteQuery(String query) at COR_Utility.Helper.<>c__DisplayClass1.b__0(Click eventVal) at SwissAddonFramework.UI.EventHandling.ItemEvents.ClickContainer.Execute(Event e) at SwissAddonFramework.UI.EventHandling.Global.Executer.Execute(Event e) at SwissAddonFramework.UI.EventHandling.ItemEvents.ItemEventBase.HandleEvent(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) at SwissAddonFramework.Global.ItemEventHandler.Invoke(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) at SwissAddonFramework.Global.CatchItemEvent(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) on FormF_50"
What could be the cause?
SBO version? SQL Server version? COR customize?
Thanks in advance.
Regards.
PS: on a different system my rule works in the right way.
Manuel Marhold
Hi Massimo,I think SBO doen't work with this query.
You can created a stored function on your sql-server with this query and run "SELECT * FROM dbo.storedFunctioName(parameter...)" to load the query and fill the grid.
0
Please sign in to leave a comment.
Comments
0 comments