selecting whereclause from table/column containing whereclause

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
rutgermonsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
The previous "EXTRACTVALUE" suggestion assumed only one row would be returned from the vehicles table.   Given the sample data, that's not likely to be true.  So you'll probably want to use XMLTABLE instead

SELECT *
  FROM XMLTABLE(
           '//ROW'
           PASSING DBMS_XMLGEN.getxmltype(
                          'select * from vehicles where '
                       || (SELECT clausedescription
                             FROM whereclauses
                            WHERE clausename = 'FUELTYPE')
                   )
           COLUMNS brand PATH '/ROW/BRAND',
                fuel path '/ROW/FUEL')
0
 
YZlatCommented:
you need to join two tables on a common field - the field that links both vehicles and whereclause tables
0
 
sdstuberCommented:
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;
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Gerwin Jansen, EE MVETopic Advisor Commented:
Something like:

select * from vehicles where whereclause in
(select clausedescription from whereclauses WHERE clausename= 'fueltype');
0
 
sdstuberCommented:
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)
0
 
rutgermonsAuthor Commented:
@gerwinjansen


where whereclause in


is "whereclause" and actual function?
0
 
sdstuberCommented:
>>> 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'"
0
 
Gerwin Jansen, EE MVETopic Advisor 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
0
 
rutgermonsAuthor Commented:
0
 
rutgermonsAuthor Commented:
see atatched
0
 
sdstuberCommented:
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
0
 
rutgermonsAuthor 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?
0
 
rutgermonsAuthor Commented:
ur right, should be fueltype ='DIESEL'
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
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)
0
 
rutgermonsAuthor Commented:
BINGO!Thank you so much for sharing your knowledge for us Sean!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.