SQL statement not bringing back results
A user on IDS 11.10 reported that a SELECT on a history table filtered by date range and h_type returned ~17,000 rows with 'ORDER BY h_date', but zero rows with 'ORDER BY h_date DESC' — the same behaviour in dbaccess, ISQL and 4GL, and on a test database. Early replies only flagged a typo (a second WHERE that should be AND), which the poster corrected without changing the symptom. Other suggestions included NULL handling, insufficient temp space, comparing SET EXPLAIN plans / forcing the plan with directives, and an IBM engineer's suspicion of a product defect or corrupt index (run oncheck -cI). No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a real mystery here. I have a SQL statement that should bring back
rows, however if I put in an order by DESC it does not bring back results.
This one works, brings back around 17,000 records...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
where h_type = 'BILLED'
order by h_date
This does not, brings back NO RECORDS FOUND...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
where h_type = 'BILLED'
order by h_date Desc
Any thoughts?
It's an online engine 11.10. There is a test database with a slightly older
dataset and does the same thing. My first thought was bad data, since it
worked just two months ago. No changes have been made to informix.
Hi, try "and h_type = 'BILLED'" instead of "where h_type = 'BILLED'". That's
going to work.
I don't understand how your first statement worked. I tried it and I got the
following message :
201: A syntax error has occurred. (Quite normal).
Regards,
Olmedo Monteverde
OLF S. A.
Z.I.3, Corminboeuf
Case Postale 1152
1701 Fribourg, Switzerland
-----Message d'origine-----
De : ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] De la part de ROBERT
REPPERT
Envoyé : mardi, 10. mars 2009 15:21
À : ids@iiug.org
Objet : SQL statement not bringing back results [15070]
I have a real mystery here. I have a SQL statement that should bring back
rows, however if I put in an order by DESC it does not bring back results.
This one works, brings back around 17,000 records...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
where h_type = 'BILLED'
order by h_date
This does not, brings back NO RECORDS FOUND...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
where h_type = 'BILLED'
order by h_date Desc
Any thoughts?
It's an online engine 11.10. There is a test database with a slightly older
dataset and does the same thing. My first thought was bad data, since it
worked just two months ago. No changes have been made to informix.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Is the repeated WHERE clause a typo? The WHERE key work can only appear
once. I'm surprised that you are not getting a syntax error. Try these:
SELECT * FROM history
WHERE h_date between '01/28/2009' and '02/26/2009'
AND h_type = 'BILLED'
order by h_date;
SELECT * FROM history
WHERE h_date between '01/28/2009' and '02/26/2009'
AND h_type = 'BILLED'
order by h_date Desc;
Art
On Tue, Mar 10, 2009 at 10:21 AM, ROBERT REPPERT <slappomatic@msn.com>wrote:
> I have a real mystery here. I have a SQL statement that should bring back
> rows, however if I put in an order by DESC it does not bring back results.
>
> This one works, brings back around 17,000 records...
>
> SELECT * FROM history
> where h_date between '01/28/2009' and '02/26/2009'
> where h_type = 'BILLED'
> order by h_date>
> This does not, brings back NO RECORDS FOUND...
>
> SELECT * FROM history
> where h_date between '01/28/2009' and '02/26/2009'
> where h_type = 'BILLED'
> order by h_date Desc>
> Any thoughts?
>
> It's an online engine 11.10. There is a test database with a slightly older
> dataset and does the same thing. My first thought was bad data, since it
> worked just two months ago. No changes have been made to informix.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--00163646d8bc20ae9f0464c556d8
Thanks to those who have picked this typo up, the second where should be an and
The queries should be...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date
This does not, brings back NO RECORDS FOUND...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date Desc
Thanks to those who have picked this typo up, the second where should be an and
The queries should be...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date
This does not, brings back NO RECORDS FOUND...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date Desc
In some cases, the null values can altered the result of a query.
Check if you have null values this:
Select count(*) from history where h_date is null;
Select count(*) from history where h_type is null ;
If result more than 0 rows, then modify your query this:
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
And not h_date is null and not h_type is null
order by h_date Desc
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ROBERT
REPPERT
Sent: Martes, 10 de Marzo de 2009 10:34 a.m.
To: ids@iiug.org
Subject: Re: RE: SQL statement not bringing back results [15075]
Thanks to those who have picked this typo up, the second where should be an and
The queries should be...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date
This does not, brings back NO RECORDS FOUND...
SELECT * FROM history
where h_date between '01/28/2009' and '02/26/2009'
and h_type = 'BILLED'
order by h_date Desc
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Technically, there is no difference between electing/fetching records from
history as both the SELECT statements have the same WHERE clause.
Try selecting a "h_date" column instead of all columns i.e. "SELECT h_date
FROM history" and see what you get.
Are you running this query using "dbaccess" utility? or from the source code..
Do you have enough space in $TEMP directory? It might possible the said ORDER
by requires a TEMP space to sort the result and if have
no enough space to do so, you may not get result back..just a thought!!
-Dharmendra
> To: ids@iiug.org
> From: slappomatic@msn.com
> Subject: Re: RE: SQL statement not bringing back results [15075]
> Date: Tue, 10 Mar 2009 11:33:35 -0400
>
> Thanks to those who have picked this typo up, the second where should be an
> and
>
> The queries should be...
>
> SELECT * FROM history
> where h_date between '01/28/2009' and '02/26/2009'
> and h_type = 'BILLED'
> order by h_date>
> This does not, brings back NO RECORDS FOUND...
>
> SELECT * FROM history
> where h_date between '01/28/2009' and '02/26/2009'
> and h_type = 'BILLED'
> order by h_date Desc>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Windows Live: Life without walls.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_032009
I am using ISQL, DBACCESS, and in the 4GL code. DBTEMP is 54% using just h_date in the select produces same results.
>I have a real mystery here. I have a SQL statement that should bring back
>rows, however if I put in an order by DESC it does not bring back results.
>
>This one works, brings back around 17,000 records...
>
>where h_date between '01/28/2009' and '02/26/2009'
>where h_type = 'BILLED'
>order by h_date
>
>This does not, brings back NO RECORDS FOUND...
>
>SELECT * FROM history
>where h_date between '01/28/2009' and '02/26/2009'
>where h_type = 'BILLED'
>order by h_date Desc>
>Any thoughts?
>
>It's an online engine 11.10. There is a test database with a slightly older
>dataset and does the same thing. My first thought was bad data, since it
>worked just two months ago. No changes have been made to informix.
This seems like it could be a defect or possibly a bad index (which is only
encountered if you traverse it in reverse order like your order by desc might
cause). If you get set explain output for both queries are they using the same
query plan? If they aren't, you could try using directives to force the same
plan. You also may want to consider running oncheck -cI on that table if it's
feasible.
Jacques Renaut
Informix APD team
IBM