DB2 Stored Procedure - Passing Parameter Issues
in [DB2]
|
Prev: ADM0500E The DB2 Service does not have the necessary authority to complete the command
Next: New Auth Agents on V9.1 FP1 on UNIX
From: Lennart on 24 Mar 2010 13:37 On 24 mar, 03:16, wfs <wfs1...(a)gmail.com> wrote: > On Mar 23, 10:05 pm, "Mark A" <no...(a)nowhere.com> wrote: > > > > > "wfs" <wfs1...(a)gmail.com> wrote in message > > >news:5869647e-35be-4f93-ad0b-c84a1c69b352(a)i25g2000yqm.googlegroups.com.... > > > > Hi All, > > > > db2 stored procedure - passing parameters issue > > > > static sql within the procedure > > > > where char_account_no in ( :ws-acct-list ) > > > > if pass 123457 it returns the correct result for the single account > > > > if pass '1234567' it returns nothing > > > > if pass '1234567','2345678' it returns nothing. > > > > Anyone have any idea of the syntax to pass multiple accounts #'s. > > > > something like '1234567','2345678','3456789' > > > I am assuming this is a numeric field such as INT, SMALLINT, BIGINT, etc > > > The Standard SQL syntax for that would be > > > where char_account_no in (1234567,2345678,3456789), but you are trying pass > > them as one parm defined as char or varchar.- Hide quoted text - > > > - Show quoted text - > > Hi Mark, > sorry if I didn't make it clear it's a CHARACTER field - > 'char_account_no' > > in db2 developer workbench (jdbc driver), if I enter a 7 character > value without quotes - it finds the data. Putting quotes around it > (either single or double) causes no data to be found. > > Calling it from Dot Net using the odbc / cli driver gives the > same result. > > If I execute the sql directly via SPUFI, using the format; > > where char_account_no in ( '1234567' , '2345678' , '3456789' ) - > works fine > > - so I presume it must be something to do with a host variable > being expected to be a single value.... > > - sounds like I have to convert it to dynamic sql and do prepare / > execute etc > > The account list can be several thousand long.... so I don't want > to code > > where char_account_no in > ( :act01, :act02, :act03 etc.... ) > > Thanks > > Bill
From: Lennart on 24 Mar 2010 13:41 On 24 mar, 18:37, Lennart <erik.lennart.jons...(a)gmail.com> wrote: > On 24 mar, 03:16, wfs <wfs1...(a)gmail.com> wrote: > > > On Mar 23, 10:05 pm, "Mark A" <no...(a)nowhere.com> wrote: > > > > "wfs" <wfs1...(a)gmail.com> wrote in message > > > >news:5869647e-35be-4f93-ad0b-c84a1c69b352(a)i25g2000yqm.googlegroups.com.... > > > > > Hi All, > > > > > db2 stored procedure - passing parameters issue > > > > > static sql within the procedure > > > > > where char_account_no in ( :ws-acct-list ) > > > > > if pass 123457 it returns the correct result for the single account > > > > > if pass '1234567' it returns nothing > > > > > if pass '1234567','2345678' it returns nothing. > > > > > Anyone have any idea of the syntax to pass multiple accounts #'s. > > > > > something like '1234567','2345678','3456789' > > > > I am assuming this is a numeric field such as INT, SMALLINT, BIGINT, etc > > > > The Standard SQL syntax for that would be > > > > where char_account_no in (1234567,2345678,3456789), but you are trying pass > > > them as one parm defined as char or varchar.- Hide quoted text - > > > > - Show quoted text - > > > Hi Mark, > > sorry if I didn't make it clear it's a CHARACTER field - > > 'char_account_no' > > > in db2 developer workbench (jdbc driver), if I enter a 7 character > > value without quotes - it finds the data. Putting quotes around it > > (either single or double) causes no data to be found. > > > Calling it from Dot Net using the odbc / cli driver gives the > > same result. > > > If I execute the sql directly via SPUFI, using the format; > > > where char_account_no in ( '1234567' , '2345678' , '3456789' ) - > > works fine > > > - so I presume it must be something to do with a host variable > > being expected to be a single value.... > > > - sounds like I have to convert it to dynamic sql and do prepare / > > execute etc > > > The account list can be several thousand long.... so I don't want > > to code > > > where char_account_no in > > ( :act01, :act02, :act03 etc.... ) > > > Thanks > > > Bill Oops, hit the send button by mistake :-). Don't know if it applies to you platform, but if it exists you might want to have a look at global temporary tables. In short you insert all account_no's in such a table and then use a join against it in the procedure, just a thought. /Lennart
From: --CELKO-- on 24 Mar 2010 22:27 I hope that you know better than to put the data type as part of the data element name! People will think you are a 1960's BASIC programmer who never read ISO-11179 rules. Parameters are scalar and only one data type in SQL, not lists, arrays, pointer chains or other fancy things. That is why 123457 returns the correct result for the single account. The string '1234567' it returns nothing because it has bad quote marks. The string '1234567','2345678' it returns nothing it has bad quote marks *and* makes no sense in SQL. >> Anyone have any idea of the syntax to pass multiple accounts #'s. << One really bad way is to write a parser in one query that dissembles the string into a one column table expression. I know it is a bad way -- I wrote one of the first versions of it in a newsgroup and put it in one of my books many years ago! Slightly better, you can use dynamic SQL, which is very costly, It is better to load a table with all the constraints you need on the account numbers -- like a validating the check digits, range, removing redundant duplicates, joining them to other data, etc. >> The account list can be several thousand long.... so I don't want to code WHERE account_nbr IN (:act01, :act02, :act03 etc.... ) << Actually, don't knock it. Would you flinch at a 1000 element array in a procedural language? DB2 can handle up to 32K parameters and do all the argument validation for you. A text editor can write an insanely long parameter list for you instantly. My rule of thumb is to stop at 100 parameters and use the working table. No real reason for that magic number. I have an sudoku solver written by Richard Romley in ONE query that takes 81 parameters -- Flies like a bat out of hell in SQL Server.
From: Tonkuma on 3 Apr 2010 13:48 If you passed ws_acct_list parameter in a comma separated format (like '1234567,2345678,3456789'), you can extract each account by using recursive common-table- expression. Here is an example... WITH find_separater( p , n ) AS ( SELECT 1 , 0 FROM sysibm.sysdummy1 UNION ALL SELECT COALESCE( NULLIF( LOCATE( ',' , ws_acct_list , p ) , 0 ) , LENGTH(ws_acct_list) + 1 ) + 1 , n + 1 FROM find_separater WHERE n < 100000 AND p <> LENGTH(ws_acct_list) + 2 ) SELECT ... FROM ... WHERE char_account_no IN (SELECT SUBSTR( ws_acct_list , p1 , p2 - p1 - 1 ) FROM find_separater s1( p1 , n1 ) , find_separater s2( p2 , n2 ) WHERE n2 = n1 + 1 AND p2 > p1 + 1 )
From: Tonkuma on 3 Apr 2010 21:22
Although, I guessed you were using DB2 for z/OS, if you used DB2 for LUW, you can make table UDF(s), like this... Use of the table UDF. SELECT ... FROM ... WHERE char_account_no IN (SELECT item FROM TABLE ( get_item( :ws-acct-list ) ) AS t ) ; CREATE FUNCTION get_item ( item_list VARCHAR(32000) , separator CHAR(1) ) RETURNS TABLE( item VARCHAR(254) ) READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION RETURN WITH find_separater( p , n ) AS ( VALUES ( 1 , 0 ) UNION ALL SELECT COALESCE( NULLIF( LOCATE( separator , item_list , p ) , 0 ) , LENGTH(item_list) + 1 ) + 1 , n + 1 FROM find_separater WHERE n < 100000 AND p <> LENGTH(item_list) + 2 ) SELECT SUBSTR( item_list , p1 , p2 - p1 - 1 ) FROM find_separater fs1( p1 , n1 ) , find_separater fs2( p2 , n2 ) WHERE n2 = n1 + 1 AND p2 > p1 + 1 ; CREATE FUNCTION get_item ( item_list VARCHAR(32000) ) RETURNS TABLE( item VARCHAR(254) ) READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION RETURN SELECT item FROM TABLE( get_item( item_list , ',' ) ) ; |