We help IT Professionals succeed at work.

selecting whereclause from table/column containing whereclause

605 Views
Last Modified: 2014-01-02
Folks

I have 2 tables

vehicles
whereclauses

in my whereclauses table i have 2 columns namely

clausename
clausdescription

the clausdescription contains actual whereclauses i.e. (where fuel_type ='diesel'), the associated clausename  is 'fueltype'

how do get the following to work

select * from vehicles WHERE  (select clausedescription from whereclauses WHERE clausename= 'fueltype')

all help will do
Comment
Watch Question

CERTIFIED EXPERT

Commented:
you need to join two tables on a common field - the field that links both vehicles and whereclause tables
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can use dynamic sql.  Exact syntax will depend on the framework from which you are executing the sql statements.

in pl/sql it might look something like this...


DECLARE
    v_sql VARCHAR2(32767);
    v_cur SYS_REFCURSOR;
    v_row vehicles%ROWTYPE;
BEGIN
    SELECT clausedescription
      INTO v_sql
      FROM whereclauses
     WHERE clausename = 'fueltype';

    v_sql := 'select * from vehicles where ' || v_sql;

    OPEN v_cur FOR v_sql;

    LOOP
        FETCH v_cur INTO v_row;

        EXIT WHEN v_cur%NOTFOUND;

        -- do something with the data
        DBMS_OUTPUT.put_line('v_row found' || v_row.col1 || ' ' || v_row.col2);
    END LOOP;

    CLOSE v_cur;
END;
Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
Something like:

select * from vehicles where whereclause in
(select clausedescription from whereclauses WHERE clausename= 'fueltype');
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
neither a join ( http:#a39751411 ) nor using a simple IN clause ( http:#a39751415 ) will produce the results you're looking for

you need to construct a legal sql statement, if you don't want to do a 2-step process with dynamic sql, you could use XML with a dynamic statement and then parse out the fields form the the resulting document.

Something like this...


SELECT EXTRACTVALUE(xml, '//ROW/YOUR_FIRST_COLUMN'), EXTRACTVALUE(xml, '//ROW/YOUR_SECOND_COLUMN')
  FROM (SELECT DBMS_XMLGEN.getxmltype(
                      'select * from vehicles where '
                   || (SELECT clausedescription
                         FROM whereclauses
                        WHERE clausename = 'fueltype')
               )
                   xml
          FROM DUAL)

Author

Commented:
@gerwinjansen


where whereclause in


is "whereclause" and actual function?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> is "whereclause" and actual function?

no it's not,  that suggestion won't work unless you happen to have a column called "whereclause" in your vehicles table that already has the same whereclause you're trying to lookup inside the whereclauses table.

which means you wouldn't be looking for vehicles with diesel fueltype.
rather, you'd be looking for vehicles that had a column containing the clause "fueltype='diesel'"
Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
@Sean - thanks :)

whereclause  would be a column in your vecicles table

Some sample data and a description of the vehicles table, including your expected output would help :D

Author

Commented:
see atatched
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, I understand.  

there is no direct means of embedding a where clause as the statement is executed.
The sql must be complete at the time it is parsed, then it's executed.

So, you must use dynamic sql.

Either something like the pl/sql above or the xml version.

I think you had an error in your sample data.

"TRANSMISSION='DIESEL'"

but that doesn't change anything

Author

Commented:
Hi Sean

Im struggling a bit, your code runs but I am not sure what to add on the columsn


SELECT EXTRACTVALUE(xml, '//ROW/YOUR_FIRST_COLUMN'), EXTRACTVALUE(xml, '//ROW/YOUR_SECOND_COLUMN')


if from my vehicle table I wish to view the brand and fuel columns how must I specify these?

Author

Commented:
ur right, should be fueltype ='DIESEL'
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
another option,  create a function that reads the conditions and evaluates them against the corresponding column values in the vehicles table then returns a simple YES/NO or 1/0 value as a result to compare against.

Note, this will probably not be very efficient
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
SELECT EXTRACTVALUE(xml, '//ROW/BRAND'), EXTRACTVALUE(xml, '//ROW/FUEL')
  FROM (SELECT DBMS_XMLGEN.getxmltype(
                      'select * from vehicles where '
                   || (SELECT clausedescription
                         FROM whereclauses
                        WHERE clausename = 'fueltype')
               )
                   xml
          FROM DUAL)
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
BINGO!Thank you so much for sharing your knowledge for us Sean!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.