![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 21 to 30 of 32 total |
![]() |
Wed, Jun 1 2011 2:59 AM | Permanent Link |
Adam H. | Hi Roy,
Ahh thanks for that. Maybe I shouldn't have simplified my example for the newsgroups. ![]() One way I've thought about doing it is to use the replace command, but it would be huge... Replace('A' WITH '' IN Replace ('B' WITH '' IN Replace ('C' WITH '' IN ..... etc) Allowing for every character (symbols too) would make for one super long SQL statement. Maybe I'm better off going back to a real field and doing the calculation back in my app? Cheers Adam. |
Wed, Jun 1 2011 3:29 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Adam
>Ahh thanks for that. Maybe I shouldn't have simplified my example for >the newsgroups. ![]() True. SO what is the real requirement? >One way I've thought about doing it is to use the replace command, but >it would be huge... > >Replace('A' WITH '' IN Replace ('B' WITH '' IN Replace ('C' WITH '' IN >.... etc) > >Allowing for every character (symbols too) would make for one super long >SQL statement. > >Maybe I'm better off going back to a real field and doing the >calculation back in my app? I refuse to comment until I see the actual requirement ![]() Roy Lambert [Team Elevate] |
Wed, Jun 1 2011 7:00 PM | Permanent Link |
Adam H. | > I refuse to comment until I see the actual requirement
![]() LOL - the actual requirement is still the same. To sort by Numeric order. What I neglected to say (honestly it didn't even cross my mind) is that I want to allow for the user to set whatever prefix they want. (And have the option to change). So the prefix might be "A" or "ABC" - a variable length. I'm truely sorry about neglecting this... it didn't even cross my mind until I went to implement it. ![]() |
Thu, Jun 2 2011 2:49 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Adam
>LOL - the actual requirement is still the same. To sort by Numeric >order. What I neglected to say (honestly it didn't even cross my mind) >is that I want to allow for the user to set whatever prefix they want. >(And have the option to change). > >So the prefix might be "A" or "ABC" - a variable length. > >I'm truely sorry about neglecting this... it didn't even cross my mind >until I went to implement it. ![]() I have posted a solution which will work until there are more than 6 alphas as a prefix. Roy Lambert [Team Elevate] |
Thu, Jun 2 2011 9:58 AM | Permanent Link |
John Hay | Roy
> > I'm not sure how complex the code in a column definition can be, I do know it can't use a user defined function. As I have time I'll play around and see if I can come up with something. Alternatively if John Hay is feeling bored...... > OK you tempted me ![]() Imperfect I'm sure but seems to work. Use a function to find the position of the number CREATE FUNCTION "getnumber" (IN "infld" VARCHAR(100) ) RETURNS INTEGER BEGIN declare fld varchar(100); declare i integer; declare found boolean; set fld=rtrim(infld); set i = length(fld); set found = false; if (i > 0) and ((substr(fld,i,1) >= '0') and (substr(fld,i,1) <= '9')) then repeat if ((substr(fld,i,1) >= '0') and (substr(fld,i,1) <= '9')) then set i = i-1; else set found=true; end if; until found or (i < 1) end repeat; set i = i+1; else set i = 0; end if; if i > 0 then set i = cast(substring(fld,i,20) as integer); end if; return i; END Use a generated column in the table to hold the number. ALTER TABLE "TABLENAME" Add COLUMN "Number" INTEGER GENERATED ALWAYS AS getnumber(id); John |
Thu, Jun 2 2011 10:35 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | John
Fascinating. I'm especially gobsmacked by the fact that a generated column can use the function but a computed one can't. I hadn't realised there was a difference or if Tim had told me I'd forgotten. Brilliant as always. Roy Lambert |
Thu, Jun 2 2011 11:08 AM | Permanent Link |
John Hay | Roy
> Fascinating. I'm especially gobsmacked by the fact that a generated column can use the function but a computed one can't. I hadn't realised there was a difference or if Tim had told me I'd forgotten. > I was surprised too. I'm sure there is a good reason - maybe standards <ducks> John |
Thu, Jun 2 2011 1:33 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | John
>I was surprised too. I'm sure there is a good reason - maybe standards <ducks> I prefer chicken or turkey Roy Lambert |
Thu, Jun 2 2011 4:26 PM | Permanent Link |
Charalampos Michael | Hello Guys!
>> Worth a shot I suppose. But my line of thinking tells me that computed >> fields won't be indexed as they're created on the fly. Doing a select / >> sort on a non-indexed field in my previous table could take 20+ seconds >> to return a result. (There's a bit of data in there). > > You can create an index on a computed field. I assume that in that case the real data is stored in the index. Think back to dBase and calculated indices. > > Roy Lambert [Team Elevate] Well, there a super easy way. Tim should add "StrCmpLogicalW" sorting support ![]() http://msdn.microsoft.com/en-us/library/bb759947%28v=vs.85%29.aspx |
Thu, Jun 2 2011 11:34 PM | Permanent Link |
Adam H. | Hi John,
Incredible! Thanks for that. I see that there's a lot more to EDB than meets the eye! Cheers Adam. |
« Previous Page | Page 3 of 4 | Next Page » |
Jump to Page: 1 2 3 4 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |