Combining columns in sql.

Commissar

Is there any way to merge 2 columns in sql. I'm using LEFT JOIN to combine the 2 tables which leaves a number of blank cells. I'ld like to use a value in the first table in those cells of the resulting table Date column.
I tried renaming a column but I get Date and Date1 back and I'ld prefer to avoid using a new column for the sake of those few few entries.

Also, a related question, I'ld like to put default text in the blank cells of another column of the resulting table.

Can anybody suggest a possible solution?

liamo

If by "Blank" you mean "Null", then
Select IsNull(<SomeField>, '01/01/2000') as Date
will return the value in SomeField unless it's NULL in which case '01/01/2000' will be returned in a column called "Date"


Regards,

Liam

bonkey

Commissar wrote:

Is there any way to merge 2 columns in sql.

Sure. It depends on what you're merging and what you consider merging to mean, though.

I'm using LEFT JOIN to combine the 2 tables which leaves a number of blank cells. I'ld like to use a value in the first table in those cells of the resulting table Date column.

IsNull is your friend here too.

The basic usage is:
IsNull(SomeField, SomeOtherFieldToUseOnlyWhenSomeFieldIsNull)

Unfortunately, this is DB-specific. Going from Memory, in Oracle and DB2, the function is actually called NVL. MSSQL, Sybas both use IsNull. No idea about mySql, sorry.

In MSSQL, there's an even more powerful field called COALESCE. THis can be given effectively any number of parameters (256 max?) and basically it goes from left-to-right and returns teh first non-null value from amongst them.

So IsNull is just a simplified 2-parameter COALESCE

Also, a related question, I'ld like to put default text in the blank cells of another column of the resulting table.

As the previous poster suggested, IsNull / Nvl comes to the rescue again. Instead of a field in the second parameter, you just stick your default value. Remember it should be / has to be the smae datatype, so you don't want to do :

IsNull(SomeIntegerField, "Empty")

jc

Can anybody suggest a possible solution?[/QUOTE]

Commissar

ISNULL did the trick. Thanks for the help Liamo and Bonkey.