Different result between COUNT(*) & DECLARE

Dear All, When I use statement DECLARE in my 4GL function total number of record in a table is much more smaller compared if I used syntax SELECT COUNT(*) in my ISQL statement. What happened to my table ? Thanks .

Nothing, Your table is ok. You should check where condition. You have to check fields char type with null values with two conditions: file is null or not null and length(field) is null or not Sadikin_Halim@app.co.id wrote: > Dear All, > > When I use statement DECLARE in my 4GL function total number of record in a > table is much more smaller compared if I used syntax SELECT COUNT(*) in my > ISQL statement. > What happened to my table ? > Thanks .

Sadikin_Halim@app.co.id wrote in message <95lllv$gkm$1@news.xmission.com>... > >When I use statement DECLARE in my 4GL function total number of record in a >table is much more smaller compared if I used syntax SELECT COUNT(*) in my >ISQL statement. >What happened to my table ? Excuse me for asking two dumb questions: 1) Are you executing or opening and fetching your declared cursor? 2) Are you connected to the same database? Serious questions now - here's the only remaining possibility I can think of - a trap for new players with 4GL. Do you have a conflict between a variable name and a table or column name? create table mytable ( key serial, data char(10) ); then in the 4GL: define data, counter integer .... declare mycursor cursor for select count(*) into counter from mytable where data = something now, the nasty bit here is, the reference to data in the 4GL will access the VARIABLE called data. You should rewrite the select as: declare mycursor cursor for select count(*) into counter from mytable where mytable.data = something This way, it's not ambiguous to the 4GL compiler. Unless you have a 4GL variable called mytable which is a record! Most important tip on this issue is: Never declare a variable to have the same name as a table or a column because you are begging to get into trouble with this problem. Many people I know declare all variables with a "wart" which is a prefix that distinguishes where the name is declared. For example, prepare all statements as s_something, cursors as c_something, global variables as g_something, function arguments as a_something, local variables as l_something and so on. Get the hang of it? As long as NONE of your tables or columns start with x_ where x is any letter, then you have no possible ambiguity. Some people turn this on it's head and declare table and column names with warts, and leave program variables wart-free. I don't think that's quite as convenient, but it's a personal choice. Some people may point out (or you may find it in the 4GL manual) that you could write the select as: declare mycursor cursor for select count(*) into counter from mytable where @data = something because the @ tells the 4GL compiler that this name must absolutely be a column from a table, but I don't like that. Here's why: select a, b, c, d, e from tab1, tab2, tab3 where @f = @g and @h = @i and @j > 20 Now, quickly, tell me which column comes from which table. OK, sure, neither of us know the table schemas, but even when you know the tables, you still have to pause, blink for a moment and consider. Often you can't remember, especially if they are tables you haven't looked at for a while. Also, since many tables share column names, you still risk getting stuck. I just find it heaps easier to fully expand the table.column names and then it's really easy to read and understand the select, and there will be no 4GL ambiguity. Hmmmm - does that solve your problem?