Skip to Content

sql query with variables

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 19, 2016 at 11:18 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 24, 2016 at 02:45 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 24, 2016 at 05:42 PM

    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...'

    Add comment
    10|10000 characters needed characters exceeded