Solved

selecting whereclause from table/column containing whereclause

Posted on 2014-01-02
16
571 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
0
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 39751411
you need to join two tables on a common field - the field that links both vehicles and whereclause tables
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39751413
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39751415
Something like:

select * from vehicles where whereclause in
(select clausedescription from whereclauses WHERE clausename= 'fueltype');
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39751442
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
 

Author Comment

by:rutgermons
ID: 39751457
@gerwinjansen


where whereclause in


is "whereclause" and actual function?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39751461
>>> 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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39751539
@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
 

Author Comment

by:rutgermons
ID: 39751568
0
 

Author Comment

by:rutgermons
ID: 39751570
see atatched
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39751590
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
 

Author Comment

by:rutgermons
ID: 39751602
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
 

Author Comment

by:rutgermons
ID: 39751610
ur right, should be fueltype ='DIESEL'
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39751612
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39751620
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39751646
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
 

Author Closing Comment

by:rutgermons
ID: 39751684
BINGO!Thank you so much for sharing your knowledge for us Sean!
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question