Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: Columns Name stored in another table -- Use in Where condition

    Hi All,

    I have table called " Attributes" with values as

    ID Attribute_ID Value
    ---------------------------------

    1001 GPI 1234567
    1001 NDC 1111111
    1001 ROU AG

    I have to form a query like this...

    Select * from <<table Name>> where GPI = 1234567 and NDC = 1111111 and ROU = 'AG'

    I have to use the columns names from the Attribute tables and form a query.

    Could anyone suggest some ways to do this? Thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    close,

    Code:
    Select * 
    from attributes 
    where GPI = 1234567 or
    NDC = 1111111 or
     ROU = 'AG';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2006
    Posts
    8
    Thanks for the response beilstwh.

    But, I need in different way...

    Actually, I have mentioned a sample query...that is

    Select * from RULE where GPI = 1234567 and NDC = 1111111 and ROU = 'AG'

    I have to form this query at run time using the values from the attributes tables. The number of attribute id may vary also. it depends on the rule id.

    Appreciate your help

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Untesed code -- but something like this should do the trick --

    Code:
    declare
    cursor get_attr_values is select Attribute_ID, Value from attributes where id in (select rule_id from rule);
    exec_string varchar2(2000);
    begin
    exec_string := 'select * from rule where '
    for get_attr_row in get_attr_values LOOP
       exec_string := exec_string||get_attr_row.attribute_id||'='||get_attr_row.value||' AND';
    END LOOP;
    exec_string:=rtrim(exec_string,' AND');
    execute immediate exec_string;
    end;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ID 
      from Attributes 
     where Attribute_ID = 'GPI' and Value = '1234567' 
        or Attribute_ID = 'NDC' and Value = '1111111'  
        or Attribute_ID = 'ROU' and Value = 'AG'
    group
        by ID
    having count(*) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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