Post reply

Null and 0 in Order by Clause

  • August 20, 2006 at 2:59 am

    #114209

    Hello

    I have a huge query that make mathematical transformation on database.

    All works fine except the order by clause.

    I am using Left Join in this query and therefore it is possible that part of fields will be null.What i need is actually that sql server will use null=0 when items are sorted,and in real i get null<0.

    How can i resolve this issue?

    Thanks and best regards

    Oifa Yulian

  • August 20, 2006 at 4:01 am

    #655787

    You can use "isnull(column_name,0)" in the order clause but using a function in the order or join clause kills the indexes you know.

  • August 20, 2006 at 5:04 am

    #655789

    Hello Zubeyir and thanks for your reply.

    The problem with isNull is that there is something like 100 possible columns in final result that can be done , and * instead of specific name is used.Therefore it will take a lot of text to repair the query.Therefore i am looking for different way , that will allows me to leave * in query while still changing the order by behaviour.I guess there is some way to change default on server , but can not find it.

    Best regards

    Oifa Yulian

  • August 20, 2006 at 6:48 am

    #655791

    Where I meant to put the isnull clause was the "order by" of your query. I think it won't be too difficult to change only that part. In case I understand you wrong you can post your query to here.

    SELECT*
FROMCustomers
ORDER BY ISNULL(ID, 0)

    Addionally there is a connection based setting that you may use as "SET ANSI_NULLS" but that does not meet your needs.

    Regards

    Zubeyir

  • August 20, 2006 at 7:35 am

    #655794

    10x man , did not know that can put isnull in Order by.

  • August 20, 2006 at 8:29 am

    #655800

    You are welcome, enjoy it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply