on 02-13-2008 7:27 AM
Hi all,
I want a user matrix to load data when I hit a button and the data to be filled is the output of an sql query. binding the matrix columns with table columns and then load the matrix with DBDatasource attached to that table will not do in this case as the columns in the sql query are from several different tables or views.
writing following line to do this takes several minutes to load the data:
For j = 0 To Reordset.RecordCount - 1
Matrix.AddRow()
Matrix.Columns.Item("col1").Cells.Item(i).Specific.value = Recordset.Fields.Item("cardcode").Value
Recordset.movenext()
next
Is there any other way to fill the matrix in this case, which loads the data faster?
Regards,
HI Roland,
if u can remember, u had asked for some SQL rowcounter function so that u can fetch the row number in the sql query itself.
following is the function (row_number() over (order by NameOfColumn)
select row_number() over (order by docentry) as rowcount, docentry,docnum,..... from ...
hope it helps. (I know is too late, but I just happend to knnw it now )
regards,
Binita
'rowcount' is a keyword so use some other alias for that column.
Edited by: Binita Joshi on Apr 2, 2008 12:37 PM
Sorry Roland.one bad news is, row_number does not work in executequery of datatable(even if u write it in view and fetch it from there).u wont catch any exception but it wont simply print data.
Edited by: Binita Joshi on Apr 2, 2008 12:55 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Binita,
thank you very much for that hint!!
AND:
I've found a way to use it with DataTable.
The solution is to use a CAST for the ROW_NUMBER-Column:
SELECT
CAST(
ROW_NUMBER() OVER (ORDER BY T1.U_LogNo ASC) AS INT
) AS RowCnt,
T1.Code,
T1.U_ParcelNo,
T1.U_LogNo
FROM
[@XXX_EXAMPLE] T1
WHERE
T1.U_ParcelNo='132006'
ORDER BY
T1.U_LogNo ASC
And the CAST leads me back to your problem (some thoughts):
First: I think your UserDataSource must be of the same type as the DB-Table column. Why is your DB-Table column not of a defined type when it's created with SAP or SDK? Is it a calculation like SELECT U_Sum1 + U_Sum2 ?
I think B1 is regarding the DB-Col type - also for DataTable querys because B1 shows (normally...) the correct user defined format for price, date... also for DataTables.
So maybe CAST is helping you too for the DataTable-query. Try to SELECT your problem column with CAST:
SELECT
CAST(U_WhatEver AS NUMERIC(19,6))
FROM
...
But I also found that B1 does not regard everything of the B1-ColTypes for DataTables. For example a ShortText(20) in a DataTable-Matrix is not always restricted to 20 characters in the GUI. The user can type more than 20 characters without the red alarm-message at the bottom.
Although sometimes this works - it depends on the real column-type in DB that SAP has created: Sometimes it's nvarchar(max) and sometimes nvarchar(20) as expected.
If it's nvarchar(max) then a CAST has helped me again for the DataTable-query:
SELECT
CAST(U_Alpha20 AS NVARCHAR(20))
FROM
...
After that the GUI restricts the user-input to 20 characters
Cheers,
Roland
Hi Roland,
I face the same problem in matrix.loadfromdatasource for the second time will show garbage value in numeric column.
How did you solve it???
Following is my query, I execute the query with B1 datatable. then bind it to matrix column.
SELECT
CAST(row_number() OVER (ORDER BY ORDR.DocEntry) AS INT) AS RowNo,
ORDR.DocEntry, ORDR.DocNum, ORDR.CardCode,
ORDR.DocDueDate, ORDR.DocStatus, RDR1.LineNum,
RDR1.ItemCode, RDR1.Dscription, CAST(RDR1.Quantity AS NUMERIC(19,6)) AS Quantity,
RDR1.Price, RDR1.LineTotal, RDR1.TotalFrgn,
RDR1.WhsCode, RDR1.U_LotNo, RDR1.U_CalWeight,
RDR1.U_WeightQty, RDR1.U_CallOffQty,
ISNULL(RDR1.Quantity,0)-ISNULL(RDR1.U_CallOffQty,0) AS OpenQty,
RDR1.TreeType
FROM
ORDR
INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry
WHERE
ORDR.DocStatus='O' AND RDR1.TreeType='S' AND RDR1.Quantity>0
Thanks for any help...
Hock Soon
hi
Just check that when ur trying to put value in itemcode no other form is active then the form u r putting itemcode for eg not even choose from the list form should be open
Thanks
Pavana Punja
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Then my friend, u forgot telling the main thing !!
it was the problem. odatatable.clear after every matrix reload is essential. it solved the problem many thanks .
regards,
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Roland and David ,
thanks both of u for looking into this rather unfathomable problem !! even i get confused.
David, it never happens that my Quantity query writes null values.it always has non zero value.
Roland, as I said, precisely, in add mode, I have just "#" and itemcode column bound to user datasource. as soon as I switch to update mode, i m again rebinding my all columns to the columns of the table into which they should save the respetive values. and consequently, my oMatrix.LoadFromDataSource() code fills up the matrix after passing proper where condition parameters.
but, as I said, some extra rows are printed with 0.00 value.
clue less. let me do some more R n D with databinding.will surely let u know the consequences.
thanks & regards,
Binita
.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Roland,
if possible, please let me know how can i wipe out the matrix completely without having the quantity/rate type columns with 0.00 values. (please see my previous reply to u in the same thread above ) .its urgent.
regards,
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I'm doing a simple oMtx.Clear before every RE-loading of a Matrix.
When there is (or are) lines with empty Mtx-Cells of Columns which are bounded to Price, Quantity...etc type I see no way to get rid of the 0,00.
Wasn't the rebinding to a UserDataSource only temporary for the CFL-Problem?
I fear I don't understand the problem correctly....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Roland ,
The workaround for casting the rownumber to integar worked. for the crypted chaos columns, I had to bind the first "#" column to short number type user datasource.
thanks once again.
( one last question :). even after clearing the matrix, the quantity and measure type columns which were bound to the datatable query columns show 0.00. how can i wipe this out completely? for example, the recordcount for datatable query is 15. now, next time i again bind the same matrix in (update mode) with different datasource, and suppose it has 6 rows to fill, then , it shows 6 rows with perfact data but, rest (14-6) = 8 rows are filled with 0.00. how can I remove that?)
regards,
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Rolandddd , God bless u for that workaround !!!
it just worked. thanks.
But, u can further help me....as I m stuck up one more time.
I want to print this rowcounter in the first '#' column.I have posted even question on that. it prints 0 in all the rows.
I am binding it the same way I m binding other columns.
oMatrix.Columns.Item("Col0").DataBind.Bind("PopTable", "ROWC")
should it work?
thanks in advance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI David and Roland,
its wierd but, attaching a datasource query to column which is already attached to quantity type userdatasource doesn't simply work(atleast in my case). I removed the attached userdatasource. and after doing matrix.loadfromdatasource, I had to again re-attach (setbound true) the quantity column to the original column of my database table. it is quite apparent tht if I don't do this , it should show me garbage values next time I load the column,since the container column is now attached to coulmn of query which dosent have any defined type of its own like quantity or varchar. this is extreemly size and type specific.
Please correct if my understanding is wrong(even after spending a whole day to solve it!!).
regards,
Binita Joshi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thts what David. thts what I am wondering. if I am doing databind to column of itemcode in datatable query to the column of matrix which is attached to userdatasource of type shorttext, it fetches data. but, if I am fetching quantity column to the matrix column which is attached to userdatasource of type dt_quantity, it fetches data for the first time, when I again populate it, it prints garbage, while itemcode shows perfact values, though it is also attached to userdatasource. so whts wrong with quantity type userdatasource???
or I doubt I m doing something wrong!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi David,
yes. I m writting exactly the same thing and it is not giving me any syntactic or functional error but just printing garbage !!
regards.
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
implementation error for datatable query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Roland and David,
I know , I am posting this thing really late but, I happend to implement it only now.
one strange error I am facing is, if the column which is attached to userdatasource of type dt_Quantity is databound to one of the column of datatable query, first time it shows proper data. on subsequent immediate fetch(or execution of the same query), all other rows show same data while this particular column shows some garbage like encrypted varchar chararcters!!!
Dont really know, what to do with this.
(also, if i remove userdatasource and attach it to the table column of type quantity, it works just fine. I know, it has been discussed before in the same post that you cant have userdatasource attached to the same column to which you are binding column of datatable query but this thing works just fine with itemcode column of userdatasource of type shorttext. so I was just wondering what's wrong with quantity??)
any clues ??? its urgent
thanks in advance.
regards,
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Roland,
I have a reply from SAP and it works fine:
wht u need to do is add the following line to you CFL event.
matrix.Columns.Item("col_1").DataBind.SetBound(True, "", "itcodeDS")
form.DataSources.UserDataSources.Item("itcodeDS").ValueEx = val
matrix.SetLineData(pVal.Row)
even if u have bound the column while loading the form or menu , you will again have to bind it in CFL event..
try it...
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Binita,
thank you very much for that tip! )
I think it is the best to rebind the CFL-column back to the UserDataSource directly after every oMtx.LoadFromDataSource() with DataTable...
...because if there are functions which are doing calculations on the MTX-rows, these functions can always rely on a UserDataSource for the CFL-column.
If we would do the rebind only at CFL those functions would have to read values sometimes from DataTable and sometimes from UserDataSource.
OK: Cell.Item("XX").specific.value should always work but I try to get rid of EditText.Value/String as much as I can...
Cheers,
Roland
Hi Roland,
Thanks again for the code. I already tried it with no luck
according to me, to get system CFL work on matrix column, u will have to bind it with user data source and do matrix.setlinedata(rowindex).
But this works only when u don't load the matrix with the datatable but copy it with 'specific.value' property to each column, as I have mentioned in my first scrap. but again , we will be back to square one as it takes minutes to load the data if done that way.
I Have posted the issue to SAP as I want it with datatable only.....will let u know for sure as soon as I get it.
till then..........
regards,
Binita
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Roland and David,
Thank u so very much for replying back. the code given by roland worked perfactly fine. but, It has one problem. in my user defined form, one of the column of the matrix has system CFL attached (CFL for itemcode). to do this, I will have to bind that column to user datasource. now, for attaching datatable, I will have to bind my columns with the columns in the datatable query. after, the matrix get loaded with the code given by Roland, I am adding one empty row and trying to bind the column with user datatsource. it gives me "Matrix-line exists' error. as it requires "matrix.clear()" to work. if I do this, my matrix gets wiped out. now, How can I do this?
Regards..............
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Binita,
I've tested the CFL for DataTable-Matrixes in my test-AddOn. But there's still one problem left. Maybe you find it out (I will need this in the future too...).
The ChooseFromList must be added behind the DataTable-Bind, which is done on every MTX-Load (it's not from the sample above, so the UIDs are new ones):
oDt = oDts.Item("dt_test")
query = "SELECT * FROM [@T_CONVTOOLS01]"
oDt.ExecuteQuery(query)
oMtx.Columns.Item("0").DataBind.Bind("dt_test", "Code")
oMtx.Columns.Item("1").DataBind.Bind("dt_test", "U_Alpha01")
oMtx.Columns.Item("2").DataBind.Bind("dt_test", "U_Price01")
oMtx.Columns.Item("3").DataBind.Bind("dt_test", "U_Quant01")
oMtx.Columns.Item("4").DataBind.Bind("dt_test", "U_Date01")
AddChooseFromLists()
oMtx.LoadFromDataSource()
...where AddChooseFromLists() is...
Private Shared Sub AddChooseFromLists()
Try
Dim oCfls As SAPbouiCOM.ChooseFromListCollection
Dim oCons As SAPbouiCOM.Conditions
Dim oCon As SAPbouiCOM.Condition
Dim oCfl As SAPbouiCOM.ChooseFromList
Dim oCflCreationParams As SAPbouiCOM.ChooseFromListCreationParams
Dim oBtn As SAPbouiCOM.Button
Dim oMtx As SAPbouiCOM.Matrix
Dim oCol As SAPbouiCOM.Column
Dim oEtx As SAPbouiCOM.EditText
oMtx = oForm.Items.Item("MTX_TEST02").Specific
oCfls = oForm.ChooseFromLists
oCflCreationParams = SboCon.SboUI.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_ChooseFromListCreationParams)
oCflCreationParams.MultiSelection = False
oCflCreationParams.ObjectType = SAPbouiCOM.BoLinkedObject.lf_BusinessPartner
oCflCreationParams.UniqueID = "CFL_C1"
oCfl = oCfls.Add(oCflCreationParams)
'#
'### OPTION:
'# Shown ChooseFromList restricted by Conditions
oCons = oCfl.GetConditions()
oCon = oCons.Add()
oCon.Alias = "CardType"
oCon.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
oCon.CondVal = "C"
oCfl.SetConditions(oCons)
'#
'###
'#
oCol = oMtx.Columns.Item("1")
oCol.ChooseFromListUID = "CFL_C1"
oCol.ChooseFromListAlias = "CardCode"
'#########################################################
Catch e As Exception
Microsoft.VisualBasic.MsgBox(className & ".AddChooseFromLists()" & vbCrLf & "Exception:" & vbCrLf & e.Message.ToString)
End Try
End Sub
But I'm running into problems when trying to set the MTX-Cell via DataTable at the CFL-Event (see comment):
If pVal.EventType = SAPbouiCOM.BoEventTypes.et_CHOOSE_FROM_LIST Then
Dim oMtx As SAPbouiCOM.Matrix
Dim oCFLEvent As SAPbouiCOM.IChooseFromListEvent
Dim oCFL As SAPbouiCOM.ChooseFromList
Dim oDataTable As SAPbouiCOM.DataTable
Dim cflID As String
Dim oDt As SAPbouiCOM.DataTable = oForm.DataSources.DataTables.Item("dt_test")
oCFLEvent = pVal
cflID = oCFLEvent.ChooseFromListUID
'Dim oForm As SAPbouiCOM.Form
'oForm = SboConnection.SboUI.Forms.Item(FormUID)
oCFL = oForm.ChooseFromLists.Item(cflID)
If Not oCFLEvent.BeforeAction Then
oDataTable = oCFLEvent.SelectedObjects
If oDataTable Is Nothing Then Exit Sub
'############## Matrix Test #######################################################################
If (oCFLEvent.ItemUID = "MTX_TEST02") Then
oMtx = oForm.Items.Item("MTX_TEST02").Specific
'### CFL Results to UserForm
'#
oDt.Rows.Offset = oCFLEvent.Row - 1
MsgBox(oDt.GetValue("U_Alpha01", oCFLEvent.Row - 1))
MsgBox(oDataTable.GetValue("CardCode", 0))
' PROBLEM - can't get this working - don't know at the moment:
' on other forms SetValue works....
oDt.SetValue("U_Alpha01", oCFLEvent.Row - 1, oDataTable.GetValue("CardCode", 0))
' this also gives me problems...:
'oMtx.Columns.Item("1").Cells.Item(oCFLEvent.Row).Specific.value = oDataTable.GetValue("CardCode", 0)
oForm.Update()
'#
'###
End If
End If
End If
The CFL-Form opens and returns the choosen value(s) at the CFL-event. But writing to MTX still not works with this code...
Maybe you find the last piece. If so: please write here.
Cheers,
Roland
p.s.:
David Nussböck wrote
LOL - ROLAND WAS FASTER ))
...just one minute...
Hi Binita,
here's a sample
oForm.DataSources.DataTables.Add ("RESULT")
oForm.DataSources.DataTables.Item("RESULT").ExecuteQuery ("SELECT U_Ausdruck, U_Verbuch FROM
oForm.Items.Item("mtxList").Specific.Columns.Item("ColAusdruc").DataBind.Bind "RESULT", "U_Ausdruck"
oForm.Items.Item("mtxList").Specific.Columns.Item("ColVerbuch").DataBind.Bind "RESULT", "U_Verbuch"
regards
LOL - ROLAND WAS FASTER ))
David
Edited by: David Nussböck on Feb 13, 2008 9:39 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi!
You may use the DataTable:
Every time the form loads:
oDts = oForm.DataSources.DataTables
oDts.Add("DT_MYDATATABLE")
Every time the MTX loads:
Dim .......
Dim oMtx......
Dim query As String
query="SELECT U_one, U_two [...] FROM [@MY_TABLE] [...] WHERE [...]"
oDt = oDts.Item("DT_MYDATATABLE")
oDt.ExecuteQuery(query)
With oMtx.Columns
.Item("0").DataBind.SetBound(True, "", "UDS_0") ' <- one column as UserDataSource
.Item("1").DataBind.Bind("DT_MYDATATABLE", "U_one")
.Item("2").DataBind.Bind("DT_MYDATATABLE", "U_two")
End With
oMtx.LoadFromDataSource()
A disadvantage is that there is no line-numbering. Except you bind the first col to a UserDataSource and fill it again line by line after the "power"-LoadFromDataSource. But this is time consuming again.
But the best thing would be if there were a SQL-function which could select the row-number directly in the query. Does anybody know such a wished function "ResultRowCounter()" ?
SELECT dbo.ResultRowCounter(), U_Col1, U_Col2 FROM.......
Cheers,
Roland
Edited by: Roland Toschek on Feb 13, 2008 11:23 AM
wrong UID at "oDts.Add"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.