Skip to Content
0

sql query with variables

Nov 18, 2016 at 09:39 PM

81

avatar image

Hello

I need to do a sql query where I might have to do a script and use variables. I use ADS server 11.1.

I have this table LOG:
FECHA Date
CONFIG Character 100
SUCURSAL numeric 3
Example:
15/11/16 \\terminal-server\programas\config10.dbf 10
15/11/16 \\terminal-server\programas\config4.dbf 6


I need to locate the records whose SUCURSAL number is not in the CONFIG field. For example the record 2. The record one is right because the string .....config10.dbf contains the number 10.
how can I do it?
very thanks

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Joachim Dürr Nov 19, 2016 at 11:18 AM
0

select * from log where not contains(config, 'config'+trim(cast(sucursal as sql_char)))

Share
10 |10000 characters needed characters left characters exceeded
Paola Bruccoleri Nov 24, 2016 at 02:45 PM
0

Hello

After much testing and searching, here I share the solution.

DECLARE @suc string;
DECLARE cursor1 CURSOR AS SELECT * FROM log;
CREATE TABLE #Temp1 ( fecha Date, hora char(10), usuario char(20), opcion char(50) );
OPEN cursor1;

WHILE FETCH cursor1 DO
@suc = RTRIM(CONVERT( cursor1.sucursal, SQL_VARCHAR ));
/* LOCATE Return integer location (1-based) of str1 in str2, with optional start starting point.
If str1 is not found in str2, 0 is returned.
*/
IF LOCATE( @suc, cursor1.config ) = 0 THEN
INSERT INTO #Temp1 VALUES( cursor1.fecha, cursor1.hora, cursor1.usuario, cursor1.opcion );
END IF;
END WHILE;
CLOSE cursor1;
select * from #Temp1;

It's works right.

The CONTAINS function does not work as expected

With the solution of Joachim I do not get the expected results. Thank you very much for your contribution.

Very thanks

Share
10 |10000 characters needed characters left characters exceeded
Joachim Dürr Nov 24, 2016 at 05:42 PM
0

So, change it to:

select * from log where LOCATE( RTRIM(CONVERT(sucursal, SQL_VARCHAR )), config ) = 0

But - hey, what about a record

15/11/16 \\terminal-server\programas\config40.dbf 4

This will return true - in this modified query aswell as in yours, because the CONVERT returns '4' which the locate finds in '...config40...'

Share
10 |10000 characters needed characters left characters exceeded