SELECT & VARIABLE

[vlukes]

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.

 

[olded]

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

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.

 

[vlukes]

Thanks, but "t" is VARIABLE, no table name...

 

[olded]

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

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

tmp_str now equals this string:

SELECT col01, col02 FROM qwerty

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

 

[vlukes]

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

 

[vlukes]

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

 

[PHV]

HELP ME, PLEASE...
1) Try to make sense with olded's post (6 Mar 08 11:22).
2) There is no dual pseudo table in Informix.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vlukes]

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

 

[vlukes]

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

 

[PHV]

What about this ?

LET text = "SELECT CURRENT, '" || t CLIPPED || "' FROM dual";

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vlukes]

Thanks !!!! Code is READY...