version: 1.0.0-rc.1
SQL:SELECT * FROM ( select a.* ,b.ly from ( SELECT a.master_id, c.NAME, c.name2, a.up_type, a.up_rmtype, a.up_rmno, a.up_real_rate, a.rm_type, a.rm_no, a.rm_real_rate, a.modify_user, a.create_datetime, b.arr, b.dep, a.up_reason, b.dsc_reason, b.company_id, b.source_id, b.agent_id, a.rm_real_rate - a.up_real_rate, b.rsv_no, b.grp_accnt, a.remark FROM gc_gxpms_master_upgrade a LEFT JOIN gc_gxpms_master_base b ON a.master_id = b.id LEFT JOIN gc_gxpms_master_guest c ON a.master_id = c.id LEFT JOIN ( SELECT entity_id, min( id ), min( create_datetime ) create_datetime FROM gc_gxpms_log_info WHERE new_value = 'I' GROUP BY entity_id ) d ON d.entity_id = a.master_id WHERE a.up_rmno != a.rm_no AND ( d.create_datetime < a.create_datetime OR d.create_datetime IS NULL ) AND a.hotel_group_id = 2 AND a.hotel_id = $hotel_id$ AND b.hotel_group_id = 2 AND b.hotel_id = $hotel_id$ AND c.hotel_group_id = 2 AND c.hotel_id = $hotel_id$ AND b.sta IN ( 'I', 'O', 'S' ) AND a.create_datetime BETWEEN $bgn_date$ AND $end_date$ order by a.master_id desc ,a.create_datetime desc union all SELECT a.master_id, c.NAME, c.name2, a.up_type, a.up_rmtype, a.up_rmno, a.up_real_rate, a.rm_type, a.rm_no, a.rm_real_rate, a.modify_user, a.create_datetime, b.arr, b.dep, a.up_reason, b.dsc_reason, b.company_id, b.source_id, b.agent_id, a.rm_real_rate - a.up_real_rate, b.rsv_no, b.grp_accnt, a.remark FROM gc_lspms_master_upgrade a LEFT JOIN gc_lspms_master_base b ON a.master_id = b.id LEFT JOIN gc_lspms_master_guest c ON a.master_id = c.id LEFT JOIN ( SELECT entity_id, min( id ), min( create_datetime ) create_datetime FROM gc_lspms_log_info WHERE new_value = 'I' GROUP BY entity_id ) d ON d.entity_id = a.master_id WHERE a.up_rmno != a.rm_no AND ( d.create_datetime < a.create_datetime OR d.create_datetime IS NULL ) AND a.hotel_group_id = 2 AND a.hotel_id = $hotel_id$ AND b.hotel_group_id = 2 AND b.hotel_id = $hotel_id$ AND c.hotel_group_id = 2 AND c.hotel_id = $hotel_id$ AND b.sta IN ( 'I', 'O', 'S' ) AND a.create_datetime BETWEEN $bgn_date$ AND $end_date$ order by a.master_id desc ,a.create_datetime desc union all SELECT a.master_id, c.NAME, c.name2, a.up_type, a.up_rmtype, a.up_rmno, a.up_real_rate, a.rm_type, a.rm_no, a.rm_real_rate, a.modify_user, a.create_datetime, b.arr, b.dep, a.up_reason, b.dsc_reason, b.company_id, b.source_id, b.agent_id, a.rm_real_rate - a.up_real_rate, b.rsv_no, b.grp_accnt, a.remark FROM gc_gxpms_master_upgrade a LEFT JOIN gc_gxpms_master_base_history b ON a.master_id = b.id LEFT JOIN gc_gxpms_master_guest_history c ON a.master_id = c.id LEFT JOIN ( SELECT entity_id, min( id ), min( create_datetime ) create_datetime FROM gc_gxpms_log_info WHERE new_value = 'I' GROUP BY entity_id ) d ON d.entity_id = a.master_id WHERE a.up_rmno != a.rm_no AND ( d.create_datetime < a.create_datetime OR d.create_datetime IS NULL ) AND a.hotel_group_id = 2 AND a.hotel_id = $hotel_id$ AND b.hotel_group_id = 2 AND b.hotel_id = $hotel_id$ AND c.hotel_group_id = 2 AND c.hotel_id = $hotel_id$ AND b.sta IN ( 'I', 'O', 'S' ) AND a.create_datetime BETWEEN $bgn_date$ AND $end_date$ order by a.master_id desc ,a.create_datetime desc union all SELECT a.master_id, c.NAME, c.name2, a.up_type, a.up_rmtype, a.up_rmno, a.up_real_rate, a.rm_type, a.rm_no, a.rm_real_rate, a.modify_user, a.create_datetime, b.arr, b.dep, a.up_reason, b.dsc_reason, b.company_id, b.source_id, b.agent_id, a.rm_real_rate - a.up_real_rate, b.rsv_no, b.grp_accnt, a.remark FROM gc_lspms_master_upgrade a LEFT JOIN gc_lspms_master_base_history b ON a.master_id = b.id LEFT JOIN gc_lspms_master_guest_history c ON a.master_id = c.id LEFT JOIN ( SELECT entity_id, min( id ), min( create_datetime ) create_datetime FROM gc_lspms_log_info WHERE new_value = 'I' GROUP BY entity_id ) d ON d.entity_id = a.master_id WHERE a.up_rmno != a.rm_no AND ( d.create_datetime < a.create_datetime OR d.create_datetime IS NULL ) AND a.hotel_group_id = 2 AND a.hotel_id = $hotel_id$ AND b.hotel_group_id = 2 AND b.hotel_id = $hotel_id$ AND c.hotel_group_id = 2 AND c.hotel_id = $hotel_id$ AND b.sta IN ( 'I', 'O', 'S' ) AND a.create_datetime BETWEEN $bgn_date$ AND $end_date$ order by a.master_id desc ,a.create_datetime desc ) a left join (select parent_code,code,ly from ( select parent_code,code,ifnull(descript,descript_en) as ly from gc_gxgroup_code_base where hotel_id=$hotel_id$ and parent_code in('upgrade_reason','rmchg_reason') order by parent_code asc ) a group by code ) b on a.up_reason = b.code order by a.master_id desc,create_datetime ) AS DATART_VTABLE LIMIT 1000 OFFSET 0
DB: MYSQL
EXCEPTION:org.apache.calcite.sql.parser.SqlParseException: Encountered "union" at line 1, column 1160.
Was expecting one of:
"FETCH" ...
"LIMIT" ...
"NULLS" ...
"OFFSET" ...
")" ...
"," ...