How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Knut Stolze on 13 Mar 2006 04:39 Dave Hughes wrote: > Will Honea wrote: > >> 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? > > The WITH ... construct is a "common table expression" which was syntax > introduced in the SQL-99 standard. Functionally, both your version and > my version above are identical. Not quite the same - there are some very small differences that you usually don't have to worry about. The common table expression is guaranteed to be evaluated before the SELECT statement. Also, the CTE is evaluated exactly once. The DB2 optimizer is not given a choice to copy the CTE in several branches of the statement if it thinks that would be better. This is usually only relevant for non-deterministic/external action stuff. Besides that, CTEs are identical to the sub-select. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Serge Rielau on 13 Mar 2006 07:16 Knut Stolze wrote: > Dave Hughes wrote: > >> Will Honea wrote: >> >>> 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? >> The WITH ... construct is a "common table expression" which was syntax >> introduced in the SQL-99 standard. Functionally, both your version and >> my version above are identical. > > Not quite the same - there are some very small differences that you usually > don't have to worry about. The common table expression is guaranteed to be > evaluated before the SELECT statement. Also, the CTE is evaluated exactly > once. The DB2 optimizer is not given a choice to copy the CTE in several > branches of the statement if it thinks that would be better. This is > usually only relevant for non-deterministic/external action stuff. > > Besides that, CTEs are identical to the sub-select. > > What you describe there are not the CTEs I know. The rules fro non determinism are teh same for derived tables, views and CTE. Order of execution is only honored for CTE if there is a reason to honor them (like SELECT FORM INSERT/UPDATE/DELETE). Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Knut Stolze on 13 Mar 2006 07:19 Serge Rielau wrote: > What you describe there are not the CTEs I know. The rules fro non > determinism are teh same for derived tables, views and CTE. > Order of execution is only honored for CTE if there is a reason to honor > them (like SELECT FORM INSERT/UPDATE/DELETE). Oh, all right. The SQL standard does make this distinction and I assumed it applies to DB2 as well. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: planb on 5 Apr 2006 16:14 Brian Tkatch wrote: > >So let's start with some simple SQL constructs, that i know so very well in > >SQL Server, that seem to be like pulling teeth in DB2. -snip- > >1. Declaring a variable > > > >SQL Server: > > DECLARE @SavedUserID int > > The command(s) completed successfully. > > Drop the useless @ symbol, and end all statement with a semi-colon. > Also, there is no implicit block of code, so you must start your own. > > BEGIN > DECLARE SaverUserID INT; > END I've got the same problem -- I'm trying to use the reporting services in visual studio against a DB2 database, and I need to pass in a couple of parameters. I've gotten this to work, using unnamed parameters (a ? instead of @whatever), but I need to use the parameter in two different places, and I don't want to have 4 parameters. It seems that a variable would be the ideal solution, but how do I declare it? BEGIN DECLARE theP INT; END SELECT Count(*) FROM theTable doesn't work -- "[DB2] SQL0199N The use of the reserved word "THEP" following ''' is not valid." So, perhaps I ned to have my select inside the BEGIN END.... But I get the exact same error... So, how do I declare the variable so that DB2 will accept it? (Sorry for the late reply, but I read through the thread and didn't find an answer I understood).
From: Serge Rielau on 5 Apr 2006 16:46
planb(a)newsreaders.com wrote: > Brian Tkatch wrote: >>> So let's start with some simple SQL constructs, that i know so very well in >>> SQL Server, that seem to be like pulling teeth in DB2. > -snip- >>> 1. Declaring a variable >>> >>> SQL Server: >>> DECLARE @SavedUserID int >>> The command(s) completed successfully. >> Drop the useless @ symbol, and end all statement with a semi-colon. >> Also, there is no implicit block of code, so you must start your own. >> >> BEGIN >> DECLARE SaverUserID INT; >> END > > I've got the same problem -- I'm trying to use the reporting services > in visual studio against a DB2 database, and I need to pass in a couple > of parameters. I've gotten this to work, using unnamed parameters (a ? > instead of @whatever), but I need to use the parameter in two different > places, and I don't want to have 4 parameters. It seems that a > variable would be the ideal solution, but how do I declare it? > > BEGIN > DECLARE theP INT; > END > > SELECT Count(*) FROM theTable > > doesn't work -- "[DB2] SQL0199N The use of the reserved word "THEP" > following ''' is not valid." > > So, perhaps I ned to have my select inside the BEGIN END.... > But I get the exact same error... > > So, how do I declare the variable so that DB2 will accept it? > > (Sorry for the late reply, but I read through the thread and didn't > find an answer I understood). > DB2 does not support global variables. No ifs, no butts. Either you use parameter markers, or you have to use a procedure/function. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |