需要使用某些视图（PL SQL）生成报告，其中聚合显示特定的库存位置计数 [英] Need to generate a report using some views(PL SQL) with aggregation that shows for a particular Stock Location counts

问题描述

要求：



在每个库存计数后漂移多少以及哪些EPC通过库存数量更正



1）在前计数快照中的相同位置（上午5:30和上午11:30之前）



2）处于不同的位置location



3）根本不在一个地方



4）以上的百分比< br $>


创建的表格：



DRIFT_ITEMS_Pre（移动前EPC详细信息将存储在他的表格中）



TPNB



EPC



LOCATIONID



ITEMSTATUS



LOADED_TIME



MOVED_FLAG



DRIFT_ITEMS_POST（运动后EPC详细信息将存储在他的表格中）

TPNB

EPC



LOCATIONID



ITEMSTATUS



LOADED_TIME


MOVED_FLAG



另外还有一个表格，我们将捕获每个EPC和加载时间的日志记录器详细信息。



我目前的方法是创建两个视图和聚合过程，然后将创建一个父视图，将生成如下所示的报告但是我收到一些错误：



1）一个过程/更新/合并将首先运行如下：



更新DRIFT_ITEMS DIPre，DRIFT_ITEMS_POST DIPost SET MOVED_FLAG ='1'

其中EPC IN（从中选择EPC） Drift_EPC_Items DIP其中

（（（DIPre.LOADED_TIME> DIP.TIME_STAMP）> STOCK.stockcounts.STARTDATE））

或

（（ STOCK.stockcounts.FINISHDATE> DIP.TIME_STAMP）> DIPost.LOADED_TIME））



错误：



在命令的第1行开始出错：

更新DRIFT_ITEMS DIPre，DRIFT_ITEMS_POST DIPost SET MOVED_FLAG ='1'

其中EPC IN（从Drift_EPC_Items DIP中选择EPC，其中

（（（DIPre.LOADED_TIME> DIP.TIME_STAMP）> STOCK.stockcounts.STARTDATE））

或

（（STOCK.stockcounts.FINISHDATE> DIP.TIME_STAMP ）> DIPost.LOADED_TIME））

命令行错误：1列：25

错误报告：

SQL错误：ORA-00971 ：缺少SET关键字

00971. 00000 - 缺少SET关键字

*原因：

2）查看创建以达到以下标准

a）在前计数快照中的相同位置（上午5:30和上午11:30之前）



b）Were在不同的位置



c）根本不在一个位置



d）上述百分比< br $>




创建或替换强制查看DRIFT_COUNTS_MOVEMENTS_VW（EPC，LOCATIONID）AS

SELECT s.locationid，p.locationid，

count（epc）FROM DRIFT_ITEMS的内部连接drift_items_post p

on（s.epc = p.epc和s.moved_flag< > 1和s.locationid<> p.locationid）

group by s.locationid;

3）查看创建

a）在前计数快照中的同一位置（上午5:30和上午11:30之前）



b）位于不同的位置



c）不在一个位置



d）以上百分比





创建或替换强制查看报告。DRIFT_MOVEMENTS_POST_VW（LOCATIONID，EPC）AS

SELECT p.locationid，count（epc）

来自DRIFT_ITEMS_POST p其中epc不在（选择epc from drift_items s，其中s.moved_flag<> 1）分组由p.locationid;



在第二个视图创建中也是我收到错误（视图正在创建错误）。任何人都可以帮助解决这个问题或建议我一些最好的方法来实现这个要求。

Requirement :

To drift after every stock count how many and which EPCs were corrected by the stock count

1)Were in that same location in the before count snapshot(At 5:30 AM and before 11:30 AM)

2)Were in different location

3)Weren't in a location at all

4)% of the above

Tables created :

DRIFT_ITEMS_Pre (Before movement EPC Details will be stored int his table)

TPNB

EPC

LOCATIONID

ITEMSTATUS

LOADED_TIME

MOVED_FLAG

DRIFT_ITEMS_POST(After movement EPC Details will be stored int his table)
TPNB
EPC

LOCATIONID

ITEMSTATUS

LOADED_TIME

MOVED_FLAG

Also there is another table where we will capture the log parcer details of each EPC and loadtime.

My current approach is to create two views along with an aggregate procedure , then will create one parent view which will generate the report like below however i am getting some errors:

