SQL Oracle openquery to get items and location of items

Question

I am using an openquery to an Oracle server to get information from BaaN ERP.
My request is to get the information of an item and all the locations where the item has stock, and show the information in a gridview, as you can see in the attached image.

For example: I have item CIS-11-2846-01 in warehouse 1, 2, 3 and 4.
In warehouse 1 I have 100 pieces located in R1, warehouse 2 has 5000 pieces located in R1-A, warehouse 3 has 5000 pieces located in R2-B and warehouse 4 has 1000 pieces located in R5-Z.

I managed to make a query that returns all the locations and actual stock for that item, but I have no idea on how to make the "row header" to show the Item number with the production estimated qty. (The blue remark in the picture)

Edit

My actual query is as follows:

SELECT * FROM OPENQUERY
(am3p1, 'SELECT o.T$PDNO, REPLACE(o.T$SITM,'' '','''')T$SITM, o.T$CWAR, o.T$QUNE, o.T$CPES$1, w.T$LOCA, w.T$STKS FROM baan.tticst001305 o
INNER JOIN baan.twhinr140305 w ON REPLACE(o.T$SITM,'' '','''') = REPLACE(w.T$ITEM,'' '','''')
 WHERE o.T$PDNO = ''2GE000632'' AND w.T$LOCA <> '' '' ORDER BY o.T$SITM, w.T$IDAT')

@Hogan - it's an openquery, so I'd assume that the query is being done from SQL to Oracle. — devlin carnate, Mar 31 '16 at 14:38
You need to show us more if you want some actual help. Show us the query that gives you the non blue part. — Hogan, Mar 31 '16 at 14:38
Hello @Hogan, yeah sorry about that. Is exactly as Devlin says. It's an openquery to Oracle. I have edited the question with the query I am using :) — , Mar 31 '16 at 14:43

score 0 · Accepted Answer · answered Mar 31 '16 at 17:31

Well I don't know openquery but I do know sql. In SQL you would do the following (if o.T$PDNO is item name and o.T$QUNE is quantity.)

SELECT o.T$PDNO as item_name, sum(o.T$QUNE) as total
FROM (
  SELECT o.T$PDNO, REPLACE(o.T$SITM,' ','')T$SITM, o.T$CWAR, o.T$QUNE, o.T$CPES$1, w.T$LOCA, w.T$STKS
  FROM baan.tticst001305 o
  INNER JOIN baan.twhinr140305 w ON REPLACE(o.T$SITM,' ','') = REPLACE(w.T$ITEM,' ','')
  WHERE o.T$PDNO = '2GE000632' AND w.T$LOCA <> ' '
  ORDER BY o.T$SITM, w.T$IDAT')
)
GROUP BY o.T$PDNO

You don't give any rules for how to derive the last two columns so I can't include them.

Perfect solution! Thanks a lot for the help! Made some minor changes, but the overall solution worked as expected. Will edit with the openquery that I used. — , Mar 31 '16 at 18:16

SQL Oracle openquery to get items and location of items

1 Answers1