Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unhappy Unanswered: SQL Query problem... (help plz!!!)

    Hi,
    I have a problem creating an sql query -
    I have to replace each appearance of a hard-coded function variable with an expression from another table. The problem is that there might be several expressions to replace in one field, for ex :

    Item.countChildsByName( "CO_OCB" )+Item.countChildsByName( "CO_ICB" )+Item.countChildsByName( "CO_External" )+Item.countChildsByName( "CO_External" ) <=2

    I want to replace each of the expressions inside the quotation marks(that comes after "ByName") with a field from other table, How can I do that ?

    Thanks for your time !

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: SQL Query problem... (help plz!!!)

    Here is a sample of dynamic SQL within a PL/SQL block. It shows how to replace hard coded table named with a variable. You can modify to include column names and other things.

    DECLARE
    v_tab VARCHAR2(30);
    v_id NUMBER(10);
    cursor c1 IS SELECT TABLE_NAME FROM USER_TABLES.
    v_sql VARCHAR2(1000);
    BEGIN
    OPEN c1;
    LOOP
    FETCH c1 INTO v_tab;
    v_sql := 'SELECT id FROM ' || v_tab;
    EXECUTE IMMEDIATE (v_sql) INTO (v_id);
    END LOOP;
    CLOSE c1;
    END;
    /

  3. #3
    Join Date
    Feb 2003
    Posts
    7

    Re: SQL Query problem... (help plz!!!)

    I am really sorry for my ignorance, but I don't know PL/SQL, If you could explain your SQL, it would be great,
    My Table name is "TBPCRULES" and the field that holds the hard coded function called "EXPRESSION",
    I want to replace all of the "xxx" expressions (only the ones that are followed by "ByName" string) with a different field called "CID" from the table - "TBNAMES", can you try to modify your sample please ?

    (I would really make my day...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •