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 a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Nov 19, 2016 at 11:18 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.