Order By problem

[Jean Sharpe]

I am new at SQL and am trying to debug a problem at a client site. We have a C++ application using MFC CRecordSet and ODBC. The application does a select statement to get data -- this works fine. When the same application uses the same select and adds an order by, we get the correct amount of rows, but no data. Odd thing is, when we use a different database, we do not have this problem. This database had recently been copied from another server. I have done checkdb and DBCC DBREINDEX on all the tables used, but this hasn`t helped. Any suggestions or ideas?

[Asfen Tantono]

On 7/2/98 9:35:39 AM, Jean Sharpe wrote:
> I am new at SQL and am trying to debug a problem at a client site. We have
> a C++ application using MFC CRecordSet and ODBC. The application does a
> select statement to get data -- this works fine. When the same application
> uses the same select and adds an order by, we get the correct amount of
> rows, but no data. Odd thing is, when we use a different database, we do
> not have this problem. This database had recently been copied from another
> server. I have done checkdb and DBCC DBREINDEX on all the tables used, but
> this hasn`t helped. Any suggestions or ideas?

Jean,

On which field did you order it by? I suspect this field contain several lines of `NULL` or blank values. Try placing `DESC` after ther ORDER by <field>. eg. ORDER BY FIELD1 DESC. See if you can see any data this time.

[jean sharpe]

Asfen and anyone else who can help:

I have tried the DESC option. Still get the same symptom in ODBC app. When I run the same SQL query in ISQL/w I get the correct results. If fails only in the ODBC app.

On 7/2/98 6:41:22 PM, Asfen Tantono wrote:
> On 7/2/98 9:35:39 AM, Jean Sharpe wrote:
> I am new at SQL and am trying
> to debug a problem at a client site. We have
> a C++ application using
> MFC CRecordSet and ODBC. The application does a
> select statement to
> get data -- this works fine. When the same application
> uses the same
> select and adds an order by, we get the correct amount of
> rows, but no
> data. Odd thing is, when we use a different database, we do
> not have
> this problem. This database had recently been copied from another
>
> server. I have done checkdb and DBCC DBREINDEX on all the tables used, but
>
> this hasn`t helped. Any suggestions or ideas?

Jean,

On which
> field did you order it by? I suspect this field contain several lines of
> `NULL` or blank values. Try placing `DESC` after ther ORDER by <field>. eg.
> ORDER BY FIELD1 DESC. See if you can see any data this time.