Hello - I have the code below that I was sent as SQL - I’ve tried to change it a bit to work in Notes, but my agent is just skipping right over the code starting with the qry.SQL = line and jumping right to the For j =1 line. Any ideas how to update this code?
The outcome is a tree-structured list that I need to display in a view so I can select from the view on a form.
Thanks in advance!
'Load cbudg from AIS warehouse - ibiscodesets - caccess1
'for Mnemonics field on DonorInfo subform
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase, codedb As NotesDatabase
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim res As ODBCResultSet
Dim idView As NotesView, codeview As NotesView
Dim doc As NotesDocument, iddoc As NotesDocument
Dim max As Double, id As String
Dim i As Double, j As Double
Dim bigArray() As String
Dim answer As Variant
Dim newone As String
Set db = session.CurrentDatabase
Set idView=db.getView("(copyofMNIC)")
’ warehouse control
Dim wh_db As New NotesDatabase("","")
Dim wh_doc As NotesDocument
Dim wh_view As notesview
Set wh_db=session.getdatabase("DDAS2/Notes","warehousctacs.nsf")
Set wh_view=wh_db.getview("AISCT")
Dim c1 As String, c2 As String, c3 As String, c4 As String
Set wh_doc = wh_view.Getfirstdocument
If Not wh_Doc Is Nothing Then
c1 = wh_doc.ColumnValues(0)
c2 = wh_doc.ColumnValues(1)
c3 = wh_doc.ColumnValues(2)
c4 = wh_doc.ColumnValues(3)
End If
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set res = New ODBCResultSet
Call con.connectTo(c1,c2,c3,c4)
Set qry.Connection = con
'pyb2 changed when to if
'+ " when description like '__00000000000' then substring(description, 1, 2) + '%' "_
qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode,"_
+ "rtrim(case"_
+ " if description like '__00000000000' then substring(description, 1, 2) + '%' "_
+ " if description like '____000000000' then substring(description, 1, 4) + '%' "_
+ " if description like '_____00000000' then substring(description, 1, 5) + '%' "_
+ " if description like '______0000000' then substring(description, 1, 6) + '%' "_
+ " if description like '_________0000' then substring(description, 1, 9) + '%' "_
+ " if description like '__________000' then substring(description, 1, 10) + '%' "_
+ " else description "_
+ " end) as AccessFilter, "_
+ " rtrim(cast(text1 as char(30))) as MDesc, 0 as MLevel"_
+ " into #t "_
+ " from dbo.caccess1"_
+ "where getdate() between begin_date and end_date" _
+ "SELECT c.Mnemonic, c.AccessCode, c.AccessFilter, c.MDesc," _
+ " (SELECT COUNT(*) FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter) As MLevel, "_
+ " (SELECT TOP 1 p.Mnemonic FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter ORDER BY p.AccessCode DESC) As Parent, " _
+ "CASE WHEN Exists (SELECT * FROM #t p WHERE c.AccessCode <> p.AccessCode and p.AccessCode like c.AccessFilter) THEN '1' ELSE '0' END As hasChildren INTO #t2 FROM " _
+ " #t c " _
+ "SELECT SPACE(MLevel * 2) + MDesc + '(' + Mnemonic + ')' As Mnemonic FROM #t2 ORDER BY AccessCode " _
Set res.Query = qry
res.Execute
res.LastRow
max = res.NumRows
For j = 1 To max
Set doc = db.CreateDocument
doc.Form = "TreeMonic"
res.CurrentRow = j
doc.Mnemonic = res.GetValue("Mnemonic")
Call doc.Save(False,False)
Next
res.Close(DB_CLOSE)
End Sub