Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 22 total |
CASTing AS VARCHAR |
Sun, Nov 29 2009 7:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I think this is a bug, but could be WAD. CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, returns a column 9 wide (well length(CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) shows 9) but I'm only shown 1 character (eg 1) If I use CAST(C._ID AS VARCHAR(6))+'/'+CAST(Site._SiteID AS VARCHAR(6)) I get the right result (eg 1000001/0) and length(CAST(C._ID AS VARCHAR(6))+'/'+CAST(Site._SiteID AS VARCHAR(6)) shows 9 Both C._ID and Site._SiteID are defined as integer. Messing around further and eyeballing the result what I think is happening is ElevateDB is adding the parameters for the VARCHARs and 1 for the constant and "trimming" the catenated result to fit. If I reduce the parameters as follows CAST(C._ID AS VARCHAR(4))+'/'+CAST(Site._SiteID AS VARCHAR(4)) I still get the full result of 1000001/0 I don't know if this is a special case but I definitely think its wrong. My view is if a parameter is there it applies within the CAST and if no parameter is there it should catenate the fields and work out the length from the result, a bit like it does if you join two VARCHARs together. Roy Lambert |
Mon, Nov 30 2009 9:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I think this is a bug, but could be WAD. CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, >> If you're running this in a query, then you need to make sure to specify the size of the VARCHARs in the CAST expressions. Not doing so will result in EDB just using 0 for the size of the resultant CAST expression. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Nov 30 2009 11:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< I think this is a bug, but could be WAD. > > CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, >> > >If you're running this in a query, then you need to make sure to specify the >size of the VARCHARs in the CAST expressions. Not doing so will result in >EDB just using 0 for the size of the resultant CAST expression. Not quite. You just need to specify ANY combined length that is equal to or greater than the size minus 1 (for the /) of the output for the expression as a whole. eg CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR(14)) AS Ref works fine. However, so does CAST(C._ID AS VARCHAR) as ID So individual elements work fine with no parameter its just in an expression Another example CAST(C._ID AS VARCHAR)+CAST(Site._SiteID AS VARCHAR(2)) AS Ref , determines the size to use from CAST(Site._SiteID AS VARCHAR(2)) Roy Lambert |
Tue, Dec 1 2009 9:42 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Not quite. >> Umm, I'm looking at the code right now, and it most definitely uses 0 as the length when you don't specify it in the VARCHAR data type. << You just need to specify ANY combined length that is equal to or greater than the size minus 1 (for the /) of the output for the expression as a whole. eg >> And, how exactly does that make what I said incorrect ? -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 1 2009 10:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Umm, I'm looking at the code right now, and it most definitely uses 0 as the >length when you don't specify it in the VARCHAR data type. > ><< You just need to specify ANY combined length that is equal to or greater >than the size minus 1 (for the /) of the output for the expression as a >whole. eg >> > >And, how exactly does that make what I said incorrect ? Because.... <<Not doing so will result in EDB just using 0 for the size of the resultant CAST expression.>> If your statement above is correct then CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, would have returned / only. What EDB is doing is using the aggregate sum of any parameters for any CAST AS VARCHAR expressions, adding in the length of any constants (call this MaxSpecified), formatting the integers (in this case) somehow, catenating the resultant expressions and returning the up to MaxSpecified characters in a column MaxSpecified wide. So your assertion is correct only if you consider that there is only ONE CAST expression in CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, and I count TWO Roy Lambert |
Wed, Dec 2 2009 12:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< If your statement above is correct then CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, would have returned / only. >> No. Again, what I said was: Not doing so will result in EDB just using 0 for the size of the resultant CAST expression. Notice I said CAST *expression*, not *expressions* (plural). Each CAST expression is going to have a length of 0. Thus the resultant expression length will be 1 (0+1+0), and that is what EDB will use for the length of the column in the result set. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Dec 2 2009 2:21 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< If your statement above is correct then > > CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, > > would have returned / only. >> > >No. Again, what I said was: > >Not doing so will result in EDB just using 0 for the size of the resultant >CAST expression. > >Notice I said CAST *expression*, not *expressions* (plural). Each CAST >expression is going to have a length of 0. Thus the resultant expression >length will be 1 (0+1+0), and that is what EDB will use for the length of >the column in the result set. This may just be a US vs UK interpretation but <<Not doing so will result in EDB just using 0 for the size of the resultant CAST expression.>> in my view, and using the above expression and assuming C._ID is 1000012, Site._SiteID is 0 would result in CAST(C._ID AS VARCHAR) produces zero length string CAST(Site._SiteID AS VARCHAR) produces zero length string so zero length string + / + zero length string NOT 1 What it seems to be doing as I said (and it may just be a language thing) is <<What EDB is doing is using the aggregate sum of any parameters for any CAST AS VARCHAR expressions, adding in the length of any constants (call this MaxSpecified), formatting the integers (in this case) somehow, catenating the resultant expressions and returning the up to MaxSpecified characters in a column MaxSpecified wide.>> This seems to be what your example is suggesting. Also consider this one create table xxx as select cast(_id as varchar) from companies with no data produces a table like CREATE TABLE "xxx" ( "Expression" VARCHAR(60) COLLATE "ANSI", ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 PUBLISH BLOCK SIZE 512 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768 Is this a special case? I have no idea where the 60 comes from. Roy Lambert |
Wed, Dec 2 2009 10:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< CAST(C._ID AS VARCHAR) produces zero length string CAST(Site._SiteID AS VARCHAR) produces zero length string so zero length string + / + zero length string NOT 1 >> 1 is the resulting length of the string, and 0+1+1 is the breakdown of where that length came from. They're the same thing. I'm really spending way too much time on this, so I'm going to just leave you with what I originally said: if you specify the length for the VARCHAR, then you won't have a problem. In fact, I'm going to make sure that not doing so produces an error in the future, just so I don't have to have this conversation again. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Dec 3 2009 3:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>I'm really spending way too much time on this, so I'm going to just leave >you with what I originally said: if you specify the length for the VARCHAR, >then you won't have a problem. In fact, I'm going to make sure that not >doing so produces an error in the future, just so I don't have to have this >conversation again. Having an error is preferable to how it currently works. .Are you also going to adjust it so that each CAST respects its own parameters or is it still going to be an aggregate for the compound expression?. Roy Lambert |
Thu, Dec 3 2009 6:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< 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. << Are you also going to adjust it so that each CAST respects its own parameters or is it still going to be an aggregate for the compound expression?. >> For the last time: The CAST function is not doing anything in aggregate with respect to the lengths of the VARCHARs. They couldn't even if they wanted to - they have absolutely zero knowledge of one another. The result set of a query is determined *before* the query is executed. Thus, EDB has *no idea* what the actual data will look like when the query is executed. It only uses the compiled version of the query's SELECT column expressions to determine what the lengths of the result set columns should be. Therefore, when it looks at your expression it sees: 0 + 1 + 0 It then sets the result column length to 1. Now, when the query is executed, any data that is longer than 1 character will get truncated. All you need to do is specify a length, say 10, and then EDB will then see: 10 + 1 + 10 and then set aside sufficient space in the result set column for the expression. That's it, simple as pie. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Thursday, May 23, 2024 at 03:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |