-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Hibernate not translating Left join properly in FROM clause
PostPosted: Sun Mar 09, 2014 6:36 pm 
Newbie

Joined: Sun Mar 09, 2014 6:31 pm
Posts: 1
Hibernate (version 3.3.2, Jdk 1.7u51) somehow doesn’t translate the query to right format. Please look for the query when we include Full Outer Left join in it. The left join didn’t position correctly in generated SQL and hence it raise error as below. All left joins should be defined together rather scattered in FROM clause. Any help will be much appreciated!

Exception
com.navis.framework.util.BizFailure: could not execute query
with com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'unitfacili0_.intend_ob_cv' in 'on clause'
SQL STATE=42S22 SQL ERROR CODE=1054
could not execute query (compact stack trace follows)


Query

select
unit1_.id as col_0_0_, unitfacili0_.unit_gkey as col_1_0_, unitfacili0_.last_pos_loctype as col_2_0_, unitfacili0_.last_pos_locid as col_3_0_, unitfacili0_.last_pos_slot as col_4_0_,
unitfacili0_.last_pos_orientation as col_5_0_, unitfacili0_.transit_state as col_6_0_, unitfacili0_.time_in as col_7_0_, unitfacili0_.time_out as col_8_0_,
unitfacili0_.time_load as col_9_0_, unitfacili0_.time_move as col_10_0_, unitfacili0_.time_complete as col_11_0_, unit1_.seal_nbr1 as col_12_0_, unit1_.seal_nbr2 as col_13_0_,
unit1_.seal_nbr3 as col_14_0_, unit1_.seal_nbr4 as col_15_0_, group123_.id as col_16_0_, carriervis8_.id as col_17_0_, carriervis8_.carrier_mode as col_18_0_,
specialsto127_.id as col_19_0_, specialsto129_.id as col_20_0_, specialsto131_.id as col_21_0_, unit1_.freight_kind as col_22_0_, unit1_.goods_and_ctr_wt_kg as col_23_0_,
unit1_.goods_ctr_wt_kg_advised as col_24_0_, unit1_.goods_ctr_wt_kg_gate_measured as col_25_0_, unit1_.goods_ctr_wt_kg_yard_measured as col_26_0_, commodity139_.id as col_27_0_,
commodity139_.description as col_28_0_, unit1_.category as col_29_0_, unit1_.dray_status as col_30_0_, importdeli146_.id as col_31_0_, equiptype150_.rfr_type as col_32_0_,
goodsbase138_.temp_reqd_C as col_33_0_, goodsbase138_.temp_min_C as col_34_0_, goodsbase138_.temp_max_C as col_35_0_, goodsbase138_.temp_show_fahrenheit as col_36_0_,
goodsbase138_.O2_pct as col_37_0_, goodsbase138_.CO2_pct as col_38_0_, goodsbase138_.humidity_required_pct as col_39_0_, goodsbase138_.vent_required_pct as col_40_0_,
goodsbase138_.vent_unit as col_41_0_, goodsbase138_.on_power as col_42_0_, goodsbase138_.off_power_time as col_43_0_, unit1_.power_rqst_time as col_44_0_,
goodsbase138_.time_mon1 as col_45_0_, goodsbase138_.time_mon2 as col_46_0_, goodsbase138_.time_mon3 as col_47_0_, goodsbase138_.time_mon4 as col_48_0_,
goodsbase138_.ext_time_monitors as col_49_0_, goodsbase138_.unplug_warn_min as col_50_0_, unit1_.is_powered as col_51_0_, unit1_.want_powered as col_52_0_,
unit1_.requires_power as col_53_0_, unitequipm148_.eq_gkey as col_54_0_, equipment149_.class as col_55_0_, routingpoi195_.id as col_56_0_, routingpoi197_.id as col_57_0_,
routingpoi199_.id as col_58_0_, refcountry203_.cntry_name as col_59_0_, refcountry207_.cntry_name as col_60_0_, refcountry211_.cntry_name as col_61_0_,
routingpoi213_.id as col_62_0_, unit1_.pin_nbr as col_63_0_, unit1_.return_to_location as col_64_0_, scopedbizu217_.id as col_65_0_, goodsbase138_.destination as col_66_0_,
goodsbase138_.origin as col_67_0_, eqbaseorde225_.nbr as col_68_0_, eqbaseorde225_.sub_type as col_69_0_, eqbaseorde233_.nbr as col_70_0_, eqbaseorde233_.sub_type as col_71_0_,
unit1_.is_oog as col_72_0_, unit1_.oog_front_cm as col_73_0_, unit1_.oog_back_cm as col_74_0_, unit1_.oog_top_cm as col_75_0_, unit1_.oog_left_cm as col_76_0_,
unit1_.oog_right_cm as col_77_0_, scopedbizu245_.id as col_78_0_, scopedbizu249_.id as col_79_0_, scopedbizu253_.id as col_80_0_, unitfacili0_.gkey as col_81_0_,
goodsbase138_.hazards_gkey as col_82_0_, unit1_.remark as col_83_0_, scopedbizu259_.id as col_84_0_, scopedbizu262_.id as col_85_0_, goodsbase138_.bl_nbr as col_86_0_,
carriervis266_.id as col_87_0_, carriervis78_.id as col_88_0_, carriervis269_.id as col_89_0_, carriervis80_.id as col_90_0_, carriervis266_.carrier_mode as col_91_0_,
carriervis78_.carrier_mode as col_92_0_, carriervis269_.carrier_mode as col_93_0_, carriervis80_.carrier_mode as col_94_0_, facility85_.id as col_95_0_,
unit1_.flex_string01 as col_96_0_, unit1_.flex_string02 as col_97_0_, unit1_.flex_string03 as col_98_0_, unit1_.flex_string04 as col_99_0_, unit1_.flex_string05 as col_100_0_,
unit1_.flex_string06 as col_101_0_, unit1_.flex_string07 as col_102_0_, unit1_.flex_string08 as col_103_0_, unit1_.flex_string09 as col_104_0_, unit1_.flex_string10 as col_105_0_,
unit1_.flex_string11 as col_106_0_, unit1_.flex_string12 as col_107_0_, unit1_.flex_string13 as col_108_0_, unit1_.flex_string14 as col_109_0_, unit1_.flex_string15 as col_110_0_,
unitequipm148_.dmgs_gkey as col_111_0_, unit1_.foreignhost_key as col_112_0_, unitequipm148_.eqs_gkey as col_113_0_, unit1_.goods as col_114_0_,
unitfacili0_.flex_string01 as col_115_0_, unitfacili0_.flex_string02 as col_116_0_, unitfacili0_.flex_string03 as col_117_0_, unitfacili0_.flex_string04 as col_118_0_,
unitfacili0_.flex_string05 as col_119_0_, unitfacili0_.flex_string06 as col_120_0_, unitfacili0_.flex_string07 as col_121_0_, unitfacili0_.flex_string08 as col_122_0_,
unitfacili0_.flex_string09 as col_123_0_, unitfacili0_.flex_string10 as col_124_0_, unitfacili0_.flex_date01 as col_125_0_, unitfacili0_.flex_date02 as col_126_0_,
unitfacili0_.flex_date03 as col_127_0_, unitfacili0_.flex_date04 as col_128_0_, unitfacili0_.flex_date05 as col_129_0_, unitfacili0_.flex_date06 as col_130_0_,
unitfacili0_.flex_date07 as col_131_0_, unitfacili0_.flex_date08 as col_132_0_, mnrstatus301_.id as col_133_0_, equipcondi304_.id as col_134_0_, unitfacili0_.restow_typ as col_135_0_,
unitequipm148_.depart_order_item_gkey as col_136_0_, unitequipm148_.arrive_order_item_gkey as col_137_0_, scopedbizu310_.id as col_138_0_, scopedbizu312_.id as col_139_0_,
unitfacili0_.is_direct_ib_to_ob_move as col_140_0_, unitfacili0_.time_dlv_appmnt as col_141_0_, unitfacili0_.paid_thru_day as col_142_0_, unitfacili0_.last_free_day as col_143_0_,
unitfacili0_.guarantee_thru_day as col_144_0_, scopedbizu111_.id as col_145_0_, scopedbizu111_.role as col_146_0_, unitfacili0_.line_paid_thru_day as col_147_0_,
unitfacili0_.line_last_free_day as col_148_0_, unitfacili0_.line_guarantee_thru_day as col_149_0_, scopedbizu113_.id as col_150_0_, scopedbizu113_.role as col_151_0_,
unitfacili0_.power_paid_thru_day as col_152_0_, unitfacili0_.power_last_free_day as col_153_0_, unitfacili0_.power_guarantee_thru_day as col_154_0_,
scopedbizu115_.id as col_155_0_, scopedbizu115_.role as col_156_0_, unitfacili0_.gkey as col_157_0_
from
inv_unit_fcy_visit unitfacili0_
left outer join inv_unit unit1_ on unitfacili0_.unit_gkey = unit1_.gkey,