1) One procedure/Update/Merger will run first as follows:

update DRIFT_ITEMS DIPre,DRIFT_ITEMS_POST DIPost SET MOVED_FLAG='1'
where EPC IN(Select EPC from Drift_EPC_Items DIP where
(((DIPre.LOADED_TIME>DIP.TIME_STAMP)>STOCK.stockcounts.STARTDATE) )
OR
((STOCK.stockcounts.FINISHDATE>DIP.TIME_STAMP)>DIPost.LOADED_TIME ))

ERROR :

Error starting at line 1 in command:
update DRIFT_ITEMS DIPre,DRIFT_ITEMS_POST DIPost SET MOVED_FLAG='1'
where EPC IN(Select EPC from Drift_EPC_Items DIP where
(((DIPre.LOADED_TIME>DIP.TIME_STAMP)>STOCK.stockcounts.STARTDATE) )
OR
((STOCK.stockcounts.FINISHDATE>DIP.TIME_STAMP)>DIPost.LOADED_TIME ))
Error at Command Line:1 Column:25
Error report:
SQL Error: ORA-00971: missing SET keyword
00971. 00000 - "missing SET keyword"
*Cause:
2)View Creation in order to achieve below criterias
a)Were in that same location in the before count snapshot(At 5:30 AM and before 11:30 AM)

b)Were in different location

c)Weren't in a location at all

d)% of the above


CREATE OR REPLACE FORCE VIEW "DRIFT_COUNTS_MOVEMENTS_VW" ( "EPC", "LOCATIONID") AS
SELECT s.locationid, p.locationid,
count(epc) FROM DRIFT_ITEMS s inner join drift_items_post p
on (s.epc=p.epc and s.moved_flag <> 1 and s.locationid <> p.locationid )
group by s.locationid;
3)View Creation
a)Were in that same location in the before count snapshot(At 5:30 AM and before 11:30 AM)

b)Were in different location

c)Weren't in a location at all

d)% of the above


CREATE OR REPLACE FORCE VIEW "REPORT"."DRIFT_MOVEMENTS_POST_VW" ( "LOCATIONID","EPC") AS
SELECT p.locationid,count(epc)
FROM DRIFT_ITEMS_POST p where epc not in (select epc from drift_items s where s.moved_flag <> 1) group by p.locationid;

in 2nd view creation also i am getting error(view is getting created with error).Can anyone plz help to sort this out or suggest me some best approach to achieve the requirement.

推荐答案

First例如，如果唯一的区别是行的状态（在移动之前），为什么要将数据存储在两个单独的表中t /运动后）。这会导致额外的复杂性



但是，如果你想使用那些表，如果我正确理解了这个问题，那么更新pre表可能是这样的：

First of all, why store the data in two separate tables if the only difference is the status of the row (before movement / after movement). This causes extra complexity

However, if you want to use those table and if I understand the question correctly, to update the pre table could be something like:

UPDATE DRIFT_ITEMS_Pre
SET MOVED FLAG = '1'
WHERE EXISTS (SELECT 1
              FROM  DRIFT_ITEMS_POST
              WHERE DRIFT_ITEMS_POST.EPC = DRIFT_ITEMS_Pre.EPC)

AND MOVED FLAG！='1';

换句话说，如果在邮政运动表中找到EPC



然后另一个表基于这个想法，后来的位置id已经改变了或许像

AND MOVED FLAG != '1';
IN other words if EPC is found in the post movement table

Then again the other table based on the idea the location id has changed later perhaps something like

UPDATE DRIFT_ITEMS_POST a
SET MOVED FLAG = '1'
WHERE EXISTS (SELECT 1
              FROM  DRIFT_ITEMS_POST b
              WHERE a.EPC = b.EPC
              AND   a.LOCATIONID != b.LOCATIONID
              AND   a.LOADED_TIME < b.LOADED_TIME)
AND MOVED FLAG != '1';

我不确定逻辑，因为你没有解释细节和用法每个领域。但是，我确信你从这些例子中得到了这个想法。

I'm not sure about the logic since you didn't explain the details and usage of each field. However, I'm sure you get the idea from those examples.

这篇关于需要使用某些视图（PL SQL）生成报告，其中聚合显示特定的库存位置计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！