This discussion is archived
3 Replies Latest reply: Nov 22, 2012 11:35 AM by Udo RSS

Null first by default ?

576227 Newbie
Currently Being Moderated
Hello,

Is there any way to tell Oracle 10g XE to place the null values first when sorting asc ?

This is to avoid having to explicitly use the nulls first clause in the select sql statements and allowing Hibernate to be agnostic on the issue.

The goal is to have the Hibernate domain class mappings and DAO select sql statements not tied to the database server specifics.

Kind Regards,
  • 1. Re: Null first by default ?
    Udo Guru
    Currently Being Moderated
    Hello,

    use something like
    select col
    from tab
    order by col asc NULLS FIRST
    or LAST, respectively...

    -Udo
  • 2. Re: Null first by default ?
    576227 Newbie
    Currently Being Moderated
    Thanks for the suggestion, but I knew that already. I would like Hibernate and therefore the web application, to be agnostic on the database server being in use, that is, not to have any specific related to a database server. The nulls first works on Oracle but not in other database servers so I'm reluctant to go that way. Is there any way to tell Oracle to behave "nulls first" with some general preference I could activate for all the schema tables ?
  • 3. Re: Null first by default ?
    Udo Guru
    Currently Being Moderated
    Thanks for the suggestion, but I knew that already.
    You could have told us that... ;)
    The nulls first works on Oracle but not in other database servers so I'm reluctant to go that way.
    I don't think this is true. The NULL-handling is part of the ANSI:2003 extension of SQL. I agree that this extension is not implemented in all databases, but I'd adjust your statement to "but not in all other database servers".
    Is there any way to tell Oracle to behave "nulls first" with some general preference I could activate for all the schema tables ?
    How would that not be Oracle-specific? Or do all other databases offer such a feature?
    But apart from that, I don't know of any option like that. And others using Hibernate to neither, as you've probably found out yourself as well. If not, take a look at the [url https://hibernate.onjira.com/browse/HHH-465]corresponding discussion.
    Finally, if you really have to get a cross-platform system running, try to get rid of NULL-values for columns you need for sorting, either by database design or by "dirty tricks" similar to the ones named in the hibernate thread referenced before.

    -Udo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points