17,086
社区成员
发帖
与我相关
我的任务
分享Oracle SQL 求指导
select
distinct p.userdefined2 as fld_TMD_order_no,
ps.wmps_traceID as fld_TMD_connote_no,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_price,
--sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_total_amount,
-- replace(to_char(wmsys.wm_concat(rtrim(ws.userdefined5))),',',';') as fld_edi_description,
ws.userdefined5 as fld_edi_description,
TO_CHAR(SYSDATE,'yyyymmdd') as fld_TM_manifest_id,
1 as fld_TM_cartons ,
nvl(ps.wmps_grossweight,0)as fld_TM_cbm ,
nvl(ps.wmps_cubic,0)as fld_TM_weight ,
'AIR'as fld_TM_container_type,
'' as fld_TM_container_no,
'AUD' as fld_TM_seal_no,
''as fld_TM_vessel_name,
TO_CHAR(SYSDATE+1,'DD/MM/YYYY') as fld_TM_edt,
1 as fld_TMD_cartons,
nvl(ps.wmps_cubic, 0) as fld_TMD_cbm,
nvl(ps.wmps_grossweight, 0) as fld_TMD_weight,
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
TO_CHAR(SYSDATE+3,'dd/mm/yyyy') as fld_TO_import_date,
o.wmor_soreference2 as fld_TO_order_no,
1 as fld_TO_carrier,
o.wmor_consigneename as fld_TO_surname,
NVL(o.wmor_c_address1, ' ') as fld_TO_address_1,
NVL(o.wmor_c_address2, ' ') as fld_TO_address_2,
o.wmor_c_country as fld_TO_country_code,
o.wmor_c_city as fld_TO_suburb,
NVL(o.wmor_c_province, ' ') as fld_TO_state,
o.wmor_c_zip as fld_TO_postcode,
NVL(o.wmor_c_tel1, ' ') as fld_TO_telephone,
'AeParcelPac' as fld_TO_delivery_instr,
NVL(o.wmor_c_address3, ' ') as fld_TO_address_3,
NVL(ps.wmps_grossweight, 0) as fld_TO_weight,
1 as fld_TOL_line_no,
'' as fld_TOL_product_no,
'' as fld_TOL_item_no,
1 as fld_TOL_quantity,
TO_CHAR(SYSDATE+5,'DD/MM/YY') as fld_TOL_promised_date,
ws.userdefined5 as fld_edi_description,
'Jeanswest' as mame,
'11th Floor, Unit C, 2 Tins Centre'as addr1,
'Hung Cheung Road 3' as addr2,
'Tuen Mun' as city,
'Hong Kong' as state,
'NA' as postcode,
'HK'as country
from wm_out_packing_summary ps,wm_out_packing p,wm_out_order o,wm_out_order_sku os,cd_wh_sku ws
where
ps.wmps_traceid=p.wmop_traceid
and p.wmop_orderno=o.wmor_order_no
and p.wmop_sku_code=os.wmos_sku_code
and os.wmos_order_no=o.wmor_order_no
and ws.cdsk_sku_code=os.wmos_sku_code
group by ps.wmps_traceID,o.wmor_soreference2,o.wmor_consigneename,o.wmor_c_country,o.wmor_c_city,o.wmor_c_zip,ws.userdefined5,ps.wmps_grossweight,
ps.wmps_cubic,ps.wmps_cubic,ps.wmps_grossweight,o.wmor_c_address1,o.wmor_c_address2,o.wmor_c_province,o.wmor_c_tel1,o.wmor_c_address3,ps.wmps_grossweight, p.userdefined2;
这个是我写的Sql语句，有点乱。。 查询出来后的结果是如图
现在要以wmps_traceID 为主唯一查询，不要出现重复，然后要把价格累加。 还有一个 ws.userdefined5 字段里面有描述，要把 ws.userdefined5这个字段 不同的内容用 , 放一起显示出来，还不能重复。
重点的查询字段我都放在前面了。
（业务就是 以箱子id为主，一个箱子里面 有很多个产品，然后每个产品的价格和数量不一样。要把一个箱子里面的所以的产品价格*数量 总价格显示出来，然后还要把每个产品的描述放一起用 , 分开。）
这个sql语句该怎么写？ 求指导。 求指导啊。
distinct p.userdefined2 as fld_TMD_order_no,
ps.wmps_traceID as fld_TMD_connote_no,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_price,
--sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_total_amount,
-- replace(to_char(wmsys.wm_concat(rtrim(ws.userdefined5))),',',';') as fld_edi_description,
ws.userdefined5 as fld_edi_description,
TO_CHAR(SYSDATE,'yyyymmdd') as fld_TM_manifest_id,
1 as fld_TM_cartons ,
nvl(ps.wmps_grossweight,0)as fld_TM_cbm ,
nvl(ps.wmps_cubic,0)as fld_TM_weight ,
'AIR'as fld_TM_container_type,
'' as fld_TM_container_no,
'AUD' as fld_TM_seal_no,
''as fld_TM_vessel_name,
TO_CHAR(SYSDATE+1,'DD/MM/YYYY') as fld_TM_edt,
1 as fld_TMD_cartons,
nvl(ps.wmps_cubic, 0) as fld_TMD_cbm,
nvl(ps.wmps_grossweight, 0) as fld_TMD_weight,
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
TO_CHAR(SYSDATE+3,'dd/mm/yyyy') as fld_TO_import_date,
o.wmor_soreference2 as fld_TO_order_no,
1 as fld_TO_carrier,
o.wmor_consigneename as fld_TO_surname,
NVL(o.wmor_c_address1, ' ') as fld_TO_address_1,
NVL(o.wmor_c_address2, ' ') as fld_TO_address_2,
o.wmor_c_country as fld_TO_country_code,
o.wmor_c_city as fld_TO_suburb,
NVL(o.wmor_c_province, ' ') as fld_TO_state,
o.wmor_c_zip as fld_TO_postcode,
NVL(o.wmor_c_tel1, ' ') as fld_TO_telephone,
'AeParcelPac' as fld_TO_delivery_instr,
NVL(o.wmor_c_address3, ' ') as fld_TO_address_3,
NVL(ps.wmps_grossweight, 0) as fld_TO_weight,
1 as fld_TOL_line_no,
'' as fld_TOL_product_no,
'' as fld_TOL_item_no,
1 as fld_TOL_quantity,
TO_CHAR(SYSDATE+5,'DD/MM/YY') as fld_TOL_promised_date,
ws.userdefined5 as fld_edi_description,
'Jeanswest' as mame,
'11th Floor, Unit C, 2 Tins Centre'as addr1,
'Hung Cheung Road 3' as addr2,
'Tuen Mun' as city,
'Hong Kong' as state,
'NA' as postcode,
'HK'as country
from wm_out_packing_summary ps,wm_out_packing p,wm_out_order o,wm_out_order_sku os,cd_wh_sku ws
where
ps.wmps_traceid=p.wmop_traceid
and p.wmop_orderno=o.wmor_order_no
and p.wmop_sku_code=os.wmos_sku_code
and os.wmos_order_no=o.wmor_order_no
and ws.cdsk_sku_code=os.wmos_sku_code
group by ps.wmps_traceID,o.wmor_soreference2,o.wmor_consigneename,o.wmor_c_country,o.wmor_c_city,o.wmor_c_zip,ws.userdefined5,ps.wmps_grossweight,
ps.wmps_cubic,ps.wmps_cubic,ps.wmps_grossweight,o.wmor_c_address1,o.wmor_c_address2,o.wmor_c_province,o.wmor_c_tel1,o.wmor_c_address3,ps.wmps_grossweight, p.userdefined2;
这个是我写的Sql语句，有点乱。。 查询出来后的结果是如图
现在要以wmps_traceID 为主唯一查询，不要出现重复，然后要把价格累加。 还有一个 ws.userdefined5 字段里面有描述，要把 ws.userdefined5这个字段 不同的内容用 , 放一起显示出来，还不能重复。
重点的查询字段我都放在前面了。
（业务就是 以箱子id为主，一个箱子里面 有很多个产品，然后每个产品的价格和数量不一样。要把一个箱子里面的所以的产品价格*数量 总价格显示出来，然后还要把每个产品的描述放一起用 , 分开。）
这个sql语句该怎么写？ 求指导。 求指导啊。
...全文
请发表友善的回复…
发表回复
xilige550474890 2014-10-30
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。[/quote]
问题解决了，Thanks[/quote]
请问楼主这个问题怎么解决的 ？
小灰狼W 2014-07-30
- 打赏
- 举报
select regexp_replace(to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE =American'),'[[:space:]]+',' ')||'_BWB_Manifest' as fld_TO_filename from dual;
Kally_Wang 2014-07-30
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。[/quote]
这个问题有点棘手。如果程序可以直接取clob字段，不在sql语句中做to_char最好
也可以不用wm_concat函数，写个自定义的
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
这个函数返回的是varchar2类型，注意长度不要超过4000[/quote]
不好一切，还有一个小问题想请教你，这个字段，查询出来后要把月份和日期中间的空格去掉，如何做？
select to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE =American')||'_BWB_Manifest' as fld_TO_filename from dual;
Kally_Wang 2014-07-25
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。[/quote]
这个问题有点棘手。如果程序可以直接取clob字段，不在sql语句中做to_char最好
也可以不用wm_concat函数，写个自定义的
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
这个函数返回的是varchar2类型，注意长度不要超过4000[/quote]
全英文的， 书读的少，搞不来啊 ，我再看看吧。谢谢了。
Kally_Wang 2014-07-25
- 打赏
- 举报
什么样的问题[/quote]
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description, [/quote]
这个问题 要怎么玩？求大神指导指导。
小灰狼W 2014-07-25
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。[/quote]
这个问题有点棘手。如果程序可以直接取clob字段，不在sql语句中做to_char最好
也可以不用wm_concat函数，写个自定义的
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
这个函数返回的是varchar2类型，注意长度不要超过4000
Kally_Wang 2014-07-25
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。
小灰狼W 2014-07-25
- 打赏
- 举报
ORA-22922
应该和to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
没有关系
问题大概出在这里wm_concat(distinct ws.userdefined5)
你用的oracle应该是10.2.0.5以上的版本，wm_concat返回的是clob类型
Kally_Wang 2014-07-25
- 打赏
- 举报
什么样的问题[/quote]
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
wangjianlin7574 2014-07-25
- 打赏
- 举报
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
是这个sql中这个字段拼起来的数据引起的.
Kally_Wang 2014-07-25
- 打赏
- 举报
flex.messaging.MessageException: java.lang.RuntimeException : java.sql.SQLException: ORA-22922: 不存在的 LOB 值
这个错误有木有人知道该如何解决
是SQL：
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
这个字段引起的。 在自定义报表中不显示这个字段不查询就没有任何问题，要是显示出来就会报这个错误。
我查询2014.7月 - 2014.7月 以后的日期没有问题。 查询2014.6月 到2014.7月 就会报这个错误。
然后 我也在数据库中看了 fld_TO_filename 这个拼起来的字段的所有数据的长度 都在100以下 。
这个错误有木有人知道该如何解决
是SQL：
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
这个字段引起的。 在自定义报表中不显示这个字段不查询就没有任何问题，要是显示出来就会报这个错误。
我查询2014.7月 - 2014.7月 以后的日期没有问题。 查询2014.6月 到2014.7月 就会报这个错误。
然后 我也在数据库中看了 fld_TO_filename 这个拼起来的字段的所有数据的长度 都在100以下 。
Kally_Wang 2014-07-25
- 打赏
- 举报
什么样的问题[/quote]
flex.messaging.MessageException: java.lang.RuntimeException : java.sql.SQLException: ORA-22922: 不存在的 LOB 值
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:447)
at com.sinoservices.common.security.MyJavaFilterAdapter.invoke(MyJavaFilterAdapter.java:73)
at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)
at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)
at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)
at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:322)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-22922: 不存在的 LOB 值
at com.sinoservices.common.dao.impl.ExtendSqlDaoImpl.findOracle(ExtendSqlDaoImpl.java:130)
at com.sinoservices.common.dao.impl.ExtendSqlDaoImpl.find(ExtendSqlDaoImpl.java:72)
at com.sinoservices.common.util.DaoUtils.find(DaoUtils.java:601)
at com.sinoservices.chem.report.service.impl.EsDiyReportManagerImpl.oracleBySql(EsDiyReportManagerImpl.java:1243)
at com.sinoservices.chem.report.service.impl.EsDiyReportManagerImpl.queryBySqlOracle(EsDiyReportManagerImpl.java:861)
at com.sinoservices.chem.report.service.impl.EsDiyReportManagerImpl.queryBySql(EsDiyReportManagerImpl.java:335)
at sun.reflect.GeneratedMethodAccessor22757.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:50)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77)
at com.sinoservices.common.aop.AccessLogServiceAspect.around(AccessLogServiceAspect.java:50)
at sun.reflect.GeneratedMethodAccessor744.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:64)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy288.queryBySql(Unknown Source)
at sun.reflect.GeneratedMethodAccessor22756.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)
... 28 more
Caused by: java.sql.SQLException: ORA-22922: 不存在的 LOB 值
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.sinoservices.common.dao.impl.ExtendSqlDaoImpl.findOracle(ExtendSqlDaoImpl.java:108)
... 60 more
这个错误该如何该如何解决？ 你知道吗？
是SQL：
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
这个字段引起的。 在自定义报表中不显示这个字段不查询就没有任何问题，要是显示出来就会报这个错误。
我查询2014.7月 - 2014.7月 以后的日期没有问题。 查询2014.6月 到2014.7月 就会报这个错误。
然后 我也在数据库中看了 fld_TO_filename 这个拼起来的字段的所有数据的长度 都在100以下 。
Kally_Wang 2014-07-25
- 打赏
- 举报
嗯嗯 是的呀。
前面写错了 是SQL 这个字段中拼起来的数据引起的。
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
这问题 好头疼。[/quote]
问题解决了，Thanks
Kally_Wang 2014-07-23
- 打赏
- 举报
什么样的问题[/quote]
OK了，已经解决了，主要是这个原因 你提醒了我，整理了一下别人帮我添加的那个group by后面的字段。
Thanks.感激不尽。
小灰狼W 2014-07-23
- 打赏
- 举报
什么样的问题
Kally_Wang 2014-07-23
- 打赏
- 举报
有试过
select
wmps_traceid,
sum(fld_tol_price) as fld_TOL_price from(
select distinct p.userdefined2,
ps.wmps_traceID,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_price,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_total_amount
from wm_out_packing_summary ps,wm_out_packing p,wm_out_order_sku os
where ps.wmps_traceid = p.wmop_traceid
and p.wmop_orderno = os.wmos_order_no
and p.wmop_sku_code =os.wmos_sku_code
group by ps.wmps_traceID,p.userdefined2,os.userdefined2,os.wmos_qtypicked
) group by
wmps_traceid;
但是加上其他字段后 还是会有重复。
Kally_Wang 2014-07-23
- 打赏
- 举报
整理了一下group by 后面的字段，确实没有的重复mps_traceID了。
Kally_Wang 2014-07-23
- 打赏
- 举报
整理了一下group by 后面的字段，确实没有的重复mps_traceID了。 价格累加了。 还有一个没有解决。
CT_LXL 2014-07-23
- 打赏
- 举报
select ps.wmps_traceID as fld_TMD_connote_no,
sum(NVL(os.userdefined2, 0) * os.wmos_qtypicked) as fld_TOL_price,
to_char(wm_concat(ws.userdefined5)) as fld_edi_description
from wm_out_packing_summary ps,
wm_out_packing p,
wm_out_order o,
wm_out_order_sku os,
cd_wh_sku ws
where ps.wmps_traceid = p.wmop_traceid
and p.wmop_orderno = o.wmor_order_no
and p.wmop_sku_code = os.wmos_sku_code
and os.wmos_order_no = o.wmor_order_no
and ws.cdsk_sku_code = os.wmos_sku_code
group by ps.wmps_traceID;
bw555 2014-07-23
- 打赏
- 举报
在你的语句外面再套一层吧，要看懂你的语句再修改实在是费尽
select max(FLD_TMD_ORDER_NO) FLD_TMD_ORDER_NO,
FLD_TMD_CONNOTE_NO,
SUM(FLD_TOL_PRICE) FLD_TOL_PRICE,
TO_CHAR(WMSYS.WM_CONCAT(FLD_EDI_DESCRIPTION)) FLD_EDI_DESCRIPTION,
MAX(……),max(……)
from (你的sql语句)
group by FLD_TMD_CONNOTE_NO加载更多回复（4）
