RE: View question
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_TIMEFROM ICTRANS
b1WHERE
(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/') 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 andexits per item in
determinate period of time. To determinate if is an entrace or exitdepend
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 orafter 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
quantity0 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/') and
to_date('08/06/2003','mm/dd/')
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
RE: View question
Title: Message Short answer is no. Long answer is "no, you cannot, but you can use application contexts and virtual private database to do something very similar." --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teresita CastroSent: Wednesday, August 06, 2003 6:44 PMTo: Multiple recipients of list ORACLE-LSubject: View question Can I send a parameter to a view? I am using Oracle 9.2i, and I'm still new on using Oracle. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
RE: View question
I got the first part of the query, sum of entrace an exit per
item, and the initial inventary that I will use has stock on hand in case we
don't have transactionsmade onthe initial date on the rango or
later.
SELECT ICTRANS.COMPANY,
ICTRANS.LOCATION, ICTRANS.ITEM, ITEMMAST.INVEN_MAJCL, ITEMMAST.INVEN_MINCL,
ITEMLOC.SOH_QTY,SUM( CASE WHEN ICTRANS.QUANTITY0 THEN ICTRANS.QUANTITY
ELSE 0 END) ENTRADA,SUM( CASE WHEN ICTRANS.QUANTITY0 THEN
ICTRANS.QUANTITY ELSE 0 END) SALIDAFROM ICTRANS INNER
JOIN ITEMMAST ON ICTRANS.ITEM = ITEMMAST.ITEM
INNER JOIN ITEMLOC ON ICTRANS.COMPANY =
ITEMLOC.COMPANY AND ICTRANS.LOCATION = ITEMLOC.LOCATION AND
ICTRANS.ITEM =
ITEMLOC.ITEMWHERE (ICTRANS.COMPANY = 2000) AND
(ICTRANS.LOCATION = 'TJU01') AND ICTRANS.UPDATE_DATE BETWEEN
TO_DATE('08/01/2003','MM/DD/') AND TO_DATE('08/06/2003','MM/DD/')and
ITEMMAST.INVEN_MAJCL='110'GROUP BY ICTRANS.COMPANY, ICTRANS.LOCATION,
ICTRANS.ITEM, ITEMMAST.INVEN_MAJCL,
ITEMMAST.INVEN_MINCL,ITEMLOC.SOH_QTY
Here I am using a ITEMMAST ( the master table of items) too because they
want to filter information per Inventory clases
(ITEMMAST.INVEN_MAJCL).
I just want to find the way of join it why:
SELECT DISTINCT ITEM, SOH_QTY, UPDATE_DATE,
UPDATE_TIMEFROM ICTRANS
b1WHERE
(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/') 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
[EMAIL PROTECTED]
08/06/03 06:24PM
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_TIMEFROM ICTRANS
b1WHERE
(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/') 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 andexits per item in
determinate period of time. To determinate if is an entrace or exitdepend
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 orafter 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
quantity0 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/') and
to_date('08/06/2003','mm/dd/')
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
Re: View Question?
At 02:45 PM 1/7/2003 -0800, Hamid Alavi wrote: List, Oracle8i support insert/updatable view or NOT? From Oracle's Metalink: you can update a multiple base table view from 7.3.x onwards, provided columns you are updating are not primary keys for these tables.
Re: View Question?
Yo, Oracle 7.2 supported updatable views. Some restrictions have been lifted in later versions, others NOT. Mogens Hamid Alavi wrote: List, Oracle8i support insert/updatable view or NOT? Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: View Question?
USE INSTEAD OF TRIGGERS . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 2:45 PM List, Oracle8i support insert/updatable view or NOT? Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
