Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2

    Answered: Pass Multiple Values from a Variable (sample code provided)

    I am adjusting from Rapid SQL on a Sybase Server to DB2. This standard approach works well when passing multiple values assigned to a variable with a ", " separating each value in that variable:

    In this case I am looking for the "product_code" that has one of the values in that variable. In this example I want to pull product_code A or B or C.
    Code:
    declare 
    @variable varchar(100)
    
    select @variable = 'a,b,c'
    
    select distinct * from table 1
    where 
    (@variable = "*ALL"  or charindex(ltrim(rtrim(product_code))+",", ltrim(rtrim(@variable))+",") > 0)

    I understand now how to use the Begin Atomic method for variable such as this but struggling with the equivalent approach from T-SQL to DB2 to read each variable value separated by a comma if I were to add one to this piece of code. Would a REPLACE function work somehow or an index value?:

    Code:
    drop table SESSION.DAILY_READ_REP;
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
        TMP_TIME            TIMESTAMP(6),
        TMP_ORDER_ID        INTEGER,
        TMP_PRIORITY_CODE   CHARACTER(8)
    ) 
    on commit preserve rows not logged with replace;
    begin atomic
     declare v_BeginDate timestamp ;
     declare v_EndDate timestamp ;
    
     set v_BeginDate = timestamp('2016-08-01');
     set v_EndDate = timestamp('2016-08-01');
        insert into SESSION.TEST_TABLE 
            select distinct
                   ORDER_TIME,
                   ORDER_ID,
                   NULL
            from table1
            where 
            ORDER_TIME between v_BeginDate and v_EndDate;
    end;
    select distinct * from SESSION.TEST_TABLE;

  2. Best Answer
    Posted by vcs1961

    "I believe I just figured this out:

    Locate(ltrim(rtrim(code_name here)), ltrim(rtrim(upper(v_variable_name here))) ) > 0

    This will look for any value within the variable separated with commas that is in the database field."


  3. #2
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    I believe I just figured this out:

    Locate(ltrim(rtrim(code_name here)), ltrim(rtrim(upper(v_variable_name here))) ) > 0

    This will look for any value within the variable separated with commas that is in the database field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •