Concat variable in query string

raqueliii · #1 (**permalink**) 02-16-10, 18:15

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!

dav1mo · #2 (**permalink**) 02-17-10, 08:05

Take a look at dynamically built statement and prepare statement.

Dave

sathyaram_s · #3 (**permalink**) 02-17-10, 08:47

PREPARE, EXECUTE & EXECUTE IMMEDIATE are the keywords you have to search on

Sathyaram

wilsonfv · #4 (**permalink**) 02-18-10, 01:15

Quote:

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?

MarkhamDBA · #5 (**permalink**) 02-18-10, 09:26

verify that both sides of || are CHAR. if they are not, use CHAR(...)||CHAR(...)

tonkuma · #6 (**permalink**) 02-18-10, 11:00

"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. )

Quote:

Take a look at dynamically built statement and prepare statement.

Quote:

PREPARE, EXECUTE & EXECUTE IMMEDIATE are the keywords you have to search on

Stealth_DBA · #7 (**permalink**) 02-18-10, 21:12

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');

DBFinder · #8 (**permalink**) 02-18-10, 22:44

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

raqueliii · #9 (**permalink**) 02-19-10, 11:38

Thxs!!! I try both of the solutions and its great! Thxs!

stolze · #10 (**permalink**) 02-19-10, 17:21

"(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.

Internet Services

Web Development

Digital Marketing

Consumer Tech