Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread |
Messages 1 to 7 of 7 total |
computed fields, FullAddress (from StreetName1, StreetName2, City, PostalCode, Country) |
Wed, Feb 8 2012 7:38 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 Lucian |
Wed, Feb 8 2012 11:35 PM | Permanent Link |
David Cornelius Cornelius Concepts | 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!! David Cornelius Cornelius Concepts |
Thu, Feb 9 2012 2:58 AM | Permanent Link |
Lucian | >Or, just print out gobs of code here on extensions and let us tell you how
much shorter it could be!! 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |