SQL Statement Issue

HCL Domino Domino Forum
1

We are experiencing an odd issue when extracting data from some iSeries DB2 files using SQL statements from inside Notes. The following statement returns a result of x values for ABAN8:

SELECT COUNT(ABAN8) from JDRFILES.F0101,JDRFILES.F0301 WHERE ABAT1 = ‘HOE’ AND ABAN8 = A5AN8 AND A5ACL >1

We we execute the following code, and get (x-2) documents created. If the source data changes, x can change, and different values for ABAN8 are left out. But 2 values remain missing from the results. Any ideas would be appreciated.

'*******************************************

selectstmt = " SELECT ABAN8, ABALPH, ABMCU, A5CPGP, A5TXA1 from JDRFILES.F0101,JDRFILES.F0301 WHERE ABAT1 = ‘HOE’ AND ABAN8 = A5AN8 AND A5ACL >1"

tcnt = 0

qry.SQL = selectstmt

Set result.Query = qry

result.Execute

If Not (result.IsResultSetAvailable) Then

Exit Sub

End If

result.FirstRow

continue = True

While continue

Set doc = New NotesDocument (db)

doc.Form = "empkey"

acnt = acnt + 1

Call result.GetValue("ABAN8", tmp1)

tmp2 = result.GetValue("ABALPH", tmp2)

tmp3 = result.GetValue("ABMCU", tmp3)

tmp4 = result.GetValue("A5CPGP", tmp4)

tmp5 = result.GetValue("A5TXA1", tmp5)

doc.AlphaName = tmp2

doc.AddNumber = Trim(Str(tmp1))

doc.DC = tmp3

doc.PriceGroup = tmp4

doc.TaxCode = tmp5

Call doc.Save (True, True)



If result.IsEndOfData Then

	continue = False

Else

	result.NextRow

End If

tcnt = tcnt + 1

Wend

2

Subject: SQL Statement Issue

Steve, you don’t show how you are setting up the ODBCResultset variable, which can affect this. I haven’t used LSX ODBC in a while, having converted entirely over to LSX LC, but if I recall correctly you need to set the CacheLimit and FetchBatchSize on the ODBCResultset.

You may also want to post in the Enterprise Integration forum. At least there you will be ignored by people who might be able to help. :wink:

HTH,

Charles

3

Subject: RE: SQL Statement Issue

Thanks for your reply Charles. It’s my understanding that those two fields are not required, but I have tried setting them with more unusual results - two different results were not returned. I’ll try your suggestion of the EI forum.