How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Brian Tkatch on 8 Mar 2006 10:28 >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. Welcome to the non-MS/Windows world. Where you are expected to understand what your are doing. :) >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 A SELECT statement can be run outside a block of code, a DECLARE within. The difference is, SELECT is a "statement" and DECLARE is a "control statement". Both clearly delineated in SQL Reference Volume 2. Generally, the beginning of the documentate for a particular statement says when it can (and sometimes when it cannot) be executed. B.
From: Serge Rielau on 8 Mar 2006 11:03 Ian Boyd wrote: >> 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. I forgot OPEN :-) The interactive tools (like command center and CLP) have short hands for queries. That is when you type VALUES or SELECT interactively they will declare a cursor for you, open it, fetch all the rows and close. Then they pretty print the output. > 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? This is the root for SQL Procedure logic: http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004239.htm This is the root for the simpler command line scripting: http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004240.htm Here are the sentences for the GOTO statement (random example): "GOTO statement The GOTO statement is used to branch to a user-defined label within an SQL procedure. Invocation This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared." You implicitly raise an interesting point though. The SQL Reference is "dictionary" it is as little the right tool to learn the basics of SQL as any dictionary. You are used to MS SQL Server "Books Online" which is more of a guide. It describes what matters example driven). The DB2 SQL Ref is the _exact_ specification of DB2's SQL. There are plans to deliver a SQL Guide in a future release which will be more appropriate and have information such as which statement can be used where, and include scenario based examples. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Ian Boyd on 8 Mar 2006 11:20 5. Comments How to do comments in DB2-SQL? From: IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2 Chapter 2. Language Elements Tokens <quote> Comments Static SQL statements may include host language comments or SQL comments. Either type of comment may be specified wherever a space may be specified, except within a delimiter token or between the keywords EXEC and SQL. SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line. </quote> The following DB2-SQL fails: INSERT INTO Daily_Logs ( Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date, Description) VALUES ( 'DL-20060307-1', --DailyLogNumber 0, --CreatedByUserID 1, --PropertyID 1, --ShiftID "A74", --BayNumber 1, --SupervisorUserID 2, --LocationID CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls) CAST(NULL AS bigint), --ChecklistID (must manually cast nulls) 2, --DailyLogTypeID 5, --DailyLogSubTypeID '2006-03-01 11:11:07.11111', --StartDate '2006-03-01 11:21:18.22222', --EndDate CAST(NULL AS varchar(1)) --Description (must manually cast nulls) ); But if a take out the comments, it works. i have no keywords EXEC or SQL, and i am not putting my comments within a delimiter token "," since i cannot split a comma into two parts. *so tired*
From: Ian Boyd on 8 Mar 2006 11:41 > Welcome to the non-MS/Windows world. Where you are expected to > understand what your are doing. :) They have databases on computers these days. i know what i'm doing, and computers are powerful enough these days to know as well; or at least proceed with the only possible course of action. >>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 > > A SELECT statement can be run outside a block of code, a DECLARE > within. > > The difference is, SELECT is a "statement" and DECLARE is a "control > statement". Both clearly delineated in SQL Reference Volume 2. > > Generally, the beginning of the documentate for a particular statement > says when it can (and sometimes when it cannot) be executed. That explains why the reference doesn't include some statements, their not the right "kind" of statements. i see the majority of the reference is in a section called "Statements". i don't see a corresponding section of "control statements", nor is the keyword DECLARE in the index. Is there a Reference Volume 3 that documents the "control statements?" Are there are more kinds of statements? [Comicbook Guy] Umm, excuse me. Clearly select is a statement, and declare is a control statement. Thank you.
From: Ian Boyd on 8 Mar 2006 12:00
>select 1+2+3, 4+5+6 from sysibm.sysdummy1 1 2 ----------- ----------- 6 15 > SELECT 3.14159, 1+2+3, 4+5+6 1 2 3 -------- -------- -------- 3.14159 6 15 > You two got yor wires crossed.. Knut is talking about correlation names > (above the ----- line). > You are now talking about result types. ? i thought we were talking about correlation names and how they are still necessary even if there is only one column in the results set. |