This discussion is archived
10 Replies Latest reply: Feb 22, 2006 8:37 AM by 279947 RSS

Sort causing ORA-00911

423655 Newbie
Currently Being Moderated
I have a query that works fine, when I add sorting to it, I get failed to parse SQL query:
ORA-00911: invalid character. The report displays an office, county, state, type1, type2, cost. I need it to sort: state, county, type1, type2 in that order. Nothing seems to be working properly with any of these sorts. These fields only contain text data. Using HTMLDB 1.61

Thanks,
Scott
  • 1. Re: Sort causing ORA-00911
    423655 Newbie
    Currently Being Moderated
    Can somebody help with this please? I have it statically sorted with an order statement at the end of my query. But the sort features keep causing this error.
  • 2. Re: Sort causing ORA-00911
    435076 Newbie
    Currently Being Moderated
    Did you get anywhere with your ORA-00911? Just wondering, as I received a bunch of them the other day when running scripts on htmldb.oracle.com - it seems a special (hidden) character was being appended to my file whenever I uploaded (I had to edit each file after the upload and delete what appeared to be a blank line, at the end of the file).

    On another note, did you ever do anything with Crystal Reports and HTMLDB (I see you were asking questions last year - if so write to me at Paul.Leland@gnb.ca )
  • 3. Re: Sort causing ORA-00911
    423655 Newbie
    Currently Being Moderated
    I have not gotten anywhere with this ORA-00911. I'm not sure why this is happening. I have hard coded order bys into my queries, but I need the End User to have sorting control.
  • 4. Re: Sort causing ORA-00911
    Marc Sewtz Pro
    Currently Being Moderated
    Scott,
    Put it on htmldb.oracle.com and I can take a look. Do I understand you correctly, that if you use your own order-by it’s working, and if you sort-enable your report, instead of using order-by, it fails? And what are your sort sequence settings?
    Regards,
    Marc

  • 5. Re: Sort causing ORA-00911
    423655 Newbie
    Currently Being Moderated
    Yes, It will not sort using the built in htmldb sorting features, but order by works. I imported my application onto htmldb.oracle.com

    workspace: CRSI
    application: 18779
    Page: 39
    region: Abstractor Region/Cost Assignment.

    This region probably won't work as I didn't create all the tables I needed, but, you'll see what I am doing, and hopefully you will understand what's going on.

    Thanks,
    Scott
  • 6. Re: Sort causing ORA-00911
    Marc Sewtz Pro
    Currently Being Moderated
    Scott,
    That region contains an order-by – which you’re saying is working. I was more curious to see your report with column heading sorting enabled. It should be working just fine, enable the columns you want to order by, make the appropriate selections for sort sequence (at least one, and select only those columns that are actually sort enabled).
    Marc

  • 7. Re: Sort causing ORA-00911
    423655 Newbie
    Currently Being Moderated
    I am not quite sure what happened, but I removed the order bys and added the sort sequence I wanted and now it seems to work as I hoped. Weird, huh?
  • 8. Re: Sort causing ORA-00911
    Marc Sewtz Pro
    Currently Being Moderated
    Glad to hear that it works now, keep me posted if it beaks again,
    Marc
  • 9. Re: Sort causing ORA-00911
    445507 Newbie
    Currently Being Moderated
    Yeah, I'm just wondering if anyone figured out what was causing this. It's not a showstopper for me, but I find it a little strange.

    Scenario:

    I have an SQL report that does a couple of joins, pre-formatting, etc. The SQL also contains an "ORDER BY" clause. I add a derived column to the report, and immediately I start receiving ORA-00911. If I delete either the derived column or the "ORDER BY" clause, the report starts working again.

    I'm guessing this is something internal....
  • 10. Re: Sort causing ORA-00911
    279947 Newbie
    Currently Being Moderated
    Discovered if you have a semi-colon at the end of the select statement, then the sort options do not work.