![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » Search Forums » Search Results » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Wed, Dec 2 2009 10:15 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | This is what I'm currently doing COALESCE('Staff: ' + S._Staff,'')+ IF(T._Turnover IS NOT NULL AND S._Staff IS NOT NULL,', ','') + COALESCE('T/O: ' + T._Turnover,'') AS Size, Any suggestions to do it better? It works, may be the most efficient way, but just doesn't look very elegant, especially when there are three or four fields involved. Roy Lambert |
Wed, Dec 2 2009 12:29 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy, << This is what I'm currently doing COALESCE('Staff: ' + S._Staff,'')+ IF(T._Turnover IS NOT NULL AND S._Staff IS NOT NULL,', ','') + COALESCE('T/O: ' + T._Turnover,'') AS Size, Any suggestions to do it better? It works, may be the most efficient way, but just doesn't look very elegant, especially when there are three or four fields involved. >> Does it work ? If so, then you're fine. In case you haven't noticed, SQL in general isn't very "elegant". ![]() -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Dec 2 2009 2:26 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim >Does it work ? If so, then you're fine. In case you haven't noticed, SQL >in general isn't very "elegant". ![]() As I said, it works, and yes I have noticed ![]() Roy Lambert |
Fri, Dec 4 2009 8:27 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Still not massively elegant but better ![]() RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS Company, RCF('T/O: ' + T._Turnover,'Staff: ' + S._Staff,',') AS Size, 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 THEN IF In2 IS NOT NULL THEN SET Output = Output + Separator + In1; ELSE SET Output = In1; END IF; END IF; RETURN Output; END DESCRIPTION 'Reverse combine fields' Best I can come up with until I can supply a variable number of arguments to a function. Roy Lambert |
Fri, Dec 4 2009 8:37 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim Minor problem with this approach. ElevateDB can no longer figure out the size the column should be and uses 60 so I guess I'll have to CAST(RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS VARCHAR(102) AS Company, or is there a better way? Roy Lambert |
Sun, Dec 6 2009 7:10 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy, << Minor problem with this approach. ElevateDB can no longer figure out the size the column should be and uses 60 so I guess I'll have to CAST(RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS VARCHAR(102) AS Company, or is there a better way? >> The best way is to declare the length in the RETURNS clause for the function. If you omit the length, then EDB will not be able to figure out what the length is at compile time, so you should stick with functions, etc. that omit the length when they are going to be used primarily in SQL/PSM expressions (IF, CASE, WHILE, etc.) or WHERE/JOIN clauses and specify the length when the functions are used primarily in SELECT expressions. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, June 22, 2024 at 05:51 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |