| Execute the expression in select statement [message #577044] |
Tue, 12 February 2013 02:36  |
muralikri
Messages: 638 Registered: August 2011 Location: chennai
|
Senior Member |

|
|
Hi All,
CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;
CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;
I want to be execute the expression should run in select statement how to do?
and tried as like below,it's not working.
select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;
Thanks
|
|
|
|
|
|
| Re: Execute the expression in select statement [message #577058 is a reply to message #577052] |
Tue, 12 February 2013 02:57   |
xpact83
Messages: 203 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
CREATE OR REPLACE FUNCTION test1x RETURN NUMBER
is
a varchar2(1000);
b number;
BEGIN
SELECT export_column_expression
into a
FROM test2;
EXECUTE IMMEDIATE ' SELECT ' || a || ' FROM test1 ' into b ;
return b;
END;
just modify it so that you can specify also the table name or the expression you want to use if have many expressions in your table
|
|
|
|
| Re: Execute the expression in select statement [message #577060 is a reply to message #577058] |
Tue, 12 February 2013 02:59   |
 |
Michel Cadot
Messages: 54155 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
@xpact83
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
Regards
Michel
|
|
|
|
|
|
| Re: Execute the expression in select statement [message #577068 is a reply to message #577062] |
Tue, 12 February 2013 03:06   |
 |
Michel Cadot
Messages: 54155 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: i tried to create the function with execute immedaite not working.
When I say "you have to use dynamic SQL", I did not mean you just have to put the invalid statement in an EXECUTE IMMEDIATE one, of course, the statement remains invalid if you are or not in EXECUTE IMMEDIATE.
By the way, if EXPORT_COLUMN_EXPRESSION can contain any expression, or list of expressions, then you cannot use EXECUTE IMMEDIATE, you have to use DBMS_SQL.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|