Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2010
    Posts
    7

    Red face Unanswered: Concat variable in query string

    Hi!
    I have a SP and at the query i want to concatenate a input variable like:

    FECHA = "AND IN('1','2','3','4')

    CREATE PROCEDURE SP_BUSCAR ( IN FINICIO DATE, IN FFIN DATE, IN CADENA_FECHAS CHAR(100))

    DYNAMIC RESULT SETS 1

    LANGUAGE SQL

    P1 : BEGIN

    DECLARE CURSOR1 CURSOR WITH RETURN FOR

    SELECT F . ID_047 , ID_049 , ASUNTO , HINICIO , HFIN , FECHA , ( DAYOFWEEK ( FECHA ) - 1 ) AS DSEMANA FROM SCHEMA . T_047 O, SCHEMA . T_049 F WHERE O . ID_047 = F . ID_047 AND (HINICIO BETWEEN FINICIO AND FFIN) OR (HFIN BETWEEN FINICIO AND FFIN) || FECHAS;

    OPEN CURSOR1 ;

    END P1 ;


    BUT I HAVE AN ERROR:

    QL State: 42601
    Vendor Code: -104
    Message: [SQL0104] Token || was not valid.

    I READ TO USE CONCAT FUNCTION, BUT I DONT KNOW HOW TO USE, THX!
    Last edited by raqueliii; 02-19-10 at 12:43.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Take a look at dynamically built statement and prepare statement.

    Dave

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    PREPARE, EXECUTE & EXECUTE IMMEDIATE are the keywords you have to search on

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    WHERE O . ID_047 = F . ID_047 AND (HINICIO BETWEEN FINICIO AND FFIN) OR (HFIN BETWEEN FINICIO AND FFIN) || FECHAS;
    Grammar mistake in the where clause?

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    verify that both sides of || are CHAR. if they are not, use CHAR(...)||CHAR(...)
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "HFIN BETWEEN FINICIO AND FFIN" is a predicate and "(HFIN BETWEEN FINICIO AND FFIN)" is a search condition.
    Those cannot be converted to any data type.

    Follow the recommendations of Dave and Sathyaram...
    (They are practically same. )
    Take a look at dynamically built statement and prepare statement.
    PREPARE, EXECUTE & EXECUTE IMMEDIATE are the keywords you have to search on

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Beyond the question you asked, you query (as written) has a serious flaw in it. (I have replace your concatenation attempt the the code you want there (which is also missing the column you want to compare to the IN list):
    Code:
    SELECT F . ID_047 , ID_049 , ASUNTO , HINICIO , HFIN , FECHA , ( DAYOFWEEK ( FECHA ) - 1 ) AS DSEMANA 
    FROM TUMENMA . T_047 O
       , TUMENMA . T_049 F 
    WHERE O . ID_047 = F . ID_047 
      AND (HINICIO BETWEEN FINICIO AND FFIN) 
       OR (HFIN    BETWEEN FINICIO AND FFIN)
      AND ??? IN('1','2','3','4');
    As written the parenthesis do nothing as they enclose a single predicate. But the problem is you have your Join Predicate (O.ID_047 = F.ID_047) is on one side of an OR. Because of this the Join Predicate could be FALSE but you can still return the row(s) if the other side of the OR is True (HFIN BETWEEN FINICIO AND FFIN AND ??? IN ('1','2','3','4') ). This would result in a partial cartisian result. What you probably want is something like:

    Code:
    SELECT F . ID_047 , ID_049 , ASUNTO , HINICIO , HFIN , FECHA , ( DAYOFWEEK ( FECHA ) - 1 ) AS DSEMANA 
    FROM TUMENMA . T_047 O
       , TUMENMA . T_049 F 
    WHERE O . ID_047 = F . ID_047 
      AND (HINICIO BETWEEN FINICIO AND FFIN
       OR  HFIN    BETWEEN FINICIO AND FFIN)
      AND ??? IN('1','2','3','4');

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    An example from one of my SP

    Code:
    :
    :
    --Statements
    declare v_stmt statement;
    --cursor
    declare ct cursor for v_stmt;
    
    Set v_sql='select 1 from '||p_TableName||' '||p_WhrClause;
     
    prepare v_stmt from v_sql;
    
    open ct;
    
    :
    :
    regards

    DBFinder

  9. #9
    Join Date
    Feb 2010
    Posts
    7
    Thxs!!! I try both of the solutions and its great! Thxs!

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "(HFIN BETWEEN FINICIO AND FFIN)" is also a predicate. I think the issue here is the difference between "expressions" and "predicates". An expression, more specifically a string expression, can be concatenated with another (string) expression. A predicate can only be combined using AND and OR keywords with other predicates.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

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