OK, I'm working on this in a SQL statement (MySQL):
Have 3 fields from 2 tables which need to be combined to make up a
single field address field in an export file. The three fields are:
- location.address1
- location.address2
- person_location.mailcode
The mail_code belongs to the instance of person's belonging to a
place, not the place. Different people in the same facility have
different mailcodes. Good normalization, IMO. (Besides, not possible
to change the DB design even if you disagree.)
The format of the field in the export is this:
address1^address2, but only if address2 is populated, in which case
address1 without the ^
Since I don't have any place to put the mailcode, I'll concatenate it
to address2. However, as in everything in this crazy-a$$ business we
are in, it isn't so simple, as any of the three could be populated,
empty, or NULL. In MySQL, if you try to concatenate a string and any
part of it is NULL, the result is NULL.
So, before dealing with the Null thing, I had:
select
if(length(concat(address,mailcode`))=0,
address1,
concat(address1,'^',address2`,'-',mailcode`))
as concat_addr,
<rest of it>
I am working on a CASE statement as we 'speak', but there seem to be
so frickin' many possibilities, that I am having a hard time wrapping
my head around a single statement to encompass the possibilities.
before I drive myself crazier, am I missing a more obvious approach?
I could have had this done in about 12 seconds if I had a good-
ol' (but much maligned) VFP cursor...
Ken
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/1b44a49c-6a7c-47a1-99cb-c11ff7d0d...@information-architecture.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