ref_groups group123_,
ref_special_stows specialsto127_,
ref_special_stows specialsto129_,
ref_special_stows specialsto131_,
inv_goods goodsbase138_,
ref_commodity commodity139_,
ref_bizunit_scoped scopedbizu259_,
ref_bizunit_scoped scopedbizu262_,
inv_import_delivery_orders importdeli146_,
inv_unit_equip unitequipm148_,
ref_equipment equipment149_,
ref_equip_type equiptype150_,
inv_eq_base_order_item eqbaseorde224_,
inv_eq_base_order eqbaseorde225_,
inv_eq_base_order_item eqbaseorde232_,
inv_eq_base_order eqbaseorde233_,
inv_eq_state equipments248_,
ref_bizunit_scoped scopedbizu249_,
ref_bizunit_scoped scopedbizu253_,
ref_mnr_status mnrstatus301_,
ref_equip_conditions equipcondi304_,
ref_routing_point routingpoi195_,
ref_unloc_code unloccode202_,
ref_country refcountry203_,
ref_routing_point routingpoi197_,
ref_unloc_code unloccode206_,
ref_country refcountry207_,
ref_routing_point routingpoi199_,
ref_unloc_code unloccode210_,
ref_country refcountry211_,
ref_routing_point routingpoi213_,
ref_bizunit_scoped scopedbizu217_,
ref_bizunit_scoped scopedbizu245_,
argo_carrier_visit carriervis266_,
argo_carrier_visit carriervis269_,
ref_bizunit_scoped scopedbizu310_,
ref_bizunit_scoped scopedbizu312_
left outer join argo_carrier_visit carriervis8_
on unitfacili0_.intend_ob_cv = carriervis8_.gkey
left outer join argo_carrier_visit carriervis78_
on unitfacili0_.actual_ib_cv = carriervis78_.gkey
left outer join argo_carrier_visit carriervis80_
on unitfacili0_.actual_ob_cv = carriervis80_.gkey
left outer join argo_facility facility85_
on unitfacili0_.fcy_gkey = facility85_.gkey
left outer join ref_bizunit_scoped scopedbizu111_
on unitfacili0_.guarantee_party_gkey = scopedbizu111_.gkey
left outer join ref_bizunit_scoped scopedbizu113_
on unitfacili0_.line_guarantee_party_gkey = scopedbizu113_.gkey
left outer join ref_bizunit_scoped scopedbizu115_
on unitfacili0_.power_guarantee_party_gkey = scopedbizu115_.gkey

