Unusual query behaviour
Hi Gurus,
The Environement : I use Informix 9.30 , 4gl 7.20UD2 on Solaris 2.6.
Problem : The application has used a particular program for years but
now it gives me a problem when it executes a certain query.
The query : select 1 from table1
where column1 = "ABC"
and column2 = "XYZ"
When I run the sql in dbaccess it gives me 1 as many as the number of
rows it gets.
However when I run the sql in 4gl , it 'now' says
"SQL statement error number -284.
A subquery has returned not exactly one row." if there are multiple
rows.
Is there any reason for this response ? The only thing that changed is
the IDS version from 9.20 to 9.30. All compilers are the same.
I did fix the problem by retrieving the count in a variable and using
it further for validations.
Curious to know.
Thanks in Advance
Bhaskar
Bkr wrote:
> Hi Gurus,
> The Environement : I use Informix 9.30 , 4gl 7.20UD2 on Solaris 2.6.
>
> Problem : The application has used a particular program for years but
> now it gives me a problem when it executes a certain query.
>
> The query : select 1 from table1
> where column1 = "ABC"
> and column2 = "XYZ"
>
> When I run the sql in dbaccess it gives me 1 as many as the number of
> rows it gets.
>
> However when I run the sql in 4gl , it 'now' says
> "SQL statement error number -284.
> A subquery has returned not exactly one row." if there are multiple
> rows.
>
> Is there any reason for this response ? The only thing that changed is
> the IDS version from 9.20 to 9.30. All compilers are the same.
>
> I did fix the problem by retrieving the count in a variable and using
> it further for validations.
>
> Curious to know.
>
> Thanks in Advance
> Bhaskar
If you use in 4GL select 1 into myvariable
you will get an error. Your data changed, engine
is not to blame.
You can use count(*) or
use cursor with open and fetch, then check sqlca.sqlcode.
HTH
Michael
what does
select count(*) from table1
where column1 = "ABC"
and column2 = "XYZ"
return?
if the answer > 1 then the query will error in your 4GL application
if you don't use a cursor.
If the answer is 1 then your 4GL should, I think, work and you need to
talk to support.
Can't remember what happens if its 0.
uppusab@nasd.com (Bkr) wrote in message news:<de13930e.0403171039.636fbaad@posting.google.com>...
> Hi Gurus,
> The Environement : I use Informix 9.30 , 4gl 7.20UD2 on Solaris 2.6.
>
> Problem : The application has used a particular program for years but
> now it gives me a problem when it executes a certain query.
>
> The query : select 1 from table1
> where column1 = "ABC"
> and column2 = "XYZ"
>
> When I run the sql in dbaccess it gives me 1 as many as the number of
> rows it gets.
>
> However when I run the sql in 4gl , it 'now' says
> "SQL statement error number -284.
> A subquery has returned not exactly one row." if there are multiple
> rows.
>
> Is there any reason for this response ? The only thing that changed is
> the IDS version from 9.20 to 9.30. All compilers are the same.
>
> I did fix the problem by retrieving the count in a variable and using
> it further for validations.
>
> Curious to know.
>
> Thanks in Advance
> Bhaskar