2

Lets assume (for simplicity) i have two tables:

Product
 product_id
 CreatedDate
 LastEditedBy
 EditedDate

Entity
 entity_id
 CreatedDate
 LastEditedBy
 EditedDate

Both tables have the same column names but only the ID column has a different name. I would like to run a query so that when i run it from SQL plus i just give it a parameter and it gets the data from one of the tables. In the above example i could run the query like this

@archiveHistory.sql product
@archiveHistory.sql entity

Here is my attempt but it always failed to recognise one of the columns, i.e. if i run it with product, it says entity_id does not exist. if i run it with entity it says product_id does not exist.

Note that i am using the passed in parameter on both the column selection and the table name selection. 

define identifier = '&1'

Select * from (
 Select case lower('&identifier')
  when product then product_id 
  when entity then entity_id
  end ID, CreatedDate, LastEditedBy, EditedDate
 From &identifier
)

I think it will work if the column list in the CASE statement were all from the same table.

Questions

  • What do i need to do so the query ignores the column that is not relevant i.e. ignore product_id if the argument is entity

  • I thought about using an anonymous PL/SQL block (i.e. Begin End) but i am not sure how i can display the output without using dbms_output.put_line.

ziggy
  • 15,677
  • 67
  • 194
  • 287

1 Answers1

2

For this particular case, I think the following SQL-only solution might work best:

SELECT product_id AS the_field
     , CreatedDate, LastEditedBy, EditedDate
FROM   Product
WHERE  LOWER('&identifier') = 'product'
UNION ALL
SELECT entity_id AS the_field
     , CreatedDate, LastEditedBy, EditedDate
FROM   Entity
WHERE  LOWER('&identifier') = 'entity'

The query planner will pre-evaluate your '&identifier' = ... predicates, which prevents execution of the unneeded union subquery.

If that's not an option (because your real-world use-case is much more complex), there are plenty of answers on Stack Overflow already regarding the execution of dynamic SQL from PL/SQL:

You could use dynamic SQL to insert your data into a temp table, and then simply SELECT * FROM temp_table

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • The OP says we should assume they have two tables "for simplicity". I suspect in real life they have many more. – APC Apr 18 '15 at 16:33
  • @APC: I agree, and I've started with *"For this particular case"* :-), but if that disclaimer wasn't there ("for simplicity"), this would be a useful answer for a more generic question which people will find when they Google this kind of topic. So, even if it might not be useful for the OP, it might be useful for others. – Lukas Eder Apr 18 '15 at 16:40