Re: newbie: SQL join/union question

Mark,

      You've got the right idea with the UNION, just follow through with it. Since there must be a one-to-one correspondence of the columns in the select statements, just provided it yourself. In your example you would put the literal asterisk as a third column in the second select.

SELECT seqid, name, info from NAME1
UNION
SELECT seqid, name, '*' from NAME2;

The column label for the '*' will come from the first select statement.

Another point you brought up in you post was about being able to tell if a field contained a null value. This is useful in both parts of the statement. There is a function that returns a value if the expression is null, it's NVL (null value).

SELECT seqid, name, nvl(info,'Law Abiding Citizen') info from NAME1 UNION
SELECT seqid, name, nvl('*','Law Abiding Citizen') from NAME2;

Hope this helps,
Mike Stout Received on Tue Feb 09 1999 - 00:01:18 CST