Re: How to do...well...any thing...in DB2 SQL
<posted & mailed>
J. Moreno wrote:
[color=blue]
> Knut Stolze <stolze@de.ibm. com> wrote:[color=green]
>> planb@newsreade rs.com wrote:
>>[/color]
> -snip-[color=green][color=darkred]
>> > 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).[/color]
>>
>> 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 ...[/color]
>
> 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[/color]
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.
[color=blue]
> as well as
>
> WITH params(p1, p2) AS
> ( SELECT '1', '2' )
> SELECT 'a' AS answer FROM params[/color]
This can obviously not work because you have a syntax error in the common
table expression. There is no valid SELECT statement specified there.
[color=blue]
> 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><cha rstring<graphst ring>".
> SQLSTATE=46201 -- from report services when I run it against the DB2
> database).[/color]
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
<posted & mailed>
J. Moreno wrote:
[color=blue]
> Knut Stolze <stolze@de.ibm. com> wrote:[color=green]
>> planb@newsreade rs.com wrote:
>>[/color]
> -snip-[color=green][color=darkred]
>> > 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).[/color]
>>
>> 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 ...[/color]
>
> 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[/color]
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.
[color=blue]
> as well as
>
> WITH params(p1, p2) AS
> ( SELECT '1', '2' )
> SELECT 'a' AS answer FROM params[/color]
This can obviously not work because you have a syntax error in the common
table expression. There is no valid SELECT statement specified there.
[color=blue]
> 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><cha rstring<graphst ring>".
> SQLSTATE=46201 -- from report services when I run it against the DB2
> database).[/color]
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
Comment