Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: Variable with more than one value inside

    Hallo everyone,

    is there a chance in SQL generally to define a variable with more than one values, like:

    myVar = {29, 4564, 3}

    I need this, because I have a column of type VARCHAR:

    #35#1#4564#27#

    And I would like to prove, whether one of the values in myVar exists. My idea for the checking is by using like:
    Code:
    column_name like myVar
    But it showed up to be complexer as thought.

    Thanks for the comments.

    Regards,

    Ratna

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Ordinary array data type

    Once populated, you can treat an array as a table:
    Code:
    select * from table(myArrayVar)...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use arrays in SQL procedure.
    But, I think that it may be not so usefull to put values of myVar in an array, in your case.
    Because, there is no capability to compare each element in an array with a column or a variable by an operator or a function
    (same as most programming languages).
    You need to use loop to compare each element in an array with another column/variable.

    My idea is to put each element in myVar into a temporary table(or a table-expression).

    First, make a funcion to parse a string, like
    Example 1:
    Code:
    CREATE or REPLACE FUNCTION extract_members
    ( in_string VARCHAR(4000)
    , in_del    VARCHAR(  10)
    )
    RETURNS TABLE
    ( k       SMALLINT
    , element VARCHAR(100)
    )
     READS SQL DATA
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
    SELECT k
         , SUBSTR(in_string , pos_pre , pos_del - pos_pre)
     FROM  (SELECT k
                 , pos_del
                 , LAG(pos_del , 1 , 0)
                      OVER(ORDER BY k) + 1 AS pos_pre
             FROM  (SELECT k1 + k2
                     FROM  (VALUES   1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10) AS k(k1)
                     CROSS JOIN
                           (VALUES 0,10,20,30,40,50,60,70,80,90   ) AS k(k2)
                   ) AS k(k)
             CROSS JOIN
                   LATERAL
                   (VALUES INSTR(in_string || in_del , in_del , 1 , k)
                   ) AS f(pos_del)
             WHERE pos_del > 0
           )
     WHERE pos_del - pos_pre > 0
    ;
    You can see more general examples in
    Parsing Strings in SQL


    Example 2: Parse myVar and use LIKE predicate.
    Note: Need more recent DB2 version(at least DB2 9.7 for LUW Fix Pack 4, may be...).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data
    ( column_name , myVar ) AS (
    VALUES
      ( '#35#1#4564#27#'  , '29,4564,3' )
    , ( '#35#1#4564#27#'  , '7,29,4564' )
    , ( '#35#1#4564#27#'  , '29,3527,3' )
    , ( '#27#35#1#4564#'  , '29,4564,3' )
    , ( '#35#1#4529#27#'  , '29,4564,3' )
    , ( '#1#45#27#35#64#' , '29,4564,3' )
    , ( '#3#45#27#35#64#' , '29,4564,3' )
    )
    SELECT column_name , myVar
         , CASE
           WHEN EXISTS
                (SELECT 0
                  FROM  TABLE( extract_members(myVar , ',') ) AS f
                  WHERE column_name LIKE '%#' || f.element || '#%'
                ) THEN
                'Yes'
           ELSE 'No'
           END  AS element_exists
     FROM  test_data
    ;
    ------------------------------------------------------------------------------
    
    COLUMN_NAME     MYVAR     ELEMENT_EXISTS
    --------------- --------- --------------
    #35#1#4564#27#  29,4564,3 Yes           
    #35#1#4564#27#  7,29,4564 Yes           
    #35#1#4564#27#  29,3527,3 No            
    #27#35#1#4564#  29,4564,3 Yes           
    #35#1#4529#27#  29,4564,3 No            
    #1#45#27#35#64# 29,4564,3 No            
    #3#45#27#35#64# 29,4564,3 Yes           
    
      7 record(s) selected.

    Example 3: Parse both of column_name and myVar, then compare all combination of elements.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data
    ( column_name , myVar ) AS (
    VALUES
      ( '#35#1#4564#27#'  , '29,4564,3' )
    , ( '#35#1#4564#27#'  , '7,29,4564' )
    , ( '#35#1#4564#27#'  , '29,3527,3' )
    , ( '#27#35#1#4564#'  , '29,4564,3' )
    , ( '#35#1#4529#27#'  , '29,4564,3' )
    , ( '#1#45#27#35#64#' , '29,4564,3' )
    , ( '#3#45#27#35#64#' , '29,4564,3' )
    )
    SELECT column_name , myVar
         , CASE
           WHEN EXISTS
                (SELECT 0
                  FROM  TABLE( extract_members(column_name , '#') ) AS f1
                  INNER JOIN
                        TABLE( extract_members(myVar       , ',') ) AS f2
                   ON   f2.element = f1.element
                ) THEN
                'Yes'
           ELSE 'No'
           END  AS element_exists
     FROM  test_data
    ;
    ------------------------------------------------------------------------------
    
    COLUMN_NAME     MYVAR     ELEMENT_EXISTS
    --------------- --------- --------------
    #35#1#4564#27#  29,4564,3 Yes           
    #35#1#4564#27#  7,29,4564 Yes           
    #35#1#4564#27#  29,3527,3 No            
    #27#35#1#4564#  29,4564,3 Yes           
    #35#1#4529#27#  29,4564,3 No            
    #1#45#27#35#64# 29,4564,3 No            
    #3#45#27#35#64# 29,4564,3 Yes           
    
      7 record(s) selected.
    Last edited by tonkuma; 03-05-13 at 17:09. Reason: Modify test data a little. Replace CROSS JOIN in Example 3 with INNER JOIN. Add Note to Example 2.

Posting Permissions

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