Get only one unique record from AS400

HCL Domino Domino Forum
1

Hello,

One of my application is notes client based app and has back end AS400. I want to perform like this assuming user had document opened in lotus notes client. User clicks on Button1. Upon clicking the button, using the value of a key from a opened notes document to fetch an appropriate record from AS400.

So it is some thing like this.

.

.

.

Set uidoc = workspace.CurrentDocument

Set doc1 = uidoc.Document

Dim enum As Variant

enum = doc1.GetItemValue( “empnum” )

.

.

username = “xxx”

password = “xxxxxx”

Set currentLog = New NotesLog( “Agent Log” )

Set con = New ODBCConnection

Set qry = New ODBCQuery

Set result = New ODBCResultSet

Set qry.Connection = con

Set result.Query = qry

Set currentLog = New NotesLog( “Database log” )

temp = con.ConnectTo(“yyyy”, username, password)

qry.SQL = “SELECT * from Test1.XYZ”

.

.

.

.

Instead, I want to perform like this,

qry.SQL = “SELECT * from Test1.XYZ Where XYZ.Column or field = enum”. I only want to fetch one record,

can any one guide me how to do that?. I have done mostly work with SELECT * From… but no idea how to use WHERE condition in script.

Thanks in advance

MS

2

Subject: Get only one unique record from AS400

I think I understand your requirement and you are on the right track.

SQL syntax:

Select Distinct(table.Field) where table.Field = ‘enum’

The distinct works like an @unique and it may help.

3

Subject: RE: Get only one unique record from AS400

Thanks for your reply.

So this syntax is fine right,

qry.SQL = “Select Distinct(table.field) where table.Field = ‘enum’”

Thanks

MS

4

Subject: RE: Get only one unique record from AS400

Actually no the syntax is not correct for db2 (which is what you are accessing on the 400).

Actually, it should be

Select Distinct(Field) from db.table where table.field = ‘enum’

or

Select Distinct(table.Field) from db.table where table.field = ‘enum’

It kind of depends on a few things, the odbc driver you are using, the version of db2 on the 400…

Search IBM’s site for sql and syntax and db2 or google it.

5

Subject: RE: Get only one unique record from AS400

DISTINCT is pretty processor intensive and should be used very judiciously. In this case FETCH FIRST would be better because it would short-circuit the query processing and return the first row as soon as it is found. DISTINCT would require building the complete resultset then going through and eliminating duplicates.

SELECT table.field FROM db.table WHERE table.field = ‘enum’ FETCH FIRST 1 ROW ONLY

For even better performance you can use OPTIMIZE FOR 1 ROW as well. Just be aware the resultset is made read-only.

– Charles