CODEWITHSUNDEEP
sqlsyntaxinformix
TeePaps
TeePaps

Reputation: 471

Informix SELECT INTO syntax error

I've been trying to use code which finds the count of elements in a table and stores it in a local variable. I basically just want to check the existence of a record, so if there is any easier way to do this.

Here is an example I found of storing the result of a query in a variable (link):

CREATE FUNCTION checklist( d SMALLINT )
    RETURNING VARCHAR(30), VARCHAR(12), INTEGER;

    DEFINE name VARCHAR(30);
    DEFINE dept VARCHAR(12);
    DEFINE num INTEGER;

    SELECT mgr_name, department,
           CARDINALITY(direct_reports) 
       FROM manager INTO name, dept, num
       WHERE dept_no = d;
    IF num > 20 THEN
       EXECUTE FUNCTION add_mgr(dept);
    ELIF num = 0 THEN
       EXECUTE FUNCTION del_mgr(dept);
    ELSE
       RETURN name, dept, num;
    END IF;

END FUNCTION;

But when I try to create my own version of this, I get a syntax error. I have no idea what the problem is.

CREATE FUNCTION test ()
     RETURNING INTEGER AS num1;

     DEFINE l_count INTEGER;

     CREATE TEMP TABLE t_queued_calls (
         session_id DEC(18,0) PRIMARY KEY,
         calling_number NVARCHAR(50)
     ) WITH NO LOG;

     INSERT INTO t_queued_calls VALUES (123456, '5555555555');
     SELECT COUNT(*) FROM t_queued_calls INTO l_count WHERE session_id = 123456;

     DROP TABLE t_queued_calls;
END FUNCTION;

Upvotes: 0

Views: 3981

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

The position of the INTO clause is wrong in both functions. The INTO clause goes after the select-list (the list of expressions after the keyword SELECT) and before the FROM clause (see the Informix "Guide to SQL: Syntax" manual on the SELECT statement), as in this code:

CREATE PROCEDURE test()
     RETURNING INTEGER AS num1;

     DEFINE l_count INTEGER;

     CREATE TEMP TABLE t_queued_calls (
         session_id DEC(18,0) PRIMARY KEY,
         calling_number NVARCHAR(50)
     ) WITH NO LOG;

     INSERT INTO t_queued_calls VALUES (123456, '5555555555');
     SELECT COUNT(*) INTO l_count FROM t_queued_calls WHERE session_id = 123456;

     DROP TABLE t_queued_calls;

     RETURN l_count;

END PROCEDURE;

Also, the first function as shown in the question has the same problem with the ordering of the clauses. Also, it does not always RETURN a value, and the original version of the second function never returns a value (although it says it will).

Upvotes: 4

TeePaps
TeePaps

Reputation: 471

I found the correct syntax from this question: [Use MERGE statement in a stored procedure with Informix

SELECT COUNT(*) INTO l_count FROM t_queued_calls WHERE session_id = 123456;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

The could be related to the fact the insert dont have the columns name

adapt your_column1, your_column2 to your table schema 

 INSERT INTO t_queued_calls (your_column1, your_column2) VALUES (123456, '5555555555');
 SELECT COUNT(*) FROM t_queued_calls INTO l_count WHERE session_id = 123456;

And/Or the number of column from the select don't match the number and type in insertt ... you select un field only but insert two field

and select into is strange select format ...normally is insert into but select don't use into clause

Upvotes: 1

Related Questions