0

I have three databases connected remotedly via DBLink and I want to create a record in the three of them. The dynamic in this problem is that there is a company which has 3 retail stores in which they can sell items found in the others. The item1 is only found in retailstore1, item2 is only found in retailstore2 and so on. However, one store can sell items not currently found in it so it needs to request to the other stores in order to check if there's enough in stock. Everytime one store sells, it creates a record in LOG, but only if it was successful.

These are the tables used in the example.

  • Retail Store #1: Items(a int), LOG(a int, b int, c int)
  • Retail Store #2: Items(b int), LOG(a int, b int, c int)
  • Retail Store #3: Items(c int), LOG(a int, b int, c int)

Now, I want to check if there's enough items in stock before making the record in LOG in the three stores, but this has to be done in a single query or store procedure or the necessary steps to make this possible. The restriction is that the databases need to be open once, execute, close and no more.

ditmark12
  • 111
  • 1
  • 11
  • Note. If you know how to make an approach, please help me. It doesn't have to be a full answer using the syntax required. Just ideas. Thank you. – ditmark12 Oct 16 '14 at 17:39
  • 1
    I'm having trouble understanding your exact issue, as well as your schema. If you want a response, you'll want to flesh out this question. Some suggestions: What version of Oracle are you using? How are you checking to see if an item is in stock (the Items table you describe doesn't have enough information in it). What is the purpose of the LOG table - why are there three columns? You need to show an example of the data flow. In absence of all that, read up on Two-Phase Commits (http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm) – Steve Broberg Oct 16 '14 at 18:36

1 Answers1

0

It sounds like what you want is a query that shows which store stocks the item, so when the clerk does a lookup, it shows the item, store, and qty left. One way might use unions, such that if a procedure where you passed in the item number (without knowing where it exists), you would query like this: (p_item IN NUMBER) -- this is the parameter passed in with the item number desired

select item1, store1, qty1, 0 item2, 0 store2, 0 qty2, 0 item3, 0 store3, 0 qty3
from store1
where item1 = p_item
union all
select 0, 0, 0, item2, store2, qty2, 0, 0, 0 
from store2
where item2 = p_item
union all
select 0, 0, 0, 0, 0, 0, item3, store3, qty3 
from store3
where item3 = p_item

this should only return 1 row with values in it other than 0s, and will tell you where it's at.

The update could then be using an if statement or case statement to update the appropriate store based on which item/store combination it was found in.

JohnFL
  • 52
  • 3