Ken Kixmoeller/fh wrote:
>> Select
>> Case ColumnOrVariable
>> when ThisCondition then ThisValue
>> when ThatCondition then ThatValue
>> etc. end as MyOutput
>>
>
>
> Yes, that is what I was working on. Here is what I have so far:
>
> CASE
> WHEN length(address2)=0 AND length(mailcode)=0 THEN address1
> WHEN length(address2)=0 AND length(mailcode)>0 THEN concat
> (address1,'^',mailcode)
> WHEN length(address2)>0 AND length(mailcode)=0 THEN concat
> (address1,'^',address2)
> WHEN length(address2)>0 AND length(mailcode)>0 THEN concat
> (address1,'^',address2,' - ',mailcode)
> ELSE '' END as `concataddr`,
>
> Able to use the length() function for both empty and null data
> conditions, so that shortens it a lot.
>
>
>> So why didn't you present your VFP code and we would have T-SQLed
>> it for you.
>>
>
> It wasn't VFP; SQL all the way. Sometimes I use VFP to test SQL,
> though. I still find it easier to visualize my data when I actually
> can *see* it. <g> If it was VFP, I'd just RW the cursor and do a few
> replace statements. Ah, well, the statement above wasn't as bad as I
> thought it would be...
>
Don't know if I understand the issue here (I've just skimmed over the
thread, sorry) but (untested) :
SQLServer
select address1 + IsNull('^'+rtrim(ltrim(address2)), '') +
IsNull(' - '+rtrim(ltrim(mailcode)), '')
Fox
select address1 + NVL('^'+alltrim(address2), '') + NVL(' -
'+alltrim(mailcode), '')
Keep in mind that ('something' + Null ) == Null and that AllTrim(.Null.)
== .Null.
HTH
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
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/[email protected]
** 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.
