Solved

selecting whereclause from table/column containing whereclause

Posted on 2014-01-02
16
559 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
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
you need to join two tables on a common field - the field that links both vehicles and whereclause tables
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 37

Expert Comment

by:Gerwin Jansen
Comment Utility
Something like:

select * from vehicles where whereclause in
(select clausedescription from whereclauses WHERE clausename= 'fueltype');
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
@gerwinjansen


where whereclause in


is "whereclause" and actual function?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> 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 37

Expert Comment

by:Gerwin Jansen
Comment Utility
@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
Comment Utility
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rutgermons
Comment Utility
see atatched
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
ur right, should be fueltype ='DIESEL'
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
BINGO!Thank you so much for sharing your knowledge for us Sean!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now