Does a given row exist.

I want to check whether a row for a given critera exists. the only way I know at present is, for example: SELECT f1, f2 FROM t WHERE f3 = 'something' And ... ... ;if (SQLCODE == 0) { ... } if (SQLCODE == 100) { ... } I am wondering if there is a more efficient way which avoids unnecessarily retrieving fields f1 and f2. Thanks, Andrew H. sending to informix-list

Andrew Hardy wrote: > I want to check whether a row for a given critera exists. > > the only way I know at present is, for example: > > SELECT f1, f2 FROM t WHERE f3 = 'something' And ... ... ;> if (SQLCODE == 0) > { > ... > } > > if (SQLCODE == 100) > { > ... > } > > I am wondering if there is a more efficient way which avoids unnecessarily > retrieving fields f1 and f2. Without knowing more about the circumstances, I can offer a suggestion or two but you'll have to determine if either of them will offer any benefit. My first suggestion would be to select the key fields (f3 and ???). That may be more efficient in that you might get away with just an index read. My second suggestion is somewhat dependent on why you want to know if the row exists. If, for example, you plan to insert a value into the table only if the row does not already exist, I'd suggest skipping the select and go right to insert. If the insert fails (duplicate value in a unique index), then the row existed and you'll still have done just one operation. If the row did not exist, you'd still have just the one insert operation. Might either of those help? -- June Hunt