这条语句count(*)为何得不到select出来的总行数???
SELECT count(*),
we.wip_entity_name,
MSI2.SEGMENT1,
sum(WRO.REQUIRED_QUANTITY) QTY,
MSI.PRIMARY_UNIT_OF_MEASURE UOM,
wro.QUANTITY_PER_ASSEMBLY
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
INV.MTL_SYSTEM_ITEMS MSI,
WIP.WIP_OPERATIONS WO,
INV.MTL_SYSTEM_ITEMS MSI2
,wip.wip_entities we
WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = 4
AND MSI.ORGANIZATION_ID = 4
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
AND MSI2.ORGANIZATION_ID = 4
AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
and wdj.wip_entity_id=we.wip_entity_id
and substr(MSI.SEGMENT1,1,1) in ('7')
and MSI.SEGMENT1 like '7190-020160%'
group by
we.wip_entity_name,
MSI2.SEGMENT1,
MSI.PRIMARY_UNIT_OF_MEASURE ,
wro.QUANTITY_PER_ASSEMBLY
结果如下:
count(*) wip_entity_name SEGMENT1 qty uom QUANTITY_PER_ASSEMBLY
1 FJ2012060501 3459-403180 18.6 kg 9.3
1 FJ2012060502 3459-403180 18.6 kg 9.3
明明是行数是两行,我希望能得到行数2,但count(*)却是1,应该怎么改语句啊
SELECT count(*),
we.wip_entity_name,
MSI2.SEGMENT1,
sum(WRO.REQUIRED_QUANTITY) QTY,
MSI.PRIMARY_UNIT_OF_MEASURE UOM,
wro.QUANTITY_PER_ASSEMBLY
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
INV.MTL_SYSTEM_ITEMS MSI,
WIP.WIP_OPERATIONS WO,
INV.MTL_SYSTEM_ITEMS MSI2
,wip.wip_entities we
WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = 4
AND MSI.ORGANIZATION_ID = 4
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
AND MSI2.ORGANIZATION_ID = 4
AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
and wdj.wip_entity_id=we.wip_entity_id
and substr(MSI.SEGMENT1,1,1) in ('7')
and MSI.SEGMENT1 like '7190-020160%'
group by
we.wip_entity_name,
MSI2.SEGMENT1,
MSI.PRIMARY_UNIT_OF_MEASURE ,
wro.QUANTITY_PER_ASSEMBLY
结果如下:
count(*) wip_entity_name SEGMENT1 qty uom QUANTITY_PER_ASSEMBLY
1 FJ2012060501 3459-403180 18.6 kg 9.3
1 FJ2012060502 3459-403180 18.6 kg 9.3
明明是行数是两行,我希望能得到行数2,但count(*)却是1,应该怎么改语句啊
解决方案 »
- LANG, NLS_LANG, oracle数据库中NLS_LANGUAGE三者的关系是什么??
- 谁能提供国外银行卡在铁路系统的应用介绍?
- 使用group by后怎么查询除分组字段外的字段?
- 一条INSERT INTO语句的错误
- ===== 求一简单的sql语句 =====
- 如何在oracle安装 jvm?
- 关于存储过程的一个很奇怪的问题
- 不幸删除system账号,急急急急急!
- 在Form中Call 一个 Report
- oracle 有没有sql server profiler类似的工具?
- 用SQL查询一批数据,如何在最后一行增加个数字合计
- 导入.dmp时出错:IMP-00003: 遇到 ORACLE 错误 942
we.wip_entity_name,
MSI2.SEGMENT1,
sum(WRO.REQUIRED_QUANTITY) QTY,
MSI.PRIMARY_UNIT_OF_MEASURE UOM,
wro.QUANTITY_PER_ASSEMBLY
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
INV.MTL_SYSTEM_ITEMS MSI,
WIP.WIP_OPERATIONS WO,
INV.MTL_SYSTEM_ITEMS MSI2
,wip.wip_entities we
WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = 4
AND MSI.ORGANIZATION_ID = 4
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
AND MSI2.ORGANIZATION_ID = 4
AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
and wdj.wip_entity_id=we.wip_entity_id
and substr(MSI.SEGMENT1,1,1) in ('7')
and MSI.SEGMENT1 like '7190-020160%'
group by
we.wip_entity_name,
MSI2.SEGMENT1,
MSI.PRIMARY_UNIT_OF_MEASURE ,
wro.QUANTITY_PER_ASSEMBLY
pl sql developer报错
指向we.wip_entity_name并提示ORA-00937:非单组分组函数