0

I have two unrelated tables, Activity and Schedules, in an oracle database using Business Objects to query. I can pair up any activity with any schedule but I want it to be prompted in the query. The tables are as follows:

Activity_Name
  Bodyweights
  Dosing
  Health Check

Schedule
  Twice A Day
  Daily
  Weekly

And I'd like to be able to run a query with a prompt that for each activity prompts for a schedule. I'd end up with this as one possibility:

Activity Schedule
     Activity        Schedule
     --------        --------
     Bodyweights     Weekly
     Dosing          Twice A Day
     Health Check    Twice A Day

The activity list would be one query with a prompt for available activities. I'd use those results in the Where clause of the second one and prompt for each schedule.

ЯegDwight
  • 24,821
  • 10
  • 45
  • 52
cjweitz
  • 33
  • 2
  • 9
  • What prevents you from creating a third table that would join those two tables in many-to-many relationship? – Nick Krasnov Sep 26 '12 at 18:17
  • There is no related key between the two. Essentially I'm trying to generate a derived table that's created at run time and prompts for each value, i.e. R1C1 = Bodyweights, R1C2 = Prompt for BW Schedule, R2C1 = Dosing, R2C2 = Prompt for Dosing Schedule, etc... – cjweitz Sep 26 '12 at 18:17
  • 1
    The database does not have the ability to prompt the user. That would have to be done on the client side. Answers will depend on the client. – Shannon Severance Sep 26 '12 at 18:23
  • Gotchya, the database can't but Business Objects can. I can generate a variable in BO that's a prompt but I'm curious what the sql on the oracle side is to call that variable (therefore the prompt) for each row in a table. – cjweitz Sep 26 '12 at 18:30
  • Does anyone know how this could be done as a stored procedure with PL/SQL? Generate a derived table (3 columns; index, activity_id, schedule_id) by querying for each record in a row from the respective tables and inserting them with the procedure. I know Oracle can't directly prompt but various UI's; SQL Developer, Business Objects, etc.. can. – cjweitz Oct 01 '12 at 17:55

0 Answers0