当前位置: 代码迷 >> J2EE >> sql转hql 求解!该如何解决
  详细解决方案

sql转hql 求解!该如何解决

热度:15   发布时间:2016-04-22 00:45:58.0
sql转hql 求解!!!
SQl为:

select temp.aa as 日期,temp.bb as DC,temp.cc as 品类,temp.ee as 品牌,temp.ff as "收货能力(箱)",temp.gg as "收货能力(体积)",temp.hh as 已预约箱数,temp.jj as 已预约体积 from 
(
select (select trunc(sysdate) from users where rownum =1) aa,dc.dc_name bb,dd.category cc,dd.brand ee,dd.average_receive_capacity ff,dd.average_receive_volume gg,
nvl((select sum(o.box_quantity) from log2_appoint_carrier_order o 
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id 
left join log_base_organization org on org.id=c.receiver_id 
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) hh,
nvl((select sum(o.volume) from log2_appoint_carrier_order o 
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id 
left join log_base_organization org on org.id=c.receiver_id 
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) jj
from log_base_dc_infodetail dd
inner join log_base_dc_info dc on dc.id=dd.dcinfo_id and dc.dc_type='NDC' 
where dd.ability_start_time<trunc(sysdate) and dd.ability_end_time>trunc(sysdate) and dd.status='ACTIVE' 
union all 
select (select trunc(sysdate+1) from users where rownum <=1) aa,dc.dc_name bb,dd.category cc,dd.brand ee,dd.average_receive_capacity ff,dd.average_receive_volume gg,
nvl((select sum(o.box_quantity) from log2_appoint_carrier_order o 
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id 
left join log_base_organization org on org.id=c.receiver_id 
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate+1) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) hh,
nvl((select sum(o.volume) from log2_appoint_carrier_order o 
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id 
left join log_base_organization org on org.id=c.receiver_id 
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate+1) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) jj
from log_base_dc_infodetail dd
inner join log_base_dc_info dc on dc.id=dd.dcinfo_id and dc.dc_type='NDC' 
where dd.ability_start_time<trunc(sysdate+1) and dd.ability_end_time>trunc(sysdate+1) and dd.status='ACTIVE' 
  )temp 
group by temp.aa,temp.bb,temp.cc,temp.ee,temp.ff,temp.gg,temp.hh,temp.jj order by temp.aa asc

下面是转换为HQL的语句:

SELECT temp.aa as businessDate,temp.bb as dcName,temp.cc as category,temp.ee as brand,temp.ff as averageReceiveCapacity,temp.gg as averageReceiveVolume,temp.hh as boxQuantity,temp.jj as volume 
  FROM (SELECT 
  (SELECT trunc(sysdate) from User where rownum=1) as aa,dc.name as bb,dd.category as cc,dd.brand as ee,dd.averageReceiveCapacity as ff,dd.averageReceiveVolume as gg,
nvl((SELECT sum(o.boxQuantity) from AppointmentCarrierOrder o,NDCOrder ndc 
left join o.appointmentCarrier c 
left join c.receiver org where trunc(o.appointReceiveDate)=trunc(sysdate) and org.name=dc.name and o.status='APPROVED' 
  相关解决方案