ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieterorder by case when col1 is null then 1 else 0 end, col1|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be show
n
as whatever u want that will be at the end of the list?

ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieter
order by case when col1 is null then 1 else 0 end, col1
|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be shown
as whatever u want that will be at the end of the list?
sql

ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieterorder by case when col1 is null then 1 else 0 end, col1|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be shown
as whatever u want that will be at the end of the list?

Oracle empty string == NULL behavior in SQLServer 2k5?

I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.

Can this be done? I couldn't find a setting for it.

Thanx

PeterAs far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''|||OrSELECT * FROM theTable WHERE ISNULL(field, '') = ''|||As far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''

Yep, thanx but we got this far. We were hoping to avoid typing this. Maybe some system wide setting... but I guess we'll have to learn to do it the MS way. :)|||It is less doing things the MS way and more not doing things the Oracle way. I didn't know you could do that in PL\SQL but it sure as 'ecky thump isn't part of the ANSI standard.

Anyway - a lot of people just prevent nulls from their database in the first place.


CREATE TABLE MyTable
(
MyCol VARCHAR(10) DEFAULT '' NOT NULL
)

HTH|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.This issue has been discussed a lot ;)
I think it's perfectly OK.|||The war in Iraq has been discussed a lot too. I guess that makes it OK as well?

A zero-length string is distinctly different that a null value. Anyone who equates the two does not understand the concept of a null value. A null value is "unknown" or "undetermined". A zero-length string has a known value. How can you possibly say that a known value is equal to some value that is not known? You can't. Hence, Oracle's implementation is flawed.|||zustimmung|||Anyone who equates the two does not understand the concept of a null valueI do understand the concept of a null value ;)

A zero-length string has a known value.And what is this "value"?
Can you have a similar distinction for a numeric column or a date column? (i.e. an "empty" value which is not null?)

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".

When it comes to other data types then nobody seems to want this distinction between an "empty" value and a null value. So what's so special about a character value that makes it different from other data types?|||The value of an empty string is 'empty'. The value of null is 'unknown'. There is a difference. (For one thing, one unknown value cannot be determined to be equal to another unknown value but 'empty' is definitely equal to 'empty'.)

FWIW our standard is to avoid nulls whenever an appropriate default value can be determined. Our standard default character value is the empty string so Oracle's null handling usually works for us on a practical level-but it's not ideal & definitely not ANSI-compliant. It'd be nice if you could turn that behavior on & off according to the database. Mostly I like defaulting nulls to empty strings & having a database setting for it would eliminate mistakes-but sometimes I *don't* want nulls to default to empty strings. Then, I'd like to be able to turn that 'automatic default' off.|||The value of an empty string is 'empty'. The value of null is 'unknown'.Then why doesn't anybody need this concept (empty vs. unknown) for non-character data types?
In my(!) opinion there is no difference between empty and unknown.|||I do understand the concept of a null value ;)
Ok maybe you do understand the concept of a null value. I'll give you the benefit of the doubt for now.

And what is this "value"?Zzzzzzt! Doubt removed!

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".Zzzzzzt again! A null value means you don't have data for it. A zero-length string means there is no data for it.

In my(!) opinion there is no difference between empty and unknown.

Then your opinion is wrong.

Here is an empty box: []
Here is a box which may be empty, but which may contain $1,000,000: []

Would you say the two are of equal value? Would you trade the box who's contents are unknown for the box which is known to be empty?

Well? Deal or no deal?

Do not feel bad. You are not alone. The concept of a NULL value is strangely difficult to grasp for many people. A common post of the SQL Server forums is something like "How come when I execute SELECT * FROM TABLE WHERE COLUMNVALUE = NULL I don't get any rows returned?"|||The concept really isn't empty vs. unknown, it's 'known' vs. unknown. Empty string just happens to be a convenient default (known) value for character data. The concept exists for non-character data too, it's just more difficult figuring out the appropriate default value. What's the appropriate default for Boolean?

Oracle Acquire Connection for ssis return null

Hi All!

I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...

I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I tried

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand

could you help me plz?

Using "as" will return NULL if the object can't be cast to the specified type. Is the connection manager actually an Oracle connection?|||

Yes, the connection manager is an Oracle Connection.
I use .Net Providers --> OracleClient Data Provider....

|||

I find the problem: I reference the class Oracle.DataAccess.Client and Oracle.DataAccess.Type and the 'conn' is null...

If I reference the System.Data.OracleClient all is right!

But I must use the Oracle.DataAccess... :-(

Has anybody use it yet?

|||

What is Oracle.DataAccess.Client? Is it an ADO.NET provider? If it is an ADO.NET provider you need to use an appropriate connection type that comes with it. Or you should be able to use generic connection (DbConnection) object.

HTH.

|||

Check the type of the object being returned, as there is some cinfusion as to what you have done here. Perhaps some code like -

object test = cmAdo.AcquireConnection(transaction);

Debug.Assert(false, test.Type.ToString());

I would also look at the ConnectionManagerType property for the connection, as that shoudl also tell you the type of ADo.Net connection you can expect back. See th example value below, showing that I used the MS ADO.Net oracl provider, and the class I know is System.Data.OracleClient.OracleConnection, found in the assemby System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

ADO.NETystem.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Perhaps you have not used a managed provider, but the .Net OleDb provider to connect to an Oracle OLE-DB provider. In which case your connection manager type would be -

ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089