How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Knut Stolze on 8 Mar 2006 02:14 Ian Boyd wrote: >> There are no "fields" in SQL - just rows, columns and values. > You channeling Celko? :) No, not really. I just want to make sure that you know the terminology used by DB2 (and the SQL standard) so that you will have an easier time when looking at the manual. > 3. Returning a value >>> SQL Server: >>>>> SELECT @@spid AS ProcessID >>>>> or >>>>> DECLARE @ProcessID int >>>>> SET @ProcessID = @spid >>>>> SELECT @ProcessID >>>> >>>> Again, use VALUES. >>> Again, i assume that VALUES is a bad thing: >> How so? > > Because rather than using VALUES: >> SET ApplicationID = VALUES Application_ID(); > i don't use VALUES: >> SET ApplicationID = Application_ID(); That doesn't make VALUES a bad thing. It is just not necessary here. > 5. Setting a field (a.k.a column) to NULL > > SQL Server >> UPDATE MyTable SET SomeField = NULL >> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3) Works. > or > >> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >> VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184') This works too. > or > >> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system > > In my variant of the 3rd case in DB2, it complains that "NULL is not valid > in the context where it is used." This does not work because NULL is untyped and DB2 does not know if the data types produced by the subselect match with the data types in the table. So try this: INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) SELECT fname, lname, addr1, CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10)) FROM legacy_system Btw, you got the error SQL0206N in this case. You should first look up the error description to correct your problem: ----------------------------------------------------------- $ db2 "? sql0206" SQL0206N "<name>" is not valid in the context where it is used. Explanation: This error can occur in the following cases: o For an INSERT or UPDATE statement, the specified column is not a column of the table, or view that was specified as the object of the insert or update. o For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement. o For an ORDER BY clause, the specified column is a correlated column reference in a subselect, which is not allowed. o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION statement: - The reference "<name>" does not resolve to the name of a column, local variable or transition variable. - The condition name "<name>" specified in the SIGNAL statement has not been declared. o For a CREATE TRIGGER statement: - A reference is made to a column of the subject table without using an OLD or NEW correlation name. - The left hand side of an assignment in the SET transition-variable statement in the triggered action specifies an old transition variable where only a new transition variable is supported. o For a CREATE FUNCTION statement with a PREDICATES clause: - The RETURN statement of the SQL function references a variable that is not a parameter or other variable that is in the scope of the RETURN statement. - The FILTER USING clause references a variable that is not a parameter name or an expression name in the WHEN clause. - The search target in an index exploitation rule does not match some parameter name of the function that is being created. - A search argument in an index exploitation rule does not match either an expression name in the EXPRESSION AS clause or a parameter name of the function being created. o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH clause or the FILTER USING clause references a variable that is not a parameter name that can be used in the clause. The statement cannot be processed. User Response: Verify that the names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause. For a subselect in an ORDER BY clause, ensure that there are no correlated column references. If a correlation name is used for a table, verify that subsequent references use the correlation name and not the table name. For a CREATE TRIGGER statement, ensure that only new transition variables are specified on the left hand side of assignments in the SET transition-variable statement and that any reference to columns of the subject table have a correlation name specified. sqlcode : -206 sqlstate : 42703 ----------------------------------------------------------- Unfortunately, this particular situation is not explained explicitly. So you can only derive that NULL is interpreted as column name. (Note that DB2 allows a column to be named NULL.) -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Knut Stolze on 8 Mar 2006 02:25 Ian Boyd wrote: > i've never used any idea similar to > (SELECT 5 UNION SELECT 6) AS X > > i've never had a problem where i needed to join to such virtual table. I would not call this a "virtual" table. It is a table (like views are tables). The major issue here is where the data of the table comes from. In this case, the table is constructed on the fly. > Like i said before, i'll have to figure out where VALUES would be useful > to me. > >> This T-SQL: SELECT @@spid AS ProcessID >> is NOT column aliasing. > > If i run this query, i get a record set with one row and one column. And > that column has no name. If i want to give that column a name (or a > different name) i have to use: > >> SELECT @@spid AS ProcessID > > So the column gets renamed from "" to "ProcessID." Note that DB2 names expressions that are returned from a query itself (it numbers them) if they don't have an explicit name. That's why you see the "1" or "2" in the following output: $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" 1 2 ----------- ----------- 6 15 1 record(s) selected. Same thing here: $ db2 "values ( 1+2+3, 4+5+6 )" 1 2 ----------- ----------- 6 15 1 record(s) selected. And those "1" or "2" can be used in a Java application when you fetch the data from the result set by column name. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Ian Boyd on 8 Mar 2006 09:10 > $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" > > 1 2 > ----------- ----------- > 6 15 > > 1 record(s) selected. > > > Same thing here: > > $ db2 "values ( 1+2+3, 4+5+6 )" > > 1 2 > ----------- ----------- > 6 15 > > 1 record(s) selected. > > And those "1" or "2" can be used in a Java application when you fetch the > data from the result set by column name. Unless someone changed the query to SELECT 3.14159, 1+2+3, 4+5+6 1 2 3 -------- -------- -------- 3.14159 6 15 1 record(s) selected. And now all the application logic has to be rewritten.
From: Serge Rielau on 8 Mar 2006 10:01 Ian Boyd wrote: >> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" >> >> 1 2 >> ----------- ----------- >> 6 15 >> >> 1 record(s) selected. >> >> >> Same thing here: >> >> $ db2 "values ( 1+2+3, 4+5+6 )" >> >> 1 2 >> ----------- ----------- >> 6 15 >> >> 1 record(s) selected. >> >> And those "1" or "2" can be used in a Java application when you fetch the >> data from the result set by column name. > > Unless someone changed the query to > SELECT 3.14159, 1+2+3, 4+5+6 > > 1 2 3 > -------- -------- -------- > 3.14159 6 15 > > 1 record(s) selected. > > And now all the application logic has to be rewritten. > > You two got yor wires crossed.. Knut is talking about correlation names (above the ----- line). You are now talking about result types. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Ian Boyd on 8 Mar 2006 09:58
> Procedural statements are not supported as independent statements by DB2. > > That is you can do: > CREATE..., DROP.., GRANT, REVOKE, ALTER > DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from > CLP) > UPDATE,DELETE, INSERT, MERGE > CALL > BEGIN ATOMIC .. END On some level i wondered if that was how DB2 did it. But i convinced myself that such a scheme is just too complicated to be true, and i assumed that i had some fundamental misunderstanding about what is going on. Is there a complete list somwhere of what i can and cannot run? Specifically, i'm concerned about the "implied" SELECT and VALUES. My confusion comes from the fact that sometimes i can just run SELECT * FROM ...., and other times i have to declare a cursor for a select, and then leave the cursor open. i don't see OPEN in your list. In fact, a quick check of the IBM DB2 Universal Database SQL Reference Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you point me to some references on this? |