How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 9 Mar 2006 15:17 > 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 * > FROM ( > VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) > ) AS TEMP(COLA, COLB, COLC) > > The "AS" bit is mandatory and provides a name for the table produced by > VALUES. The field aliases in brackets after AS TEMP are optional, but > allow you to assign names to the columns in the VALUES table, which can > then be referenced in the enclosing SELECT statement as above. Though I > could just as easily have done: Thank you for that. i do remember seeing the post, but i thought that the COLA, COLB, COLC were placeholders for something...i dunno. i didn't appreciate that it is the way to give names to the columns created by VALUES. VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but SQL Server should have something like it - even if i can't think of good uses for it right now :)
From: Ian Boyd on 9 Mar 2006 15:31 > Argh! I was scratching my head wondering how anyone could be so crazy Don't put it past me :) >> > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an >> > INSERT statement anyway. Let me just try it: >> You do - sometimes. >> INSERT INTO TestTable (Name, Address, Phone) >> SELECT fname, addr1, NULL FROM Customers > Damn, I'd forgotten about that particular construction. Bam! Boo-yeah! > Fair point ... probably a better strategy for someone new to DB2 and > just wishing to "get on with it". That's the sentiment DB2 needs to ponder on for a little while. > Pick an arbitrary data type... Certainly a viable option, but I'm > beginning to wonder... i mean, it has two choices: go ahead and just do what we all know i was trying to do, or refuse. No harm in db2 being nicer and easier. > <tangent> > The more I look at SQL... > ...makes me wonder... > ...whether it wouldn't > be a good idea to steal an idea from functional programming.. > </tangent> i originally learned SQL as ANSI-SQL from a very good book explaining SQL. It wasn't expousing ANSI-SQL over other flavors, just teaching SQL. SQL is, to me, a very intuitive thing, and playing with the examples from the book originally in SQL Server 6.5 almost 10 years ago, it was very easy to use. i am all in favor of adding to SQL intuitive extensions. > Actually, I'm not entirely sure VALUES is a DB2 "innovation" ... it > could just be standard SQL that DB2's implemented and which other > databases have ignored. Anyone know for sure? i've never seen it through SQL Server 2000. But i hear that MSSQL has been playing ANSI catchup for the last few years - not that not begin fully ANSI-compliant is deal-breaker - just bragging rights. > Granted, I should qualify that over-generalizing statement: "In SQL > everything is a table ... except when it's not" :-) Goes back to the "you know what i wanted to do, so why are you fighting me on this" sentiment. > The third statement, however, is problematic. It's not going to work > because it doesn't evaluate to a constant or special register. That's > the primary reason it won't work, but there's another aspect to it that > begs comment: FETCH FIRST n ROWS It was a contrived example, but one that shows how things are not always consistent. > I recommend several therapeutic sessions of fragging friends and > colleagues in Quake III (honestly, why this isn't considered mandatory > for the mental well-being of office workers is beyond me :-) Right now it's WoW.
From: Ian Boyd on 9 Mar 2006 15:42 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?
From: Ian Boyd on 9 Mar 2006 16:17 > 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.
From: Jeroen van den Broek on 9 Mar 2006 17:02
"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". -- Jeroen |