Daniel Kessler wrote:
>
> CASE WHEN o.mname is not null THEN o.fname ELSE a.a_mname END as
> middlename. Then in the outter query (main query?), I substring it
> and concatenate it with a dot ( || '.').
> This works great on first and last name which are required and are
> sure to have something in either o.fname or a.a_fname. Middle name
> though is optional and may not have an entry. That code above says
> if there's a middle name in my people table (o), use it. If not, use
> whatever is in the authors table (a). But the author's table may be
> blank too. In that case, I don't want to concatenate a dot. I'd end
> up with entries like "Abrahms,P.." Originally I tried to add a if in
> the second part of the statement, after the THEN section. I couldn't
> figure that out, after lotsa cussin and itchin. So, I thought that
> maybe I could initialize middlename as '' and maybe that would be of
> some assistance.
How is a single variable going to help? If you were to use some
variables based approach, you would need one variable for each row of
the resultset, not one variable for the whole resultset.
If you just want to make sure the middlename is not NULL you simply need
to expand your CASE statement:
CASE
WHEN o.mname is not null THEN o.fname
WHEN a.a_mname is not null THEN a.a_mname
ELSE ' '
END as middlename
> Either way, I figured it would be good to at least know how to
> initialize variables in SQL for later use. Sounds like a basic,
> fundamental tool, right?
I use it next to never. SQL is about (row)sets, not about individual
variables.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277408
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
