RE: View question

[Teresita Castro]

Thanks for answer me.   The problem that I have is the next, I have to calculate from a item transaction table the stock on hand to determinate date, (to made different reports).   To do this I made the next query:     SELECT DISTINCT ITEM, SOH_QTY, UPDATE_DATE, UPDATE_TIME FROM         ICTRANS b1 WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01') and     (UPDATE_DATE=                  (SELECT     Max(UPDATE_DATE)AS FECH_HORA                   FROM          ICTRANS                   WHERE      UPDATE_DATE <= to_date('08/01/2003', 'mm/dd/yyyy') AND (COMPANY = b1.COMPANY) AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)                    GROUP BY ITEM)) AND (UPDATE_TIME=                           (SELECT     MIN(UPDATE_TIME)AS HORA                             FROM          ICTRANS                             WHERE      (UPDATE_DATE = b1.UPDATE_DATE) AND (COMPANY = b1.COMPANY)AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)                             GROUP BY  ITEM)) ORDER by ITEM   Now I have to made a report that have the next information:  Item, stock on hand , entraces and exits per item in determinate period of time. To determinate if is an entrace or exit depend of the sign of the Ictrans.quantity field.   In this example the 08/01/2003 date will be the initial date.   This is a new system and maybe we will not have movements on the initial date or after it, so I have to bring the initial inventory from another table, ITEMLOC ( item by location) that have the initial inventory of the item.   So the query will be something like :   select  company,location, item, sum (case when quantity<0 then quantity else 0 end) exit, sum(case when quantity>=0 then quantity else 0 end) entrace from ictrans where  company=2000 and location='TJU01' and  update_date between to_date('08/01/2003','mm/dd/yyyy') and to_date('08/06/2003','mm/dd/yyyy') group by company,location, item   Union with the result of the item stock on hand  (SOH_QTY) of the first  query, or in case that this result is null, to obtain the stock on hand I have to consult the ITEMLOC table   Select item, soh_qty from itemloc where company=2000 and location='TJU01'   I hope I made my self clear and you undestand my explaination. What I want to do it a view(vw_stockonhand) to obtain the initial inventory then made a left join to the ictrans table, so in case that I don't have movements on the initial date or after it returns me a null, and another left join with the Itemloc, so if vw_stockonhand is null I get the itemloc.soh_qty.   Thanks for you time