syntax for detecting presence of query result 1

[ixnay5]

hi -

i'm new to Informix 4GL (7.3) and i've built a 4GL program but need some help with it.

i have a cursor scanning through a temp table i loaded (temp_tab).

from within the cursor loop, i want to feed a query with one of the values grabbed by the cursor, and if there's a result > 0, set a flag to 1, otherwise set it to 0.

my question is, "what's the syntax for detecting the presence of a query result?"

here's the pertinent code:


define r_s record
col1 char (16),
col2 char (30),
col3 char (4),
col4 decimal (18,6)
end record

let q = "select * from temp_tab"

prepare ex_q from q

declare q_curs cursor for ex_q

foreach q_curs into r_s.*

#query goes here:
select distinct myname from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

#how do i detect the record count resulting from
#the above query?

if #query_result > 0 then
let col5 = "1"
else
let col5 = "0"
end if

insert into newtable
(col1,col2,col3,col4,col5)
values
(
r_s.col1,
r_s.col2,
r_s.col3,
r_s.col4,
col5
)

end foreach

 

[olded]

Hi:

In 4GL, you can do something like this:

# untested
select distinct myname into myname_variable
from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

case
when sqlca.sqlcode < = 0
display "error"
when sqlca.sqlcode = 100
let col5 = "1"
otherwise
let col5 = "0"
end if
# end code

if there's no data in your select, sqlca.sqlcode equals 100 for not found. Hopefully, I've enterpeted what you've wanted, correctly.

Regards,


Ed

 

[ixnay5]

thx Ed,

your intepretation was certainly good enough.

but looks like i'm getting a 100 back for every record, which isn't right.

if i take the sql statement out and run it with a known good value for r_s.col1, i get the expected results back.

soooo...i'm guessing my sql statement isn't executing reliably inside the cursor loop. my books say any 4GL statement can be executed inside a cursor loop...but maybe since this is really an sql statement, it doesn't really work.

i'll keep trying...maybe i can wrap the sql in an execute statement or something.

ix

 

[olded]

Hi:

If you are using this SQL statement:

select distinct myname from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

in a 4GL program I'm not surprised you get "not found" each time. I think the problem is that you aren't selecting into an Informix 4GL variable - even if you aren't using the variable:

select distinct myname into myname_variable
from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

Also, you might consider using a count:

# untested
select count(distinct myname) into counter_variable
from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

if counter_variable > 0 then
let col5 = "1"
else
let col5 = "0"
end if
.
.


Regards,

Ed

 

[ixnay5]

thx. ed.

you're right. and i've tried all your suggestions. the count() method makes the most sense. still, counter_variable is coming up 0 in 4gl.

when i pull the query out and run it in isql (change into to as) with a known good value, i get the expected results which are > 0.

...?

ix

 

[olded]

Hi:

In this statement:

select distinct myname into myname_variable
from mytable
where yourname = 'r_s.col1'
and myname[1,3] = "val"

try removing the quotes from around r_s.col. I don't think they're required (r_s is a 4GL record), and it could be your problem:

select distinct myname into myname_variable
from mytable
where yourname = r_s.col1
and myname[1,3] = "val"

Regards,

Ed

 

[ixnay5]

Ed -

Hot Dang! That did it! Thanks for sticking with me.

Lemme send some stars your way...

ix