How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Jeroen van den Broek on 9 Mar 2006 17:14 "Ian Boyd" <ian.msnews010(a)avatopia.com> schreef in bericht news:duq5v901ssd(a)enews4.newsguy.com... >> So >> it boils down to question we always have with new functionality: Who's >> gonna pay for it and which feature should be moved back instead? > > Perhaps IBM should ask Oracle or MS if they can license their engines. > Ehm, what were you saying about 'walking a fine line' again... ;-) You really should try to start a thread like this on c.d.o.s., and watch what happens... -- Jeroen
From: Jeroen van den Broek on 9 Mar 2006 17:18 "Jeroen van den Broek" <jeroen(a)NOSPAM.demon.nl> schreef in bericht news:12119fi92089649(a)corp.supernews.com... > > "Ian Boyd" <ian.msnews010(a)avatopia.com> schreef in bericht > news:duq3tl01qqe(a)enews4.newsguy.com... >> 7. Casting number to a string >> >> <quote> >> CHAR >> The CHAR function returns a fixed-length character string representation >> of: >> An integer number, if the first argument is a SMALLINT, INTEGER, or >> BIGINT >> >> Note: The CAST expression can also be used to return a string expression. >> </quote> >> >> So of couse this works: >> SELECT CHAR(SomeBigIntColumnFieldArmadillo) FROM MyTable >> >> i would prefer to use CAST when doing all casts, and the documentation >> says >> i can. But this fails >> >> SELECT CAST(SomeBigIntColumnFieldArmadillo AS varchar(50)) FROM MyTable >> >> Error: SQL0461N >> A value with data type "SYSIBM.BIGINT" cannot be CAST to type >> "SYSIBM.VARCHAR". >> SQLSTATE=42846 >> (State:42846, Native Code: FFFFFE33) >> >> Any ideas? > > This Cast is not supported. > Have a look at the SQL Reference Vol1. Chapter 2 Language elements. > There is a paragraph called "casting between data types" (in the version > I'm reading now it starts on page 96). > It contains Table 8: "Supported Casts between Built-in Data Types". > And this is what the Message Reference has to say on this particular error message: --------------------------------------- SQL0461N A value with data type "<source-data-type>" cannot be CAST to type "<target-data-type>". Explanation: The statement contains a CAST with the first operand having a data type of "<source-data-type>" to be cast to the data type "<target-data-type>". This cast is not supported. User Response: Change the data type of either the source or target so that the cast is supported. For predefined data types these are documented in the SQL Reference. For a cast involving a user-defined distinct type, the cast can be between the base data type and the user-defined distinct type or from a data type that is promotable to the base data type to the user-defined distinct type. sqlcode: -461 --------------------------------------- -- Jeroen sqlstate: 42846
From: Ian Boyd on 9 Mar 2006 17:07 In SQL Server, just so you know what i'm basing it off of: CREATE TABLE #t2 (Weight real) --btw prefix # means temporary. i could also use a table variable insert into #t2 (Weight) VALUES (1) insert into #t2 (Weight) VALUES (1.1) insert into #t2 (Weight) VALUES (1.01) insert into #t2 (Weight) VALUES (1.001) insert into #t2 (Weight) VALUES (1.0001) insert into #t2 (Weight) VALUES (1.00001) insert into #t2 (Weight) VALUES (1.000001) insert into #t2 (Weight) VALUES (1.0000001) insert into #t2 (Weight) VALUES (1.00000001) insert into #t2 (Weight) VALUES (1.000000001) insert into #t2 (Weight) VALUES (1.0000000001) insert into #t2 (Weight) VALUES (1.00000000001) insert into #t2 (Weight) VALUES (1.000000000001) insert into #t2 (Weight) VALUES (1.0000000000001) insert into #t2 (Weight) VALUES (1.00000000000001) SELECT Weight, CAST(Weight AS varchar(255)) AS [Using CAST], CONVERT(varchar(255), Weight) AS [Using CONVERT with default formatting], CONVERT(varchar(255), Weight, 1) AS [Using CONVERT with 8 digits (scientific notation)], CONVERT(varchar(255), Weight, 2) AS [Using CONVERT with 16 digits (scientific notation)] FROM #t2 ORDER BY Weight DESC 1.1 1.1 1.1 1.1000000e+000 1.100000023841858e+000 1.01 1.01 1.01 1.0100000e+000 1.009999990463257e+000 1.001 1.001 1.001 1.0010000e+000 1.001000046730042e+000 1.0001 1.0001 1.0001 1.0001000e+000 1.000100016593933e+000 1.00001 1.00001 1.00001 1.0000100e+000 1.000010013580322e+000 1.000001 1 1 1.0000010e+000 1.000000953674316e+000 1.0000001 1 1 1.0000001e+000 1.000000119209290e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 1.0 1 1 1.0000000e+000 1.000000000000000e+000 May not be perfect, but 99.9% of the time it is all that i needed. Espeically for money amounts, weights, emperical values. The best i can come up with for DB2 so far is: select CAST(CHAR(DECIMAL(HoursSpent_TestField, 31, 7)) AS varchar(50)) from daily_logs 1 ------------------------------------- 000000000000000000000003.1415930 000000000000000000000001.0000000 000000000000000000000002.0000000 000000000000000000000003.0000000 000000000000000000000003.0000000 Tomorrow i will figure out how to do LTRIM and RTRIM away "0"
From: Jeroen van den Broek on 9 Mar 2006 17:28 "Jeroen van den Broek" <jeroen(a)NOSPAM.demon.nl> schreef in bericht news:12119fi92089649(a)corp.supernews.com... > > "Ian Boyd" <ian.msnews010(a)avatopia.com> schreef in bericht > news:duq3tl01qqe(a)enews4.newsguy.com... >> 7. Casting number to a string >> >> <quote> >> CHAR >> The CHAR function returns a fixed-length character string representation >> of: >> An integer number, if the first argument is a SMALLINT, INTEGER, or >> BIGINT >> >> Note: The CAST expression can also be used to return a string expression. >> </quote> >> >> So of couse this works: >> SELECT CHAR(SomeBigIntColumnFieldArmadillo) FROM MyTable >> >> i would prefer to use CAST when doing all casts, and the documentation >> says >> i can. But this fails >> >> SELECT CAST(SomeBigIntColumnFieldArmadillo AS varchar(50)) FROM MyTable >> >> Error: SQL0461N >> A value with data type "SYSIBM.BIGINT" cannot be CAST to type >> "SYSIBM.VARCHAR". >> SQLSTATE=42846 >> (State:42846, Native Code: FFFFFE33) >> >> Any ideas? > > This Cast is not supported. > Have a look at the SQL Reference Vol1. Chapter 2 Language elements. > There is a paragraph called "casting between data types" (in the version > I'm reading now it starts on page 96). > It contains Table 8: "Supported Casts between Built-in Data Types". > As you can see in that table, a Cast between BIGINT and CHAR is supported, as is a Cast between CHAR and VARCHAR, so you might try: SELECT CAST(CAST(SomeBigIntColumnFieldArmadillo AS char(50)) AS varchar(50)) FROM MyTable -- Jeroen
From: Will Honea on 10 Mar 2006 00:40
On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave(a)waveform.plus.com> wrote: > I'm pretty sure I've provided the syntax for this in a couple of other > comments, but maybe I didn't indicate it explicitly: > > SELECT COLA, COLB, COLC > FROM ( > VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) > ) AS TEMP(COLA, COLB, COLC) What is the semantic difference between your syntax and the form WITH TEMP(COLA,COLB,COLC) AS ( SELECT .... ) They appear equivilant - am I missing something? -- Will Honea |