SQL to Notes

HCL Domino Domino Forum
1

I am able to run this SQL code in both the SQL Query Analyzer and in Crystal Reports, but am not able to convert it to put into an agent in Notes. Can anyone help?

qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _

rtrim(case " + _ 

   when description like '__00000000000' then substring(description, 1, 2) + '%' " + _

   when description like '____000000000' then substring(description, 1, 4) + '%' " + _

   when description like '_____00000000' then substring(description, 1, 5) + '%' " + _

   when description like '______0000000' then substring(description, 1, 6) + '%' " + _

   when description like '_________0000' then substring(description, 1, 9) + '%' " + _

   when 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 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

2

Subject: SQL to Notes

qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _"rtrim(case " + _

"when description like ‘__00000000000’ then substring(description, 1, 2) + ‘%’ " + _

etc

3

Subject: RE: SQL to Notes

Thank you. When I updated the code to below I am getting an error, “Unexpected: End-of-line; Expected: Expression” that looks like it is pointing to the last line, "SELECT SPACE(MLevel . . . " What do I need to do to end the code?

Sub Initialize

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet



Set qry.Connection = con

Set result.Query = qry



qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _

"rtrim(case " + _ 

"when description like '__00000000000' then substring(description, 1, 2) + '%' " + _

"when description like '____000000000' then substring(description, 1, 4) + '%' " + _

"when description like '_____00000000' then substring(description, 1, 5) + '%' " + _

"when description like '______0000000' then substring(description, 1, 6) + '%' " + _

"when description like '_________0000' then substring(description, 1, 9) + '%' " + _

"when 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 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 " + _

End Sub

4

Subject: RE: SQL to Notes

"SELECT SPACE(MLevel * 2) + MDesc + ‘(’ + Mnemonic + ‘)’ As Mnemonic FROM #t2 ORDER BY AccessCode " + _

Um, plus what?

5

Subject: RE: SQL to Notes

Thanks, Stan. I fixed that error now.

I have now updated the code and am able to see that I am getting the connection and the documents through debug, but now I am trying to get them to actually create a new document in my database for each row captured. Any help on what I am missing? The field on my document is, “Mnemonic” and the document name is “FinalTry” :slight_smile:

Sub Initialize

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Dim Mnemonic As String

Dim workspace As New NotesUIWorkspace

Dim db As NotesUIDatabase



Set db = workspace.CurrentDatabase



Call con.ConnectTo("AIS Warehouse")



Set qry.Connection = con

Set result.Query = qry



qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _

"rtrim(case " + _ 

"when description like '__00000000000' then substring(description, 1, 2) + '%' " + _

"when description like '____000000000' then substring(description, 1, 4) + '%' " + _

"when description like '_____00000000' then substring(description, 1, 5) + '%' " + _

"when description like '______0000000' then substring(description, 1, 6) + '%' " + _

"when description like '_________0000' then substring(description, 1, 9) + '%' " + _

"when 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 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 " 



Call result.Execute



If result.IsResultSetAvailable Then

	Do

		result.NextRow

		Mnemonic = result.GetValue("Mnemonic")

	Loop Until result.IsEndOfData

	result.Close(DB_CLOSE)

Else

End If

con.Disconnect

End Sub