NEED HELP with Order By clause-URGENT!

From: Tamara Swilley <tswilley_at_agency.com>
Date: Mon, 6 Nov 2000 16:32:56 -0700
Message-Id: <10672.121257@fatcity.com>

Hi experts!

        I have an order by problem in a view that I can't figure out. I'm on Oracle 8.1.5, Solaris 2.6. Here's the code for the view that the select is based on:

select bseq1.SeqID SeqID,

... some other fields ...

         align.TemplateStartBP || '..' ||
         align.TemplateEndBP || ' (' ||
         align.AlignmentDirection || ')'
AlignmentCoordinates,


         ... some other fields  ...

from  GL_BioSeqTab bseq1,
         GL_AlignTab align,


         ... some other tables ...

where


         ... all the conditions ..

;

Now here's the select in the application that isn't working right:

select SeqID,

         AlignmentCoordinates,


         ... the rest of the fields ...

from THE_VIEW
where SeqID = <Sequence ID input by the GUI user>

    and ... other conditions ... = <other conditions input by the GUI user>
order by SeqID, AlignmentCoordinates asc ;

The problem is with the AlignmentCoordinates field and I think it has to do with how it's built within the view. If I take off the first order by parameter (the SeqID), the AlignmentCoordinates will order properly but only if I explicitely specify ASC or DESC. But I really need the AlignmentCoordinates ordered *within* the SequenceID and this does not work at all.

This is the last part of a software release due to our clients tomorrow, so I'm looking for any quick answers.

Thanks in advance,

Tamara Swilley
Oracle DBA
AGENCY.COM: Vail Received on Mon Nov 06 2000 - 17:32:56 CST