Elevate Software

Login Login

ProductsBulletSalesBulletSupportBulletDownloadsBulletAbout



Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread

Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread computed fields, FullAddress (from StreetName1, StreetName2, City, PostalCode, Country)
Wed, Feb 8 2012 7:38 AMPermanent Link

Lucian

note: works when StreetName1 is declared as NOT NULL that's why is not checked

IF (StreetName2='' OR StreetName2 IS NULL THEN
 IF (City='' OR City IS NULL THEN
   IF (Province='' OR Province IS NULL THEN
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1
       ELSE
         StreetName1 + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + '  ' + PostalCode
       ELSE
         StreetName1 + '  ' + PostalCode + ', ' + Country
       )
     )
   ELSE
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + Province
       ELSE
         StreetName1 + ', ' + Province + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + Province + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + Province + '  ' + PostalCode + ', ' + Country
       )
     )
   )
 ELSE
   IF (Province='' OR Province IS NULL THEN
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + City
       ELSE
         StreetName1 + ', ' + City + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + City + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + City + '  ' + PostalCode + ', ' + Country
       )
     )
   ELSE
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + City + ', ' + Province
       ELSE
         StreetName1 + ', ' + City + ', ' + Province + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + City + ', ' + Province + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + City + ', ' + Province + '  ' + PostalCode + ', ' + Country
       )
     )
   )
 )
ELSE
 IF (City='' OR City IS NULL THEN
   IF (Province='' OR Province IS NULL THEN
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + StreeName2 + '  ' + PostalCode + ', ' + Country
       )
     )
   ELSE
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + Province
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + Province + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + Province + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + Province + '  ' + PostalCode + ', ' + Country
       )
     )
   )
 ELSE
   IF (Province='' OR Province IS NULL THEN
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + City
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + City + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + City + '  ' + PostalCode + ', ' + Country
       )
     )
   ELSE
     IF (PostalCode='' OR PostalCode IS NULL THEN
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + ', ' + Country
       )
     ELSE
       IF (Country='' OR Country IS NULL THEN
         StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + '  ' + PostalCode
       ELSE
         StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + '  ' + PostalCode + ', ' + Country
       ))))) AS FullAddress
Wed, Feb 8 2012 10:02 AMPermanent Link

John Hay

Alternatively, Coalesce is your friend - presuming postalcode must be preceded by a space and your target field is 200
wide

SUBSTRING(COALESCE(IF(StreetName1<>'',' '+StreetName1,null),'')+
COALESCE(','+IF(StreetName2<>'',StreetName2,null),'')+
COALESCE(','+IF(City<>'',City,null),'')+
COALESCE(','+IF(Province<>'',Province,null),'')+
COALESCE(' '+IF(PostalCode<>'',PostalCode,null),'')+
COALESCE(','+IF(Country<>'',Country,null),''),2,200)

John

<Lucian> wrote in message news:46948857-4BBD-4FAD-8077-1A5CF7BFD221@news.elevatesoft.com...
> note: works when StreetName1 is declared as NOT NULL that's why is not checked
>
> IF (StreetName2='' OR StreetName2 IS NULL THEN
>   IF (City='' OR City IS NULL THEN
>     IF (Province='' OR Province IS NULL THEN
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1
>         ELSE
>           StreetName1 + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + '  ' + PostalCode
>         ELSE
>           StreetName1 + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     ELSE
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + Province
>         ELSE
>           StreetName1 + ', ' + Province + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + Province + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + Province + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     )
>   ELSE
>     IF (Province='' OR Province IS NULL THEN
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + City
>         ELSE
>           StreetName1 + ', ' + City + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + City + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + City + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     ELSE
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + City + ', ' + Province
>         ELSE
>           StreetName1 + ', ' + City + ', ' + Province + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + City + ', ' + Province + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + City + ', ' + Province + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     )
>   )
> ELSE
>   IF (City='' OR City IS NULL THEN
>     IF (Province='' OR Province IS NULL THEN
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + StreeName2 + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     ELSE
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + Province
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + Province + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + Province + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + Province + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     )
>   ELSE
>     IF (Province='' OR Province IS NULL THEN
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + City
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + City + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + City + '  ' + PostalCode + ', ' + Country
>         )
>       )
>     ELSE
>       IF (PostalCode='' OR PostalCode IS NULL THEN
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + ', ' + Country
>         )
>       ELSE
>         IF (Country='' OR Country IS NULL THEN
>           StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + '  ' + PostalCode
>         ELSE
>           StreetName1 + ', ' + StreeName2 + ', ' + City + ', ' + Province + '  ' + PostalCode + ', ' + Country
>         ))))) AS FullAddress
>

