How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Knut Stolze on 6 Apr 2006 06:07 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). If your actual SQL statement is a query, you could use a common table expression to have just the original two parameter markers: WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT)) SELECT ... FROM <table>, parms WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ... -- Knut Stolze DB2 Information Integration Development IBM Germany
From: J. Moreno on 6 Apr 2006 13:02 Knut Stolze <stolze(a)de.ibm.com> wrote: > planb(a)newsreaders.com wrote: > -snip- > > 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). > > If your actual SQL statement is a query, you could use a common table > expression to have just the original two parameter markers: > > WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT)) > SELECT ... > FROM <table>, parms > WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ... That sounds good -- but I can't get it to work.... Trying to turn the above into a simple query that works, I tried WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('2' AS INT) ) ) SELECT 'a' AS answer FROM params as well as WITH params(p1, p2) AS ( SELECT '1', '2' ) SELECT 'a' AS answer FROM params neither works. I get the same error message for both querys (Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'. -- from Query Analyzer, and "An unexpected token "(" was found following '''. Expected tokens include: "IS <hexstring><charstring<graphstring>". SQLSTATE=46201 -- from report services when I run it against the DB2 database). -- J. Moreno
From: Knut Stolze on 7 Apr 2006 03:55 <posted & mailed> J. Moreno wrote: > Knut Stolze <stolze(a)de.ibm.com> wrote: >> planb(a)newsreaders.com wrote: >> > -snip- >> > 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). >> >> If your actual SQL statement is a query, you could use a common table >> expression to have just the original two parameter markers: >> >> WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT)) >> SELECT ... >> FROM <table>, parms >> WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ... > > That sounds good -- but I can't get it to work.... > > Trying to turn the above into a simple query that works, I tried > > WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('2' AS INT) ) ) > SELECT 'a' AS answer FROM params This works quite nicely: $ db2 "WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('' AS INT) ) ) SELECT 'a' AS answer FROM params" ANSWER ------ a 1 record(s) selected. > as well as > > WITH params(p1, p2) AS > ( SELECT '1', '2' ) > SELECT 'a' AS answer FROM params This can obviously not work because you have a syntax error in the common table expression. There is no valid SELECT statement specified there. > neither works. I get the same error message for both querys (Server: Msg > 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'. > -- from Query Analyzer, and "An unexpected token "(" was found following > '''. Expected tokens include: "IS <hexstring><charstring<graphstring>". > SQLSTATE=46201 -- from report services when I run it against the DB2 > database). What's the exact error message? I'd suspect that something else besides DB2 is screwing things up. Maybe you're not using the IBM ODBC/CLI driver? -- Knut Stolze DB2 Information Integration Development IBM Germany
From: J. B. Moreno on 7 Apr 2006 14:04 Knut Stolze <stolze(a)de.ibm.com> wrote: > J. Moreno wrote: > > > Knut Stolze <stolze(a)de.ibm.com> wrote: -snip- > >> If your actual SQL statement is a query, you could use a common table > >> expression to have just the original two parameter markers: > >> > >> WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT)) > >> SELECT ... > >> FROM <table>, parms > >> WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ... > > > > That sounds good -- but I can't get it to work.... > > > > Trying to turn the above into a simple query that works, I tried > > > > WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('2' AS INT) ) ) > > SELECT 'a' AS answer FROM params > > This works quite nicely: > > $ db2 "WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('' AS > INT) ) ) SELECT 'a' AS answer FROM params" > > ANSWER > ------ > a > > 1 record(s) selected. -snip- > What's the exact error message? A cut/paste of your code gives this error message: An error occurred while executing the query. DB2[SQL0104N] An unexpected token "(" was found following '''. Expected tokens may include: "IS <HEXSTRING><CHARSTRING><GRAPHSTRING>". SQLSTATE=42601 If I switch to the Microsoft ODBC driver, I get this error message: An error occurred while executing the query. ERROR[42401][IBM][CLI Driver][DB2]SQL0104N An unexpected token "(" was found following ''''. Expected tokens may include: "IS <HEXSTRING><CHARSTRING><GRAPHSTRING>". SQLSTATE=42601 > I'd suspect that something else besides DB2 is screwing things up. Maybe > you're not using the IBM ODBC/CLI driver? I've tried the "IBM OLE DB Provider for DB2 Servers" and the "Microsoft OLE DB Provider for ODBC Drivers". Too bad I can't get this to work (it'd be ideal for the query that I'm working on, which involves two subquery's that are almost identical). -- J. Moreno
From: Serge Rielau on 7 Apr 2006 18:22
J. B. Moreno wrote: > Knut Stolze <stolze(a)de.ibm.com> wrote: > >> J. Moreno wrote: >> >>> Knut Stolze <stolze(a)de.ibm.com> wrote: > -snip- >>>> If your actual SQL statement is a query, you could use a common table >>>> expression to have just the original two parameter markers: >>>> >>>> WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT)) >>>> SELECT ... >>>> FROM <table>, parms >>>> WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ... >>> That sounds good -- but I can't get it to work.... >>> >>> Trying to turn the above into a simple query that works, I tried >>> >>> WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('2' AS INT) ) ) >>> SELECT 'a' AS answer FROM params >> This works quite nicely: >> >> $ db2 "WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('' AS >> INT) ) ) SELECT 'a' AS answer FROM params" >> >> ANSWER >> ------ >> a >> >> 1 record(s) selected. > -snip- >> What's the exact error message? > > A cut/paste of your code gives this error message: > An error occurred while executing the query. > DB2[SQL0104N] An unexpected token "(" was found following '''. Expected > tokens may include: "IS <HEXSTRING><CHARSTRING><GRAPHSTRING>". > SQLSTATE=42601 > > If I switch to the Microsoft ODBC driver, I get this error message: > An error occurred while executing the query. > ERROR[42401][IBM][CLI Driver][DB2]SQL0104N An unexpected token "(" was > found following ''''. Expected tokens may include: "IS > <HEXSTRING><CHARSTRING><GRAPHSTRING>". SQLSTATE=42601 > >> I'd suspect that something else besides DB2 is screwing things up. Maybe >> you're not using the IBM ODBC/CLI driver? > > I've tried the "IBM OLE DB Provider for DB2 Servers" and the > "Microsoft OLE DB Provider for ODBC Drivers". > > Too bad I can't get this to work (it'd be ideal for the query that I'm > working on, which involves two subquery's that are almost identical). > J.B., What platform are you on. Knut is referring to DB2 for LUW. DB2 for iSeries or zOS do not support WITH clause yet. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |