![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 20 of 22 total |
![]() |
Thu, Dec 3 2009 8:04 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
><< Having an error is preferable to how it currently works. >> > >How it works now is perfectly reasonable, but you aren't understanding it >for some reason. I believe I am. I've posted a screen shot to the binaries illustrating what I think is a problem. You don't see it as such, and its possible that either of both of us is misunderstanding the other. To reiterate if I cast an integer as a varchar, and I supply a parameter I would expect the integer to be truncated eg CAST(1000023 AS VARCHAR(4)) should return 1000 If I then catenate that to a constant (eg A) I would expect the result to be 1000A If I then extended it to CAST(1000023 AS VARCHAR(4)) +'A'+ CAST(1000023 AS VARCHAR(4)) I would expect 1000A1000 In fact I get 1000023A1 I also get the same result if I use CAST(1000023 AS VARCHAR) +'A'+ CAST(1000023 AS VARCHAR(8)) I cannot believe this is correct. Is it? Nor can I believe that create table xxx as select cast(_id as varchar) from companies with no data where _id is an integer field should create a 60 wide column. Admittedly I don't know what width it should use but that's another discussion. Roy Lambert |
Thu, Dec 3 2009 8:20 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Whilst making a cuppa I thought "I wonder how CHAR behaves". You might want to try this select CAST(1000023 AS CHAR) +'A'+ CAST(1000023 AS CHAR(8)), CAST(1000023 AS CHAR(0)) +'A'+ CAST(1000023 AS CHAR(8)), CAST(1000023 AS CHAR(0)), CAST(1000023 AS CHAR) from companies Substituting any table of your own. I would describe the result as "interesting" Roy Lambert ps I accept that CAST AS CHAR(0) is not something I'm ever liable to want to do in real life |
Thu, Dec 3 2009 9:52 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< If I then extended it to CAST(1000023 AS VARCHAR(4)) +'A'+ CAST(1000023 AS VARCHAR(4)) I would expect 1000A1000 In fact I get 1000023A1 >> Okay, now I see what you're having an issue with. It's the same reason that you can use VARCHAR without a length, i.e. the CAST is not truncating values, only using the length as an indicator of the expected length of the expression so that the result set columns are defined properly. I'm not sure if this is something that I want to change in a build, however, because it will break any existing application that relies on it doing what it does now, i.e. no truncation. I might have to wait until 2.04 for such a breaking change. << where _id is an integer field should create a 60 wide column. Admittedly I don't know what width it should use but that's another discussion. >> If the length of a SELECT expression is 0, then EDB uses a default length of 60. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Dec 3 2009 11:31 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Eureka we have achieved communication ![]() >Okay, now I see what you're having an issue with. It's the same reason that >you can use VARCHAR without a length, i.e. the CAST is not truncating >values, only using the length as an indicator of the expected length of the >expression so that the result set columns are defined properly. I'm not >sure if this is something that I want to change in a build, however, because >it will break any existing application that relies on it doing what it does >now, i.e. no truncation. I might have to wait until 2.04 for such a >breaking change. This is why I kept on referring to aggregating the parameters. It could even be left as is providing that its covered adequately in the manual, preferably with a few worked examples so that it won't come as a surprise to those who RTFM (ie about 0.1% of your customer base ![]() ><< where _id is an integer field should create a 60 wide column. Admittedly >I don't know what width it should use but that's another discussion. >> > >If the length of a SELECT expression is 0, then EDB uses a default length of >60. Well its a number, and as I said "that's another discussion" Out of interest what do you think of the CHAR output? Roy Lambert |
Thu, Dec 3 2009 12:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Out of interest what do you think of the CHAR output? >> I didn't really even look at it - the rules are the same for both CHAR and VARCHAR, and the only difference is the padding that comes with CHAR columns. Changing one will change the other accordingly. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Dec 3 2009 1:35 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>I didn't really even look at it - the rules are the same for both CHAR and >VARCHAR, and the only difference is the padding that comes with CHAR >columns. Changing one will change the other accordingly. You may be right, but program code is sneaky - have a look at the new jpg in the binaries. Roy Lambert |
Thu, Dec 3 2009 1:54 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< You may be right, but program code is sneaky - have a look at the new jpg in the binaries. >> Please, be more specific and stop making me guess. I don't have a lot of time on my hands right now. What exactly do you see that you think isn't correct ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Dec 3 2009 2:19 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Please, be more specific and stop making me guess. I don't have a lot of >time on my hands right now. What exactly do you see that you think isn't >correct ? I spent a lot of time trying to be more specific with the last related issue, you spent a lot of time refuting my comments, until I finally stumbled across a form of words that you understood. Please look at the jpg. If after 15 seconds you don't see what I'm talking about I'll happily spend as much time as is needed. Roy Lambert |
Thu, Dec 3 2009 2:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Please look at the jpg. If after 15 seconds you don't see what I'm talking about I'll happily spend as much time as is needed. >> No thanks, I'm tired of playing games. If you won't specify what the problem is, then I can't help you. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Dec 4 2009 4:06 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
><< Please look at the jpg. If after 15 seconds you don't see what I'm >talking about I'll happily spend as much time as is needed. >> > >No thanks, I'm tired of playing games. If you won't specify what the >problem is, then I can't help you. I resent being told I'm playing games. I put in a tremendous amount of effort trying to get the message across to you on the behaviour of CAST and VARCHAR. Was that a game? I invest more time (not much this time fortunately) to produce a query which clearly shows a difference in behaviour with CAST between VARCHAR and CHAR which you say is the same but which the jpg I posted shows isn't only to be told I'm playing games. Where is your evidence for this? The differences may be due to padding as you say. I don't have the code so I don't know. That's why I produced a query showing the results and posted a jpg to the binaries to show it. The idea was to save both you and me time, not to play games. Roy Lambert |
« Previous Page | Page 2 of 3 | Next Page » |
Jump to Page: 1 2 3 |
This web page was last updated on Thursday, July 4, 2024 at 06:12 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |