×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SELECT & VARIABLE

SELECT & VARIABLE

SELECT & VARIABLE

(OP)
Please, help me...

I have this code - in Oracle:

declare
  t char(10) := 'qwerty';
begin
  select t
  into t
  from dual;
  
  dbms_output.put_line(t);
end;

OK, code is ready, but I want use this code in Informix 4GL and there is problem:

DATABASE MyDtb;

DEFINE
  t   CHAR(10),
  c01 CHAR(10),
  c02 CHAR(10);

MAIN
  LET t = 'qwerty';

  SELECT col01, col02, t
  INTO ...
  FROM <any table>;
END MAIN

I want use My variable (t) in query, in select section...
Thanks.

RE: SELECT & VARIABLE

With Informix 4GL, you have to dynamically build a cursor and then execute it using a FOREACH loop or an OPEN cursor/FETCH:

CODE

DEFINE tmp_str CHAR(100),
       t CHAR(10),
       col01 CHAR(20),
       col02 CHAR(20)

LET t = "qwerty"

LET tmp_str = "SELECT col01, col02 FROM ", t CLIPPED
PREPARE sn_del_stmt1 FROM tmp_str
DECLARE tbl_ptr CURSOR FOR sn_del_stmt1

FOREACH sn_del_curs1 INTO col1_var, col2_var
.
.
END FOREACH

For production code, you probably want to check that the PREPARE and DECLARE actually worked.

RE: SELECT & VARIABLE

(OP)
Thanks, but "t" is VARIABLE, no table name...

RE: SELECT & VARIABLE

Yes, I know it is; variable "t" equals "qwerty".  After this string manipulation:

CODE

LET tmp_str = "SELECT col01, col02 FROM ", t CLIPPED

tmp_str now equals this string:

CODE

SELECT col01, col02 FROM qwerty

Now, in order to execute that string, you need to set up a cursor as I outlined.

RE: SELECT & VARIABLE

(OP)
Thanks, but I do not understant...:

"t" is VARIABLE name, no TABLE name...

This is sample in Oracle:

declare
  actual_date date;
  t char(10) := 'qwerty';
begin
  select sysdate, t
  into actual_date, t
  from dual;
  
  dbms_output.put_line(actual_date||'   '||t);
end;

/* result: 10.03.08   qwerty */

OK, but I'm not able create it in Informix 4GL...???

RE: SELECT & VARIABLE

(OP)
In Oracle (OK, code is ready):

declare
  actual_date date;
  t char(10) := 'qwerty';
  x char(10);
begin
  select sysdate, t
  into actual_date, x
  from dual;
  
  dbms_output.put_line(actual_date||'   '||x);
end;

---------------------------------

In Informix 4GL (error: 201):

DATABASE xyz;

DEFINE
  actual_date DATE,
  t           CHAR(10),
  x           CHAR(10);

MAIN
  LET t = 'qwerty';

  SELECT CURRENT, t
  INTO actual_date, x
  FROM dual;
  
  DISPLAY actual_date, '  ', x;
END MAIN

HELP ME, PLEASE...

RE: SELECT & VARIABLE

(OP)
Thanks, but DUAL is ready in Informix...

This Informix code (without "t") is ready:

DATABASE xyz;

DEFINE
  actual_date DATE,
  t           CHAR(10),
  x           CHAR(10),
  text        CHAR(1000);

MAIN
  LET t = 'qwerty';

  LET text =
    'SELECT CURRENT'||
    ' FROM dual' CLIPPED;
  PREPARE prp01 FROM text;
  DECLARE cur01 CURSOR FOR prp01;
  
  FOREACH cur01 INTO actual_date
      DISPLAY actual_date;
  END FOREACH;
END MAIN

----------------------------------------------

This Informix code (with "t") is bad (error -217):

DATABASE vlakovka;

DEFINE
  actual_date DATE,
  t           CHAR(10),
  x           CHAR(10),
  text        CHAR(1000);

MAIN
  LET t = 'qwerty';

  LET text =
    'SELECT CURRENT, t'||
    ' FROM dual' CLIPPED;
  PREPARE prp01 FROM text;
  DECLARE cur01 CURSOR FOR prp01;
  
  FOREACH cur01 INTO actual_date, x
      DISPLAY actual_date, '  ', x;
  END FOREACH;
END MAIN

RE: SELECT & VARIABLE

(OP)
Sorry, database name is mystake. Database name is "xyz" for together samplas...

RE: SELECT & VARIABLE

(OP)
Thanks !!!! Code is READY...  :)  :)  :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close