Unknown command beginning 'order by...' ??

[evild]

I keep getting this statement at the end of my results of some sql. The query works fine until it tries to order the results.
unknown command beginning "order by r..." - rest of line ignored.

Currently I have:

/* Column settings */
        column clnum format a16 heading "Class No."
        column bar format a16 heading "Barcode"
        column stat format a12 heading "Item Status"
        column title format a40 heading "Title"
        column auth format a30 heading "Author"
        break on REPORT skip 4
        compute count label 'Num of Items:' of clnum on REPORT

timing start

select replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') clnum,
z30_barcode bar,
z30_item_status stat,
substr(Z13_TITLE,1,40) title,
substr(z13_author,1,30) auth
from Z103, CCC01.Z13, Z30
/* match on Z13 via Z103 */
where substr(Z30_REC_KEY,1,9) = substr(z103_rec_key,6,9)
and substr(Z13_REC_KEY,1,9) = substr(Z103_rec_key_1,6,9)
and substr(Z103_rec_key_1,1,5) = 'CCC01'
and (
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '210' and '219')
or
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '230' and '249')
or
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '260' and '269')
)
AND z30_item_status in ('02','50')
AND z30_sub_library = 'CANT'
order by replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','');

Its a horrible query due to all the replace stements, but I do have to work with a horrible database.There are other bits and peices around that I havent included in this example [like stop timing and clear computes etc etc].

Is there some simple reason why the order by clause does not work? I tried ordering by different data from the select line [e.g. barcode] but still it doe not work. I also tried an order by on the column names themselves but still nothing.

 

[lewisp]

Try using

[tt]ORDER BY 1;[/tt]

 

[sem]

Does it mean that removing ORDER BY clause only makes this statement valid? Do you have other statements in that script? Do you run it from file or by redirecting output to sqlplus in shell script (possible interpreting $ by shell)?

Regards, Dima

 

[evild]

I spool the output to a file.
The query works and does return an un-ordered list. The error message appears at the bottom of my output file.

The entire script is this:

        set echo off
        set pause off
        set heading on
        set pagesize 10000
        set linesize 150 
        set feedback off
        set show off
        set termout off

/* Environment */
        set null

/* Get date  and load into _DATE */
        column TODAY new_value _DATE
        select to_char(SYSDATE, 'fmMonth DD, YYYY') TODAY from DUAL;

/* Output file */
        spool /aleph/u52_5/ccc50/print/c_r_407.rpt

/* Title of report */

ttitle Left 'Date Created: ' _DATE skip 2 Left 'List of books with classmark range of: 210-219, 230-249, 260-269' skip 1 'that also have item status 02 or 50 and are still have sub-library CANT' skip 2

/* Column settings */
        column clnum format a16 heading "Class No." 
        column bar format a16 heading "Barcode" 
        column stat format a12 heading "Item Status"
        -- column usr format a12 heading "Borrower"
        column title format a40 heading "Title"
        column auth format a30 heading "Author" 
        break on REPORT skip 4
        compute count label 'Num of Items:' of clnum on REPORT

timing start

select replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') clnum,
z30_barcode bar,
z30_item_status stat,
-- z36_id usr, 
substr(Z13_TITLE,1,40) title,
substr(z13_author,1,30) auth 
from Z103, CCC01.Z13, Z30
/* match on Z13 via Z103 */
where substr(Z30_REC_KEY,1,9) = substr(z103_rec_key,6,9)
and substr(Z13_REC_KEY,1,9) = substr(Z103_rec_key_1,6,9)
and substr(Z103_rec_key_1,1,5) = 'CCC01'
and (
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '210' and '219')
or
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '230' and '249')
or
(replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','') between '260' and '269')
)
AND z30_item_status in ('02','50')
AND z30_sub_library = 'CANT'
order by replace(replace(replace(replace(replace(substr(z30_call_no,1,16),'$i',''),'$j',''),'$k',''),'$h',''),'$','');


clear breaks
clear computes
set termout on
timing stop
spool off

 

[SantaMufasa]

EvilD, Are you absolutely, positively, I'll-bet-my-paycheck-then-my-wife-then-my-life certain that there is nothing just prior to the "order by" line that would make Oracle think that the "order by" is a new statement? (Usually this error results from a blank line in front of the "order by".) As a test, try bringing the "Order by" up a line, just behind the expression, 'CANT'. As you backspace from "ORDER BY", also backspace over the second quote in 'CANT', then re-type the quote to ensure that there are no spurious hidden characters near the quote. Then please post your findings.

Dave

 

[sampsonr]

I've seen this kind of behavior in cases where there's a comment that has a semi-colon in it just before the ORDER BY. SQL*Plus doesn't seem to notice that it's commented out and then it thinks the ORDER BY on the next line is a new command.

 

[evild]

I dont beleive it! You were right. I had three commands between the last statement and the order by statement. These were commented out with a '--' on each line. I had to remove these three line completely before it would work.

On the example I posted i removed this lines for readability.

Thanks for the help