Wed, Feb 8 2012 10:34 AMPermanent Link

Lucian

>Coalesce is your friend

Ah, another MIA "friend" of mine it seems. Guys, you should post tricks like this more often, especially for non-SQL background guys like me. Stuff like these doesn't strike me when I do SQL, I still think Pascal and try to convert like, well, yeah, like a dummy.

Thanks Smile
Lucian
Wed, Feb 8 2012 11:35 PMPermanent Link

David Cornelius

Cornelius Concepts

Avatar

Print out the SQL manual and stick it under your pillow at night.

Or, just print out gobs of code here on extensions and let us tell you how
much shorter it could be!!  Wink

David Cornelius
Cornelius Concepts
Thu, Feb 9 2012 2:58 AMPermanent Link

Lucian

>Or, just print out gobs of code here on extensions and let us tell you how
much shorter it could be!!  Wink

Oh, don't wait for me. You could post I supposed tons of little freaking tricks that would make all dummies happy.
After all it's been what, 4, 5 years since ElevateDB got out? It was nice if it was much more code here already...
Thu, Feb 9 2012 4:13 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Lucian


I'm partly with David on this one. What I would say coming to ElevaeDB from DBISAM is "learning SQL is a pain in the backside".

Most of the stuff you posted was nice little utility routines. I'll be adding some of them to my toolkit. However, this one is highly specific, and I'm guessing others will be as well, and don't really belong in the extensions ng (or an SQL tips if one is ever created).

Roy Lambert

ps - you might want to use this

CREATE FUNCTION "RCF" (IN "In1" VARCHAR COLLATE ANSI, IN "In2" VARCHAR COLLATE ANSI, IN "Separator " VARCHAR COLLATE ANSI)
RETURNS VARCHAR COLLATE ANSI
BEGIN
DECLARE Output VARCHAR;

SET Output = COALESCE(In2,'');

IF (In1 IS NOT NULL) AND (In1 <> '') THEN
IF (In2 IS NOT NULL) AND (In2 <> '') THEN
 SET Output = Output + Separator + In1;
 ELSE SET Output = In1;
END IF;
END IF;

RETURN Output;

END

DESCRIPTION 'Reverse combine fields'
VERSION 1.00


Usage would be

RCF(RCF(RCF(RCF(Country,PostalCode,','),Province,','),City,','),StreetName,',')

I think - I always have to test it.
Thu, Feb 9 2012 4:31 AMPermanent Link

Lucian

Hi Roy,

Actually the thing is you don't want a function here, it's clearly for computed fields which can't use those functions (I think, with my EDB version, cuz I'm not on the latest). I remeber when I had to build something like FullAddress computed field I tried using my AddToken func, but was rejected, so I had to resort to that painfull to read crap.

Yeah, COALLESCE and INTERVAL are some things that bug me, I don't seem to like them well when I see them in code. IF THEN ELSE is a lot more clear than COALLESCE(a,b,c). When I see COALLESCE I always, I mean always have to go to the Index help and see which is which, and that's counterproductive for me. Plus, avoiding COALLESCE and INTERVAL makes stuff more portable, maybe, I don't know for sure, I just assume.


This web page was last updated on
Thursday, April 18, 2024 at 08:02 AM

Privacy PolicyBulletSite Map

© 2024 Elevate Software, Inc. All Rights Reserved
Questions or comments ? E-mail E-mail us at info@elevatesoft.com

Image