Solved

selecting whereclause from table/column containing whereclause

Posted on 2014-01-02
16
569 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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