Need to use a field from one table in the where clause of another tabl 1

[daddypost]

I have a table promotion_schedule that has a field called "where clause". This actually has the where clause used to pull certain items out of another table. If I do a:

select where_clause from promotion_schedule

it will give me the following:

((acntv.master_sku.retail_cat='some value',prod_type='some value').

What I need to do is get all the items from the master_sku table that have those criteria. Or

select * from acntv.master_sku where (select where_clause from promotion_schedule).


Does this make any sense at all. It keeps giving me an "invalid expression" message. I understand I cant do it as I've written as above, but not sure how to actually accomplish it. Any help is greatly appreciated.

 

[SantaMufasa]

DaddyPost,

I'm certain we can assist you in achieving your objective.

For us to be most helpful to you, can you please place your effort in "execution context"? Will you be running the above code from a script in SQL*Plus?...other environment? Can/Will your code run in PL/SQL?...SQL only? What code runs just prior/just following your focus code?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[daddypost]

I was hoping to just run it as a stand alone sql statement in pl/sql developer. The ultimate goal is to have the sql script be used in a cold fusion process that displays all of the items.
If I put it into a procedure I guess I can put the results in a variable and then call the variable in the where statement of the outer select but I was hoping to not have to go the procedure route.

 

[SantaMufasa]

If you were running in SQL*Plus, then we could formulate code to do what you want within SQL*Plus. Since you are using Cold Fusion, you must use Cold Fusion to formulate (within your Cold Fusion SQL code) the SQL statement that combines and transforms the WHERE clause from your "select where_clause from promotion_schedule" into a valid, executable SQL statement.

We call the above notion/technique, "SQL writing SQL". The rules for how to "write SQL from within SQL" differ, however, based upon your execution environment: SQL*Plus, versus Cold Fusion, versus PL/SQL, versus PL/SQL Developer, et cetera. For the concept to work for you, the execution environment must be able to execute either 1) a script that your environment write to disk, or 2) code stored in a local variable. SQL*Plus can do either option 1 or 2, and PL/SQL can do either option 1 or 2. Since I am not a Cold Fusion aficionado, I cannot speak for (or demonstrate) how to do either Option 1 or Option 2.

If you can figure out (perhaps with the help of people from the Cold Fusion Tek-Tips forum) how to achieve either Option 1 or Option 2, please post the recipe here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[daddypost]

thank you for the insight. If we take cold fusion out of it and just wanted to run in pl/sql developer to get a list, is there a way to do it without running a procedure and just from a straight sql statement?
Thank you again for taking the time to help.

 

[SantaMufasa]

If we...just wanted to run in pl/sql developer to get a list, is there a way to do it without running a procedure and just from a straight sql statement?

I am not a PL/SQL Developer aficionado, so I cannot give you a coded example of exactly that. But I can demostrate how to do what you want in SQL*Plus, then you can modify the code to simulate in PL/SQL Developer what I have done in SQL*Plus.


First, let me give you a non-syntactical functional narrative of the steps you can/should do in not only "PL/SQL Developer", but also in any environment (including "Cold Fusion") to achieve your objective:

1) SELECT your desired WHERE clause from your PROMOTION_SCHEDULE table and concatenate it to the end of the preceding non-WHERE portion of your target query statement.

2) Output the results of the above query (which will be an executable SELECT statement, complete with your queried "WHERE" clause) to a script file that your processing environment (e.g. PL/SQL Developer, Cold Fusion, et cetera) can execute.

3) Invoke the script file generated from Step #2, above, to produce your desired results.

To illustrate how I would do the above steps from within SQL*Plus, I created a script named "tt_503.sql":

set echo off
set feedback off
set pagesize 0
set termout off
spool my_query.sql
select 'select * from master_sku where '||where_clause||';'
  from promotion_schedule
 where 1 = 1
/
spool off
set termout on
set pagesize 35
@my_query

The code prior to, and following, my SELECT statement, above, is SQL*Plus code. I have no idea whether or not "PL/SQL Developer" has a problem with SQL*Plus code. If it does, then you can adjust the SQL*Plus code to achieve the same results in "PL/SQL Developer".

Here are the contents of "my_query.sql", which the above code generates:

select * from master_sku where master_sku.retail_cat='some value' and prod_type='some value';

(Please note: I changed the WHERE-clause contents that you show in your original posting since it contained a couple of syntax errors. See the corrections, below.)

The following CODE block lists the contents of my copies of your two tables, followed by the invocation of "tt_503.sql" from within SQL*Plus, and the results that the script generates:

SQL> select * from promotion_schedule;

WHERE_CLAUSE
-----------------------------------------------------------------
master_sku.retail_cat='some value' and prod_type='some value'

1 row selected.

SQL> select * from master_sku;

        ID DESCRIPTIO RETAIL_CAT           PROD_TYPE
---------- ---------- -------------------- --------------------
         1 SKU 117118 some value           some value
         2 SKU 297564 not some value       not some value
         3 SKU 047831 some value           some value
         4 SKU 900482 not some value       not some value

4 rows selected.

SQL> @tt_503


        ID DESCRIPTION RETAIL_CAT           PROD_TYPE
---------- ----------- -------------------- --------------------
         1 SKU 117118  some value           some value
         3 SKU 047831  some value           some value

Let us know if this gives you the insight you need to solve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[daddypost]

That got it. Thank you so much. I was leaving out the initial select and the extra set of ''. That is exactly what I needed. Once again, you are major help and a wonderful source of knowledge. I will again give you star. I have a feeling you might have a few of those in your profile. HA.
Have a great upcoming week.