where
unit1_.group_gkey = group123_.gkey and
unit1_.special_stow_gkey = specialsto127_.gkey and
unit1_.special_stow2_gkey = specialsto129_.gkey and
unit1_.special_stow3_gkey = specialsto131_.gkey and
unit1_.goods = goodsbase138_.gkey and
goodsbase138_.commodity_gkey = commodity139_.gkey and
goodsbase138_.shipper_bzu = scopedbizu259_.gkey and
goodsbase138_.consignee_bzu = scopedbizu262_.gkey and
unit1_.ido_gkey = importdeli146_.gkey and
unit1_.primary_ue = unitequipm148_.gkey and
unitequipm148_.eq_gkey = equipment149_.gkey and
equipment149_.eqtyp_gkey = equiptype150_.gkey and
unitequipm148_.depart_order_item_gkey = eqbaseorde224_.gkey and
eqbaseorde224_.eqo_gkey = eqbaseorde225_.gkey and
unitequipm148_.arrive_order_item_gkey = eqbaseorde232_.gkey and
eqbaseorde232_.eqo_gkey = eqbaseorde233_.gkey and
unitequipm148_.eqs_gkey = equipments248_.gkey and
equipments248_.eq_owner_gkey = scopedbizu249_.gkey and
equipments248_.eq_prev_operator_gkey = scopedbizu253_.gkey and
unitequipm148_.mnr_status_gkey = mnrstatus301_.gkey and
unitequipm148_.condition_gkey = equipcondi304_.gkey and
unit1_.pol_gkey = routingpoi195_.gkey and
routingpoi195_.unloc_gkey = unloccode202_.gkey and
unloccode202_.cntry_code = refcountry203_.cntry_code and
unit1_.pod1_gkey = routingpoi197_.gkey and
routingpoi197_.unloc_gkey = unloccode206_.gkey and
unloccode206_.cntry_code = refcountry207_.cntry_code and
unit1_.pod2_gkey = routingpoi199_.gkey and
routingpoi199_.unloc_gkey = unloccode210_.gkey and
unloccode210_.cntry_code = refcountry211_.cntry_code and
unit1_.opl_gkey = routingpoi213_.gkey and
unit1_.trucking_company = scopedbizu217_.gkey and
unit1_.line_op = scopedbizu245_.gkey and
unit1_.declrd_ib_cv = carriervis266_.gkey and
unit1_.cv_gkey = carriervis269_.gkey and
unit1_.agent1 = scopedbizu310_.gkey and
unit1_.agent2 = scopedbizu312_.gkey and
unitfacili0_.fcy_gkey = ? and
(unitfacili0_.gkey in (?,
?,
?,
?))


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.