Re: SQL query re unions and nulls
Ian,
select a, b, c, d, "" colx from x ...
should give you a null. If you are truly desperate and this
doesn't work, try putting this into a temp table, as in
select a, b, c, d, "" colx
from x
union
select a, b, c, "", e
from x;
The only requirement is that the columns in your union are the
same datatypes (but you probably know this). The null string
should work both for character and numeric fields.
Yours,
Nick
*********************************
Nick Nobbe, Library of Congress
NLS/BPH
mail: nnob@loc.gov
*********************************
On Tue, 9 Sep 1997, Ian Treadgold wrote:
} I'd really appreciate some help with this particular SQL problem I've
} been landed with. I need to write two selects joined by a union to
} deliver separate subsets of columns.
}
} Consider a table x with columns a,b,c,d,e all of which are type
} integer. Depending on various entries in the where clauses, I need
} columns a,b,c,d from the first select and a,b,c,e from the second but
} I need all five columns in total.
}
} What I need is something like the following:
}
} select a,
} b,
} c,
} d,
} null
} from x
} where ....
}
} union
}
} select a,
} b,
} c,
} null,
} e
} from x
} where ...
}
} The problem is how to specify a literal null - if the fields where
} char(n) then I could live with blanks or even two quotes together but
} that won't work for numeric type fields.
}
} One last thing, I need a solution that will work on Informix and
} Sybase through ODBC.
}
} Thanks in advance if anyone can help
}