Home » SQL & PL/SQL » SQL & PL/SQL » Execute the expression in select statement (Oracle 11g)
Execute the expression in select statement [message #577044] Tue, 12 February 2013 02:36 Go to next message
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 #577052 is a reply to message #577044] Tue, 12 February 2013 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use dynamic SQL.

Regards
Michel
Re: Execute the expression in select statement [message #577058 is a reply to message #577052] Tue, 12 February 2013 02:57 Go to previous messageGo to next message
xpact83
Messages: 218
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;


select test1x from dual


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 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
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 #577062 is a reply to message #577052] Tue, 12 February 2013 03:01 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks micheal.. i tried to create the function with execute immedaite not working.

CREATE OR REPLACE function fun return VARCHAR2 as
v_sql VARCHAR2(100);  
begin
execute immediate 'select  (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME=''A1'') FROM TEST1' INTO v_sql;
return v_sql;
end;
/
 


SELECT FUN FROM DUAL
Output: 
least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)

Re: Execute the expression in select statement [message #577068 is a reply to message #577062] Tue, 12 February 2013 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
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
Re: Execute the expression in select statement [message #577069 is a reply to message #577062] Tue, 12 February 2013 03:06 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
sorry micheal.
Re: Execute the expression in select statement [message #577070 is a reply to message #577062] Tue, 12 February 2013 03:07 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks @xpact83
Splited two select statement and working fine.
Re: Execute the expression in select statement [message #577074 is a reply to message #577070] Tue, 12 February 2013 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Will you be able to do it next time?
I bet you will come here to post the same kind of question.

regards
Michel
Re: Execute the expression in select statement [message #577077 is a reply to message #577074] Tue, 12 February 2013 03:31 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Micheal do you any how to change the my code
Re: Execute the expression in select statement [message #577079 is a reply to message #577077] Tue, 12 February 2013 03:33 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Quote:
if EXPORT_COLUMN_EXPRESSION can contain any expression, or list of expressions, then you cannot use EXECUTE IMMEDIATE, you have to use DBMS_SQL.


My expression always contain only one
Re: Execute the expression in select statement [message #577083 is a reply to message #577079] Tue, 12 February 2013 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless the type of the expression is always the same one (or it exists an implicit conversion to the same one, most likely a VARCHAR2), you cannot use EXECUTE IMMEDIATE, you have to use DBMS_SQL.

Regards
Michel
Re: Execute the expression in select statement [message #577085 is a reply to message #577083] Tue, 12 February 2013 04:03 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Please help me how to do using DBMS_SQL.
Re: Execute the expression in select statement [message #577087 is a reply to message #577085] Tue, 12 February 2013 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, read the documentation.
Then study T.Kyte's print_table function.

Regards
Michel
Re: Execute the expression in select statement [message #577088 is a reply to message #577087] Tue, 12 February 2013 04:11 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@Michel,above one is it's not related this Question.
Re: Execute the expression in select statement [message #577091 is a reply to message #577088] Tue, 12 February 2013 04:30 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question

Can you explain what you mean.
What is not related to what?

Regards
Michel
Previous Topic: Problems with REPLACE( overreplacing
Next Topic: Partition Creation
Goto Forum:
  


Current Time: Sun Dec 28 19:48:26 CST 2014

Total time taken to generate the page: 0.07749 seconds