Blocking when an SQL is explained

[xyxiaoyou]

When I executed the SQL('create or replace view XY.dv_cube_test as ......') on Spark2.3.4 alone, everything was fine; When Connecting to SnappyData V1.1.1 using the JDBC Extension Connector, it gets stuck, causing a gradual increase in CPU load.

`spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.currency_rates (id varchar(255) ,tenant_id varchar(255),tenant_curr_code string,shop_curr_code string,exchange_rate string,start_date string ,end_date string ,del_flag string ,remark string,create_user_id string,update_user_id string,create_date string,update_date string,version string)")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.pos_custom_types ( ctyp_id varchar(255),tenant_id varchar(255),ctyp_gb_cz3_cfgz_id string ,ctyp_gb_cz2_cfgz_id string ,ctyp_short_name_l1 string ,created string ,ctyp_name_l4 string ,ctyp_name_l3 string ,ctyp_name_l5 string ,ctyp_name_l2 string ,ctyp_name_l1 string ,ctyp_type string ,ctyp_seq string ,ctyp_gb_extra_langs string ,ctyp_short_name_l5 string ,ctyp_short_name_l4 string ,modified string ,ctyp_short_name_l3 string ,ctyp_short_name_l2 string ,ctyp_status string ,ctyp_code string ,ctyp_gb_cz1_cfgz_id string ,sdp_instorage_time string )")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.out_shops ( shop_id varchar(255), tenant_id varchar(255), shop_name_l2 string , shop_short_name_l4 string , shop_name_l1 string , shop_short_name_l5 string , shop_name_l4 string , shop_name_l3 string , shop_code string , shop_name_l5 string , shop_loca_id string , shop_timezone string , shop_bran_id string , shop_gb_cz2_cfgz_id string , modified string , shop_website string , shop_restriction_key string , shop_short_name_l1 string , shop_gb_cz1_cfgz_id string , shop_short_name_l2 string , shop_short_name_l3 string , shop_fax string , shop_status string , shop_seq string , created string , shop_gb_cz3_cfgz_id string , shop_addr_l4 string , shop_addr_l3 string , shop_addr_l5 string , shop_info_l2 string , shop_info_l3 string , shop_addr_l2 string , shop_addr_l1 string , shop_info_l1 string , shop_info_l4 string , shop_info_l5 string , shop_phone string , sdp_instorage_time string , shop_short_name_x1 string , status string , currency_id string , shop_gb_extra_langs string , shop_timezone_name string , shop_short_name_x2 string , shop_short_name_x3 string , shop_short_name_x4 string , shop_short_name_x5 string , shop_tin string )")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.out_outlets( olet_id varchar(255), shop_id varchar(255), tenant_id varchar(255), olet_seq string , olet_addr_l5 string , olet_addr_l2 string , olet_addr_l1 string , olet_addr_l4 string , olet_fax string , olet_addr_l3 string , olet_short_name_l1 string , olet_short_name_l5 string , olet_short_name_l4 string , olet_short_name_l3 string , olet_short_name_l2 string , olet_gb_cz2_cfgz_id string , olet_gb_cz3_cfgz_id string , modified string , olet_code string , olet_date_format string , olet_phone string , created string , olet_bran_id string , olet_status string , olet_website string , olet_currency_code string , olet_gb_cz1_cfgz_id string , olet_name_l1 string , olet_currency_sign string , olet_name_l2 string , olet_name_l3 string , olet_name_l4 string , olet_name_l5 string , olet_phone_area_code string , olet_loca_id string , sdp_instorage_time string , olet_gb_extra_langs string , olet_info_l1 string , olet_info_l2 string , olet_info_l3 string , olet_info_l4 string , olet_info_l5 string , operating_area string , table_number string , seat_number string )")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.pos_checks( chks_id varchar(255), tenant_id varchar(255), chks_resv_refno_with_prefix string , chks_bper_id string , chks_txn string , chks_check_prefix string , chks_incl_tax_ref2 string , chks_incl_tax_ref3 string , chks_incl_tax_ref1 string , chks_incl_tax_ref4 string , chks_open_time string , chks_item_total string , chks_surcharge_total string , chks_close_time string , chks_bday_date string , chks_void_vdrs_id string , chks_tips_total string , chks_sync_id string , chks_aif string , chks_post_disc string , chks_bday_id string , chks_slave_created string , chks_modified_time string , chks_olet_id string , chks_close_user_id string , chks_void_loctime string , chks_slave_modified string , chks_void_user_id string , chks_guests string , chks_non_revenue string , chks_modified_loctime string , chks_close_loctime string , chks_modified_user_id string , chks_owner_user_id string , chks_close_bper_id string , chks_ctbl_id string , chks_tax3 string , chks_tax4 string , chks_tax1 string , chks_tax2 string , chks_sc2 string , chks_tax7 string , chks_sc3 string , chks_tax8 string , chks_sc4 string , chks_tax5 string , chks_sc5 string , chks_tax6 string , chks_void_time string , chks_tax19 string , chks_tax18 string , chks_ordering_mode string , chks_tax24 string , chks_tax23 string , chks_memb_id string , chks_settle_shift_num string , chks_print_user_id string , chks_tax22 string , chks_tax21 string , chks_payment_total string , chks_tax25 string , modified string , chks_print_loctime string , chks_sc1 string , chks_tax20 string , chks_check_num string , chks_lock_loctime string , chks_status string , chks_open_user_id string , created string , chks_open_loctime string , chks_print_stat_id string , chks_tax13 string , chks_tax12 string , chks_remark string , chks_tax11 string , chks_tax10 string , chks_mid_disc string , chks_ctyp_id string , chks_tax17 string , chks_tax16 string , chks_tax15 string , chks_tax14 string , chks_open_stat_id string , chks_slave_id string , chks_tax9 string , chks_print_time string , chks_resv_book_date string , chks_lock_user_id string , chks_pre_disc string , chks_gratuity_total string , chks_check_prefix_num string , chks_party_count string , chks_lock_stat_id string , chks_children string , chks_check_total string , chks_receipt_print_count string , chks_round_amount string , chks_lock_time string , chks_paid string , chks_void_stat_id string , chks_ordering_type string , chks_sect_id string , chks_close_stat_id string , chks_modified_stat_id string , chks_print_count string , sdp_instorage_time string , shop_id string , chks_gen_check_num_type string )")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.pos_check_tax_sc_refs( ctsr_id varchar(255), tenant_id varchar(255), ctsr_olet_id string , ctsr_by string , created string , ctsr_citm_id string , ctsr_chks_id string , ctsr_slave_created string , ctsr_variable string , ctsr_slave_id string , ctsr_sync_id string , ctsr_bper_id string , sdp_instorage_time string , modified string , ctsr_status string , ctsr_value string , ctsr_slave_modified string )")

spark.snappyExecute("CREATE TABLE IF NOT EXISTS XY.pos_check_items( citm_id varchar(255), tenant_id varchar(255), citm_tax16 string , citm_tax17 string , citm_tax18 string , citm_tax19 string , citm_post_disc string , citm_print_queue2_itpq_id string , citm_order_stat_id string , citm_base_qty string , citm_serving_status string , citm_tax12 string , citm_bday_date string , citm_tax13 string , citm_print_queue6_itpq_id string , citm_tax14 string , citm_tax15 string , citm_tax10 string , citm_tax11 string , citm_original_price string , citm_charge_tax4 string , citm_mix_and_match_citm_id string , citm_pre_disc string , citm_charge_tax5 string , citm_charge_tax6 string , citm_charge_tax7 string , citm_void_consumed string , citm_slave_id string , citm_revenue string , citm_charge_tax1 string , citm_charge_tax2 string , citm_order_time string , citm_charge_tax3 string , citm_chks_id string , citm_mid_disc string , citm_charge_tax8 string , citm_charge_tax9 string , citm_code string , citm_idep_id string , citm_incl_tax_ref1 string , citm_incl_tax_ref2 string , citm_incl_tax_ref3 string , citm_incl_tax_ref4 string , citm_olet_id string , citm_tax1 string , citm_tax2 string , citm_print_queue3_itpq_id string , citm_basic_calculate_method string , citm_chks_non_revenue string , citm_round_amount string , citm_order_user_id string , citm_tax9 string , citm_tax7 string , citm_tax8 string , citm_tax5 string , citm_tax6 string , citm_order_loctime string , citm_tax3 string , citm_total string , citm_tax4 string , citm_status string , citm_print_queue7_itpq_id string , citm_carry_total string , citm_price string , citm_no_print string , citm_parent_citm_id string , citm_seat string , citm_bday_id string , citm_print_queue10_itpq_id string , citm_icou_id string , citm_qty string , citm_tax23 string , citm_tax24 string , citm_item_id string , citm_tax25 string , citm_void_stat_id string , citm_tax20 string , citm_pending string , citm_tax21 string , citm_tax22 string , citm_name_l5 string , citm_name_l4 string , citm_basic_price string , citm_name_l1 string , citm_charge_sc5 string , citm_round_total string , citm_name_l3 string , citm_charge_sc3 string , citm_name_l2 string , citm_charge_sc4 string , citm_charge_sc1 string , citm_icat_id string , citm_charge_sc2 string , citm_original_price_level string , citm_print_queue4_itpq_id string , citm_void_vdrs_id string , modified string , citm_digp_id string , citm_slave_modified string , citm_charge_tax10 string , citm_chks_ordering_type string , citm_sc1 string , citm_print_queue8_itpq_id string , citm_charge_tax14 string , citm_charge_tax13 string , citm_sc3 string , citm_charge_tax12 string , citm_sc2 string , citm_charge_tax11 string , citm_sc5 string , citm_charge_tax18 string , citm_sc4 string , citm_charge_tax17 string , citm_charge_tax16 string , created string , citm_modifier_count string , citm_charge_tax15 string , citm_rush_loctime string , citm_charge_tax19 string , citm_original_olet_id string , citm_rush_time string , citm_hide string , citm_price_level string , citm_slave_created string , citm_rush_stat_id string , citm_unit_cost string , citm_charge_tax21 string , citm_charge_tax20 string , citm_rush_user_id string , citm_charge_tax25 string , citm_ctyp_id string , citm_charge_tax24 string , citm_charge_tax23 string , citm_sync_id string , citm_role string , citm_charge_tax22 string , citm_delivery_time string , citm_print_queue1_itpq_id string , citm_round_status string , citm_short_name_l1 string , citm_chks_guests string , citm_cpty_id string , citm_short_name_l2 string , citm_short_name_l5 string , citm_short_name_l3 string , citm_short_name_l4 string , citm_seq string , citm_print_queue5_itpq_id string , citm_print_queue9_itpq_id string , citm_rush_count string , citm_get_revenue string , citm_child_count string , citm_void_loctime string , citm_void_time string , citm_void_user_id string , citm_carry_revenue string , citm_bper_id string , citm_ordering_type string , sdp_instorage_time string , shop_id string )")

spark.snappyExecute("create or replace view XY.dv_pos_checks as SELECT t1.tenant_id as tenant_id , t1.chks_id as chks_id , t1.chks_bday_id as chks_bday_id , t1.chks_bday_date as bday_date, t1.chks_bper_id as chks_bper_id , t1.shop_id as shop_id , t1.chks_olet_id as chks_olet_id , t1.chks_sect_id as chks_sect_id , cast(t1.chks_aif as integer) as chks_aif, cast(t1.chks_txn as integer) as chks_txn, t1.chks_check_prefix as chks_check_prefix , cast(t1.chks_check_num as integer) as chks_check_num, t1.chks_check_prefix_num as chks_check_prefix_num , t1.chks_ctbl_id as chks_ctbl_id , cast(t1.chks_guests as integer) as chks_guests, cast(t1.chks_children as integer) as chks_children, cast(t1.chks_print_count as integer) as chks_print_count, cast(t1.chks_receipt_print_count as integer) as chks_receipt_print_count, cast(t1.chks_party_count as integer) as chks_party_count, cast(t1.chks_check_total as decimal(15,4)) as chks_check_total, cast(t1.chks_item_total as decimal(15,4)) as chks_item_total, cast(t1.chks_sc1 as decimal(15,4)) as chks_sc1, cast(t1.chks_sc2 as decimal(15,4)) as chks_sc2, cast(t1.chks_sc3 as decimal(15,4)) as chks_sc3, cast(t1.chks_sc4 as decimal(15,4)) as chks_sc4, cast(t1.chks_sc5 as decimal(15,4)) as chks_sc5, cast(t1.chks_tax1 as decimal(15,4)) as chks_tax1, cast(t1.chks_tax2 as decimal(15,4)) as chks_tax2, cast(t1.chks_tax3 as decimal(15,4)) as chks_tax3, cast(t1.chks_tax4 as decimal(15,4)) as chks_tax4, cast(t1.chks_tax5 as decimal(15,4)) as chks_tax5, cast(t1.chks_tax6 as decimal(15,4)) as chks_tax6, cast(t1.chks_tax7 as decimal(15,4)) as chks_tax7, cast(t1.chks_tax8 as decimal(15,4)) as chks_tax8, cast(t1.chks_tax9 as decimal(15,4)) as chks_tax9, cast(t1.chks_tax10 as decimal(15,4)) as chks_tax10, cast(t1.chks_tax11 as decimal(15,4)) as chks_tax11, cast(t1.chks_tax12 as decimal(15,4)) as chks_tax12, cast(t1.chks_tax13 as decimal(15,4)) as chks_tax13, cast(t1.chks_tax14 as decimal(15,4)) as chks_tax14, cast(t1.chks_tax15 as decimal(15,4)) as chks_tax15, cast(t1.chks_tax16 as decimal(15,4)) as chks_tax16, cast(t1.chks_tax17 as decimal(15,4)) as chks_tax17, cast(t1.chks_tax18 as decimal(15,4)) as chks_tax18, cast(t1.chks_tax19 as decimal(15,4)) as chks_tax19, cast(t1.chks_tax20 as decimal(15,4)) as chks_tax20, cast(t1.chks_tax21 as decimal(15,4)) as chks_tax21, cast(t1.chks_tax22 as decimal(15,4)) as chks_tax22, cast(t1.chks_tax23 as decimal(15,4)) as chks_tax23, cast(t1.chks_tax24 as decimal(15,4)) as chks_tax24, cast(t1.chks_tax25 as decimal(15,4)) as chks_tax25, cast(t1.chks_incl_tax_ref1 as decimal(15,4)) as chks_incl_tax_ref1, cast(t1.chks_incl_tax_ref2 as decimal(15,4)) as chks_incl_tax_ref2, cast(t1.chks_incl_tax_ref3 as decimal(15,4)) as chks_incl_tax_ref3, cast(t1.chks_incl_tax_ref4 as decimal(15,4)) as chks_incl_tax_ref4, cast(t1.chks_pre_disc as decimal(15,4)) as chks_pre_disc, cast(t1.chks_mid_disc as decimal(15,4)) as chks_mid_disc, cast(t1.chks_post_disc as decimal(15,4)) as chks_post_disc, cast(t1.chks_gratuity_total as decimal(15,4)) as chks_gratuity_total, cast(t1.chks_round_amount as decimal(15,4)) as chks_round_amount, cast(t1.chks_payment_total as decimal(15,4)) as chks_payment_total, cast(t1.chks_tips_total as decimal(15,4)) as chks_tips_total, cast(t1.chks_surcharge_total as decimal(15,4)) as chks_surcharge_total, t1.chks_paid as chks_paid , t1.chks_resv_book_date as chks_resv_book_date , t1.chks_resv_refno_with_prefix as chks_resv_refno_with_prefix , t1.chks_ordering_type as chks_ordering_type , t1.chks_ordering_mode as chks_ordering_mode , t1.chks_non_revenue as chks_non_revenue , t1.chks_memb_id as chks_memb_id , t1.chks_ctyp_id as chks_ctyp_id , cast(t1.chks_settle_shift_num as integer) as chks_settle_shift_num, t1.chks_remark as chks_remark , case when t1.chks_open_time = 'null' or t1.chks_open_time = '' then null else t1.chks_open_time end as chks_open_time , case when t1.chks_open_loctime = 'null' or t1.chks_open_loctime = '' then null else t1.chks_open_loctime end as chks_open_loctime , t1.chks_open_user_id as chks_open_user_id , t1.chks_open_stat_id as chks_open_stat_id , case when t1.chks_close_time = 'null' or t1.chks_close_time = '' then null else t1.chks_close_time end as chks_close_time , case when t1.chks_close_loctime = 'null' or t1.chks_close_loctime = '' then null else t1.chks_close_loctime end as chks_close_loctime , t1.chks_close_user_id as chks_close_user_id , t1.chks_close_stat_id as chks_close_stat_id , t1.chks_close_bper_id as chks_close_bper_id , t1.chks_modified_time as chks_modified_time , t1.chks_modified_loctime as chks_modified_loctime , t1.chks_modified_user_id as chks_modified_user_id , t1.chks_modified_stat_id as chks_modified_stat_id , t1.chks_print_time as chks_print_time , t1.chks_print_loctime as chks_print_loctime , t1.chks_print_user_id as chks_print_user_id , t1.chks_print_stat_id as chks_print_stat_id , t1.chks_lock_time as chks_lock_time , t1.chks_lock_loctime as chks_lock_loctime , t1.chks_lock_user_id as chks_lock_user_id , t1.chks_lock_stat_id as chks_lock_stat_id , t1.chks_void_time as chks_void_time , t1.chks_void_loctime as chks_void_loctime , t1.chks_void_user_id as chks_void_user_id , t1.chks_void_stat_id as chks_void_stat_id , t1.chks_void_vdrs_id as chks_void_vdrs_id , t1.chks_owner_user_id as chks_owner_user_id , COALESCE(t1.chks_status,'') as chks_status , t1.chks_slave_id as chks_slave_id , t1.chks_slave_created as chks_slave_created , t1.chks_slave_modified as chks_slave_modified , t1.chks_sync_id as chks_sync_id , t1.created as created , t1.modified as modified FROM pos_checks t1 ")

spark.snappyExecute("create or replace view XY.dv_out_outlets as SELECT concat(coalesce(olet_name_l1,''),'$mls$',coalesce(olet_name_l2,''),'$mls$',coalesce(olet_name_l3,''),'$mls$',coalesce( olet_name_l4,''), '$mls$',coalesce( olet_name_l5,'') ) AS olet_name, concat(coalesce(olet_short_name_l1,''),'$mls$',coalesce(olet_short_name_l2,''),'$mls$',coalesce(olet_short_name_l3,''),'$mls$',coalesce( olet_short_name_l4,''), '$mls$',coalesce( olet_short_name_l5,'') ) AS olet_short_name, concat(coalesce(olet_addr_l1,''),'$mls$',coalesce(olet_addr_l2,''),'$mls$',coalesce(olet_addr_l3,''),'$mls$',coalesce( olet_addr_l4,''), '$mls$',coalesce( olet_addr_l5,'') ) AS olet_addr, concat(coalesce(olet_info_l1,''),'$mls$',coalesce(olet_info_l2,''),'$mls$',coalesce(olet_info_l3,''),'$mls$',coalesce( olet_info_l4,''), '$mls$',coalesce( olet_info_l5,'') ) AS olet_info, t1.tenant_id as tenant_id , t1.olet_id as olet_id , t1.shop_id as shop_id , t1.olet_code as olet_code , t1.olet_name_l1 as olet_name_l1 , t1.olet_name_l2 as olet_name_l2 , t1.olet_name_l3 as olet_name_l3 , t1.olet_name_l4 as olet_name_l4 , t1.olet_name_l5 as olet_name_l5 , t1.olet_short_name_l1 as olet_short_name_l1 , t1.olet_short_name_l2 as olet_short_name_l2 , t1.olet_short_name_l3 as olet_short_name_l3 , t1.olet_short_name_l4 as olet_short_name_l4 , t1.olet_short_name_l5 as olet_short_name_l5 , cast(t1.olet_seq as int) as olet_seq, t1.olet_addr_l1 as olet_addr_l1 , t1.olet_addr_l2 as olet_addr_l2 , t1.olet_addr_l3 as olet_addr_l3 , t1.olet_addr_l4 as olet_addr_l4 , t1.olet_addr_l5 as olet_addr_l5 , t1.olet_info_l1 as olet_info_l1 , t1.olet_info_l2 as olet_info_l2 , t1.olet_info_l3 as olet_info_l3 , t1.olet_info_l4 as olet_info_l4 , t1.olet_info_l5 as olet_info_l5 , t1.olet_phone as olet_phone , t1.olet_fax as olet_fax , t1.olet_website as olet_website , t1.olet_bran_id as olet_bran_id , t1.olet_loca_id as olet_loca_id , t1.olet_currency_sign as olet_currency_sign , t1.olet_currency_code as olet_currency_code , cast(t1.olet_date_format as int) as olet_date_format, t1.olet_phone_area_code as olet_phone_area_code , COALESCE(t1.olet_status,'') as olet_status , t1.created as created , t1.modified as modified , cast(t1.seat_number as decimal(15,4)) as seat_number, cast(t1.operating_area as decimal(15,4)) as operating_area, cast(case when t1.table_number = '' then null else t1.table_number end as decimal(15,4)) as table_number FROM out_outlets t1")

spark.snappyExecute("create or replace view XY.dv_out_shops as SELECT concat(coalesce(shop_short_name_x1,''),'$mls$',coalesce(shop_short_name_x1,''),'$mls$',coalesce(shop_short_name_x1,''),'$mls$',coalesce( shop_short_name_x1,''), '$mls$',coalesce( shop_short_name_x1,'') ) AS shop_name, concat(coalesce(shop_short_name_l1,''),'$mls$',coalesce(shop_short_name_l2,''),'$mls$',coalesce(shop_short_name_l3,''),'$mls$',coalesce( shop_short_name_l4,''), '$mls$',coalesce( shop_short_name_l5,'') ) AS shop_short_name, concat(coalesce(shop_addr_l1,''),'$mls$',coalesce(shop_addr_l2,''),'$mls$',coalesce(shop_addr_l3,''),'$mls$',coalesce( shop_addr_l4,''), '$mls$',coalesce( shop_addr_l5,'') ) AS shop_addr, concat(coalesce(shop_info_l1,''),'$mls$',coalesce(shop_info_l2,''),'$mls$',coalesce(shop_info_l3,''),'$mls$',coalesce( shop_info_l4,''), '$mls$',coalesce( shop_info_l5,'') ) AS shop_info, t1.tenant_id as tenant_id , t1.shop_id as shop_id , t1.shop_code as shop_code , t1.currency_id as currency_id, t1.shop_short_name_x1 as shop_name_l1 , t1.shop_name_l2 as shop_name_l2 , t1.shop_name_l3 as shop_name_l3 , t1.shop_name_l4 as shop_name_l4 , t1.shop_name_l5 as shop_name_l5 , t1.shop_short_name_l1 as shop_short_name_l1 , t1.shop_short_name_l2 as shop_short_name_l2 , t1.shop_short_name_l3 as shop_short_name_l3 , t1.shop_short_name_l4 as shop_short_name_l4 , t1.shop_short_name_l5 as shop_short_name_l5 , cast(t1.shop_seq as integer) as shop_seq, t1.shop_addr_l1 as shop_addr_l1 , t1.shop_addr_l2 as shop_addr_l2 , t1.shop_addr_l3 as shop_addr_l3 , t1.shop_addr_l4 as shop_addr_l4 , t1.shop_addr_l5 as shop_addr_l5 , t1.shop_info_l1 as shop_info_l1 , t1.shop_info_l2 as shop_info_l2 , t1.shop_info_l3 as shop_info_l3 , t1.shop_info_l4 as shop_info_l4 , t1.shop_info_l5 as shop_info_l5 , t1.shop_phone as shop_phone , t1.shop_fax as shop_fax , t1.shop_website as shop_website , cast(t1.shop_timezone as integer) as shop_timezone, t1.shop_restriction_key as shop_restriction_key , t1.shop_bran_id as shop_bran_id , t1.shop_loca_id as shop_loca_id , COALESCE(t1.shop_status,'') as shop_status , COALESCE(t1.status,'') as status, t1.created as created , t1.modified as modified FROM out_shops t1")

spark.snappyExecute("create or replace view XY.dv_currency_rates as select t1.tenant_id as tenant_id ,t1.id as id ,t1.del_flag as del_flag ,t1.create_user_id as create_user_id ,cast (t1.exchange_rate as decimal(15,4) ) as exchange_rate ,t1.remark as remark ,t1.version as version ,t1.tenant_curr_code as tenant_curr_code ,t1.update_date as update_date ,t1.update_user_id as update_user_id ,t1.shop_curr_code as shop_curr_code ,t1.create_date as create_date ,t1.start_date as start_date ,t1.end_date as end_date from currency_rates t1 where del_flag = '0'")

spark.snappyExecute("create or replace view XY.dv_pos_custom_types as SELECT concat(coalesce(ctyp_name_l1,''),'$mls$',coalesce(ctyp_name_l2,''),'$mls$',coalesce(ctyp_name_l3,''),'$mls$',coalesce( ctyp_name_l4,''), '$mls$',coalesce( ctyp_name_l5,'') ) AS ctyp_name, concat(coalesce(ctyp_short_name_l1,''),'$mls$',coalesce(ctyp_short_name_l2,''),'$mls$',coalesce(ctyp_short_name_l3,''),'$mls$',coalesce( ctyp_short_name_l4,''), '$mls$',coalesce( ctyp_short_name_l5,'') ) AS ctyp_short_name, t1.tenant_id as tenant_id , t1.ctyp_id as ctyp_id , t1.ctyp_type as ctyp_type , t1.ctyp_code as ctyp_code , t1.ctyp_name_l1 as ctyp_name_l1 , t1.ctyp_name_l2 as ctyp_name_l2 , t1.ctyp_name_l3 as ctyp_name_l3 , t1.ctyp_name_l4 as ctyp_name_l4 , t1.ctyp_name_l5 as ctyp_name_l5 , t1.ctyp_short_name_l1 as ctyp_short_name_l1 , t1.ctyp_short_name_l2 as ctyp_short_name_l2 , t1.ctyp_short_name_l3 as ctyp_short_name_l3 , t1.ctyp_short_name_l4 as ctyp_short_name_l4 , t1.ctyp_short_name_l5 as ctyp_short_name_l5 , cast(t1.ctyp_seq as integer) as ctyp_seq, COALESCE(t1.ctyp_status,'') as ctyp_status , t1.created as created , t1.modified as modified FROM pos_custom_types t1")

spark.snappyExecute("create or replace view XY.dv_pos_check_tax_sc_refs_currency as SELECT t1.tenant_id as tenant_id, t1.ctsr_olet_id as olet_id, COALESCE(t1.ctsr_by ,'') as ctsr_by, t1.ctsr_citm_id as ctsr_citm_id, t1.ctsr_chks_id as ctsr_chks_id, t1.ctsr_id as ctsr_id, t1.ctsr_slave_id as ctsr_slave_id, t1.ctsr_sync_id as ctsr_sync_id, t1.ctsr_bper_id as ctsr_bper_id, COALESCE(t1.ctsr_variable ,'') as ctsr_variable, COALESCE(t1.ctsr_status ,'') as ctsr_status, COALESCE(CAST(t1.ctsr_value AS decimal(15,4)),0) as ctsr_value , (CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (COALESCE(CAST(t1.ctsr_value AS decimal(15,4)),0) * currency_rates.exchange_rate) END) as cury_ctsr_value , t1.created as created, t1.modified as modified, t1.sdp_instorage_time as sdp_instorage_time, t1.ctsr_slave_created as ctsr_slave_created, t1.ctsr_slave_modified as ctsr_slave_modified FROM pos_check_tax_sc_refs t1 LEFT JOIN dv_out_outlets out_outlets ON t1.ctsr_olet_id = out_outlets.olet_id AND t1.tenant_id = out_outlets.tenant_id LEFT JOIN dv_out_shops out_shops ON out_outlets.shop_id = out_shops.shop_id AND out_outlets.tenant_id = out_shops.tenant_id LEFT JOIN dv_currency_rates currency_rates ON currency_rates.tenant_id = out_shops.tenant_id AND currency_rates.shop_curr_code = out_shops.currency_id AND currency_rates.del_flag = '0' WHERE t1.ctsr_slave_created >= currency_rates.start_date AND t1.ctsr_slave_created < COALESCE(currency_rates.end_date,'2099-12-12') and out_shops.shop_status <> 'd' and out_shops.status = '1'")

spark.snappyExecute("create or replace view XY.dv_pos_check_items as SELECT t1.tenant_id as tenant_id , t1.citm_id as citm_id , t1.citm_bday_id as citm_bday_id , t1.citm_bday_date as bday_date , t1.citm_bper_id as citm_bper_id , t1.shop_id as shop_id , t1.citm_olet_id as citm_olet_id , t1.citm_chks_id as citm_chks_id , t1.citm_cpty_id as citm_cpty_id , t1.citm_item_id as citm_item_id , t1.citm_code as citm_code , coalesce(citm_name_l1,'') as citm_name_l1 , coalesce(citm_name_l2,'') as citm_name_l2 , coalesce(citm_name_l3,'') as citm_name_l3 , coalesce(citm_name_l4,'') as citm_name_l4 , coalesce(citm_name_l5,'') as citm_name_l5 , coalesce(citm_short_name_l1,'') as citm_short_name_l1 , coalesce(citm_short_name_l2,'') as citm_short_name_l2 , coalesce(citm_short_name_l3,'') as citm_short_name_l3 , coalesce(citm_short_name_l4,'') as citm_short_name_l4 , coalesce(citm_short_name_l5,'') as citm_short_name_l5 , t1.citm_parent_citm_id as citm_parent_citm_id , t1.citm_role as citm_role , cast(t1.citm_child_count as integer) as citm_child_count, cast(t1.citm_modifier_count as integer) as citm_modifier_count, cast(t1.citm_seat as integer) as citm_seat, t1.citm_mix_and_match_citm_id as citm_mix_and_match_citm_id , cast(t1.citm_seq as integer) as citm_seq, cast(t1.citm_round_total as decimal(15,4)) as citm_round_total, cast(t1.citm_total as decimal(15,4)) as citm_total, cast(t1.citm_round_amount as decimal(15,4)) as citm_round_amount, cast(t1.citm_carry_total as decimal(15,4)) as citm_carry_total, cast(t1.citm_qty as decimal(15,4)) as citm_qty, cast(t1.citm_base_qty as decimal(15,4)) as citm_base_qty, cast(t1.citm_price as decimal(15,4)) as citm_price, cast(t1.citm_original_price as decimal(15,4)) as citm_original_price, cast(t1.citm_basic_price as decimal(15,4)) as citm_basic_price, t1.citm_basic_calculate_method as citm_basic_calculate_method , cast(t1.citm_sc1 as decimal(15,4)) as citm_sc1, cast(t1.citm_sc2 as decimal(15,4)) as citm_sc2, cast(t1.citm_sc3 as decimal(15,4)) as citm_sc3, cast(t1.citm_sc4 as decimal(15,4)) as citm_sc4, cast(t1.citm_sc5 as decimal(15,4)) as citm_sc5, cast(t1.citm_tax1 as decimal(15,4)) as citm_tax1, cast(t1.citm_tax2 as decimal(15,4)) as citm_tax2, cast(t1.citm_tax3 as decimal(15,4)) as citm_tax3, cast(t1.citm_tax4 as decimal(15,4)) as citm_tax4, cast(t1.citm_tax5 as decimal(15,4)) as citm_tax5, cast(t1.citm_tax6 as decimal(15,4)) as citm_tax6, cast(t1.citm_tax7 as decimal(15,4)) as citm_tax7, cast(t1.citm_tax8 as decimal(15,4)) as citm_tax8, cast(t1.citm_tax9 as decimal(15,4)) as citm_tax9, cast(t1.citm_tax10 as decimal(15,4)) as citm_tax10, cast(t1.citm_tax11 as decimal(15,4)) as citm_tax11, cast(t1.citm_tax12 as decimal(15,4)) as citm_tax12, cast(t1.citm_tax13 as decimal(15,4)) as citm_tax13, cast(t1.citm_tax14 as decimal(15,4)) as citm_tax14, cast(t1.citm_tax15 as decimal(15,4)) as citm_tax15, cast(t1.citm_tax16 as decimal(15,4)) as citm_tax16, cast(t1.citm_tax17 as decimal(15,4)) as citm_tax17, cast(t1.citm_tax18 as decimal(15,4)) as citm_tax18, cast(t1.citm_tax19 as decimal(15,4)) as citm_tax19, cast(t1.citm_tax20 as decimal(15,4)) as citm_tax20, cast(t1.citm_tax21 as decimal(15,4)) as citm_tax21, cast(t1.citm_tax22 as decimal(15,4)) as citm_tax22, cast(t1.citm_tax23 as decimal(15,4)) as citm_tax23, cast(t1.citm_tax24 as decimal(15,4)) as citm_tax24, cast(t1.citm_tax25 as decimal(15,4)) as citm_tax25, cast(t1.citm_incl_tax_ref1 as decimal(15,4)) as citm_incl_tax_ref1, cast(t1.citm_incl_tax_ref2 as decimal(15,4)) as citm_incl_tax_ref2, cast(t1.citm_incl_tax_ref3 as decimal(15,4)) as citm_incl_tax_ref3, cast(t1.citm_incl_tax_ref4 as decimal(15,4)) as citm_incl_tax_ref4, cast(t1.citm_pre_disc as decimal(15,4)) as citm_pre_disc, cast(t1.citm_mid_disc as decimal(15,4)) as citm_mid_disc, cast(t1.citm_post_disc as decimal(15,4)) as citm_post_disc, cast(t1.citm_original_price_level as integer) as citm_original_price_level, cast(t1.citm_price_level as integer) as citm_price_level, cast(t1.citm_revenue as decimal(15,4)) as citm_revenue, cast(t1.citm_carry_revenue as decimal(15,4)) as citm_carry_revenue, cast(t1.citm_unit_cost as decimal(15,4)) as citm_unit_cost, t1.citm_print_queue1_itpq_id as citm_print_queue1_itpq_id , t1.citm_print_queue2_itpq_id as citm_print_queue2_itpq_id , t1.citm_print_queue3_itpq_id as citm_print_queue3_itpq_id , t1.citm_print_queue4_itpq_id as citm_print_queue4_itpq_id , t1.citm_print_queue5_itpq_id as citm_print_queue5_itpq_id , t1.citm_print_queue6_itpq_id as citm_print_queue6_itpq_id , t1.citm_print_queue7_itpq_id as citm_print_queue7_itpq_id , t1.citm_print_queue8_itpq_id as citm_print_queue8_itpq_id , t1.citm_print_queue9_itpq_id as citm_print_queue9_itpq_id , t1.citm_print_queue10_itpq_id as citm_print_queue10_itpq_id , t1.citm_no_print as citm_no_print , t1.citm_charge_sc1 as citm_charge_sc1 , t1.citm_charge_sc2 as citm_charge_sc2 , t1.citm_charge_sc3 as citm_charge_sc3 , t1.citm_charge_sc4 as citm_charge_sc4 , t1.citm_charge_sc5 as citm_charge_sc5 , t1.citm_charge_tax1 as citm_charge_tax1 , t1.citm_charge_tax2 as citm_charge_tax2 , t1.citm_charge_tax3 as citm_charge_tax3 , t1.citm_charge_tax4 as citm_charge_tax4 , t1.citm_charge_tax5 as citm_charge_tax5 , t1.citm_charge_tax6 as citm_charge_tax6 , t1.citm_charge_tax7 as citm_charge_tax7 , t1.citm_charge_tax8 as citm_charge_tax8 , t1.citm_charge_tax9 as citm_charge_tax9 , t1.citm_charge_tax10 as citm_charge_tax10 , t1.citm_charge_tax11 as citm_charge_tax11 , t1.citm_charge_tax12 as citm_charge_tax12 , t1.citm_charge_tax13 as citm_charge_tax13 , t1.citm_charge_tax14 as citm_charge_tax14 , t1.citm_charge_tax15 as citm_charge_tax15 , t1.citm_charge_tax16 as citm_charge_tax16 , t1.citm_charge_tax17 as citm_charge_tax17 , t1.citm_charge_tax18 as citm_charge_tax18 , t1.citm_charge_tax19 as citm_charge_tax19 , t1.citm_charge_tax20 as citm_charge_tax20 , t1.citm_charge_tax21 as citm_charge_tax21 , t1.citm_charge_tax22 as citm_charge_tax22 , t1.citm_charge_tax23 as citm_charge_tax23 , t1.citm_charge_tax24 as citm_charge_tax24 , t1.citm_charge_tax25 as citm_charge_tax25 , t1.citm_hide as citm_hide , t1.citm_original_olet_id as citm_original_olet_id , t1.citm_icat_id as citm_icat_id , t1.citm_idep_id as citm_idep_id , t1.citm_icou_id as citm_icou_id , t1.citm_digp_id as citm_digp_id , t1.citm_get_revenue as citm_get_revenue , t1.citm_serving_status as citm_serving_status , t1.citm_pending as citm_pending , t1.citm_ordering_type as citm_ordering_type , t1.citm_round_status as citm_round_status , t1.citm_order_time as citm_order_time , t1.citm_order_loctime as citm_order_loctime , t1.citm_order_user_id as citm_order_user_id , t1.citm_order_stat_id as citm_order_stat_id , t1.citm_rush_time as citm_rush_time , t1.citm_rush_loctime as citm_rush_loctime , t1.citm_rush_user_id as citm_rush_user_id , t1.citm_rush_stat_id as citm_rush_stat_id , cast(t1.citm_rush_count as integer) as citm_rush_count, t1.citm_delivery_time as citm_delivery_time , t1.citm_void_time as citm_void_time , t1.citm_void_loctime as citm_void_loctime , t1.citm_void_user_id as citm_void_user_id , t1.citm_void_stat_id as citm_void_stat_id , t1.citm_void_vdrs_id as citm_void_vdrs_id , t1.citm_void_consumed as citm_void_consumed , COALESCE(t1.citm_status,'') as citm_status , t1.citm_slave_id as citm_slave_id , t1.citm_slave_created as citm_slave_created , t1.citm_slave_modified as citm_slave_modified , t1.citm_sync_id as citm_sync_id , t1.created as created , t1.modified as modified FROM pos_check_items t1")

spark.snappyExecute("create or replace view XY.dv_pos_checks_currency as SELECT concat('{"1":"',t2.ctyp_name_l1 ,'","2":"',t2.ctyp_name_l2,'","3":"',t2.ctyp_name_l3,'","4":"',t2.ctyp_name_l4, '","5":"', t2.ctyp_name_l5, '"}' ) AS ctyp_name, t1.tenant_id tenant_id ,t1.chks_id chks_id ,t1.chks_bday_id chks_bday_id ,t1.bday_date bday_date ,t1.chks_bper_id chks_bper_id ,t1.shop_id shop_id ,t1.chks_olet_id chks_olet_id ,t1.chks_sect_id chks_sect_id ,lower(coalesce( t2.ctyp_code,'') )as ctyp_code ,t2.ctyp_name_l1 ctyp_name_l1 ,CAST(t1.chks_aif AS decimal(15,4)) chks_aif ,CAST(t1.chks_txn AS decimal(15,4)) chks_txn ,t1.chks_check_prefix chks_check_prefix ,CAST(t1.chks_check_num AS decimal(15,4)) chks_check_num ,t1.chks_check_prefix_num chks_check_prefix_num ,t1.chks_ctbl_id chks_ctbl_id ,CAST(t1.chks_guests AS decimal(15,4)) chks_guests ,CAST(t1.chks_children AS decimal(15,4)) chks_children ,CAST(t1.chks_print_count AS decimal(15,4)) chks_print_count ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_print_count AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_print_count ,CAST(t1.chks_receipt_print_count AS decimal(15,4)) chks_receipt_print_count ,CAST(t1.chks_party_count AS decimal(15,4)) chks_party_count ,CAST(t1.chks_check_total AS decimal(15,4)) chks_check_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_check_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_check_total ,CAST(t1.chks_item_total AS decimal(15,4)) chks_item_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_item_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_item_total ,CAST(t1.chks_sc1 AS decimal(15,4)) chks_sc1 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_sc1 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_sc1 ,CAST(t1.chks_sc2 AS decimal(15,4)) chks_sc2 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_sc2 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_sc2 ,CAST(t1.chks_sc3 AS decimal(15,4)) chks_sc3 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_sc3 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_sc3 ,CAST(t1.chks_sc4 AS decimal(15,4)) chks_sc4 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_sc4 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_sc4 ,CAST(t1.chks_sc5 AS decimal(15,4)) chks_sc5 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_sc5 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_sc5 ,CAST(t1.chks_tax1 AS decimal(15,4)) chks_tax1 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax1 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax1 ,CAST(t1.chks_tax2 AS decimal(15,4)) chks_tax2 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax2 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax2 ,CAST(t1.chks_tax3 AS decimal(15,4)) chks_tax3 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax3 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax3 ,CAST(t1.chks_tax4 AS decimal(15,4)) chks_tax4 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax4 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax4 ,CAST(t1.chks_tax5 AS decimal(15,4)) chks_tax5 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax5 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax5 ,CAST(t1.chks_tax6 AS decimal(15,4)) chks_tax6 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax6 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax6 ,CAST(t1.chks_tax7 AS decimal(15,4)) chks_tax7 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax7 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax7 ,CAST(t1.chks_tax8 AS decimal(15,4)) chks_tax8 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax8 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax8 ,CAST(t1.chks_tax9 AS decimal(15,4)) chks_tax9 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax9 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax9 ,CAST(t1.chks_tax10 AS decimal(15,4)) chks_tax10 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax10 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax10 ,CAST(t1.chks_tax11 AS decimal(15,4)) chks_tax11 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax11 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax11 ,CAST(t1.chks_tax12 AS decimal(15,4)) chks_tax12 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax12 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax12 ,CAST(t1.chks_tax13 AS decimal(15,4)) chks_tax13 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax13 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax13 ,CAST(t1.chks_tax14 AS decimal(15,4)) chks_tax14 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax14 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax14 ,CAST(t1.chks_tax15 AS decimal(15,4)) chks_tax15 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax15 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax15 ,CAST(t1.chks_tax16 AS decimal(15,4)) chks_tax16 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax16 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax16 ,CAST(t1.chks_tax17 AS decimal(15,4)) chks_tax17 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax17 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax17 ,CAST(t1.chks_tax18 AS decimal(15,4)) chks_tax18 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax18 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax18 ,CAST(t1.chks_tax19 AS decimal(15,4)) chks_tax19 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax19 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax19 ,CAST(t1.chks_tax20 AS decimal(15,4)) chks_tax20 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax20 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax20 ,CAST(t1.chks_tax21 AS decimal(15,4)) chks_tax21 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax21 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax21 ,CAST(t1.chks_tax22 AS decimal(15,4)) chks_tax22 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax22 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax22 ,CAST(t1.chks_tax23 AS decimal(15,4)) chks_tax23 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax23 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax23 ,CAST(t1.chks_tax24 AS decimal(15,4)) chks_tax24 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax24 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax24 ,CAST(t1.chks_tax25 AS decimal(15,4)) chks_tax25 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tax25 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tax25 ,CAST(t1.chks_incl_tax_ref1 AS decimal(15,4)) chks_incl_tax_ref1 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_incl_tax_ref1 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_incl_tax_ref1 ,CAST(t1.chks_incl_tax_ref2 AS decimal(15,4)) chks_incl_tax_ref2 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_incl_tax_ref2 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_incl_tax_ref2 ,CAST(t1.chks_incl_tax_ref3 AS decimal(15,4)) chks_incl_tax_ref3 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_incl_tax_ref3 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_incl_tax_ref3 ,CAST(t1.chks_incl_tax_ref4 AS decimal(15,4)) chks_incl_tax_ref4 ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_incl_tax_ref4 AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_incl_tax_ref4 ,CAST(t1.chks_pre_disc AS decimal(15,4)) chks_pre_disc ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_pre_disc AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_pre_disc ,CAST(t1.chks_mid_disc AS decimal(15,4)) chks_mid_disc ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_mid_disc AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_mid_disc ,CAST(t1.chks_post_disc AS decimal(15,4)) chks_post_disc ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_post_disc AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_post_disc ,CAST(t1.chks_gratuity_total AS decimal(15,4)) chks_gratuity_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_gratuity_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_gratuity_total ,CAST(t1.chks_round_amount AS decimal(15,4)) chks_round_amount ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_round_amount AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_round_amount ,CAST(t1.chks_payment_total AS decimal(15,4)) chks_payment_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_payment_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_payment_total ,CAST(t1.chks_tips_total AS decimal(15,4)) chks_tips_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_tips_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_tips_total ,CAST(t1.chks_surcharge_total AS decimal(15,4)) chks_surcharge_total ,(CASE WHEN ((currency_rates.exchange_rate IS NULL) OR (currency_rates.exchange_rate = 0)) THEN 0 ELSE (CAST(t1.chks_surcharge_total AS decimal(15,4)) * currency_rates.exchange_rate) END) cury_chks_surcharge_total ,t1.chks_paid chks_paid ,t1.chks_resv_book_date chks_resv_book_date ,t1.chks_resv_refno_with_prefix chks_resv_refno_with_prefix ,coalesce(t1.chks_ordering_type ,'') as chks_ordering_type ,case when coalesce (t1.chks_ordering_type, '') = 't' then 'takeout' when coalesce (t1.chks_ordering_type, '') = 'd' then 'delivery' ELSE 'normal check' end as chks_ordering_type_deal ,coalesce(t1.chks_ordering_mode ,'') as chks_ordering_mode ,t1.chks_non_revenue chks_non_revenue ,(CASE WHEN (t1.chks_non_revenue = 'a') THEN 'Advance order' WHEN (t1.chks_non_revenue = 'y') THEN 'Non-Revenue' WHEN (t1.chks_non_revenue = 'l') THEN 'Liability' ELSE 'Revenue' END) chks_non_revenue_deal ,t1.chks_memb_id chks_memb_id ,t1.chks_ctyp_id chks_ctyp_id ,CAST(t1.chks_settle_shift_num AS decimal(15,4)) chks_settle_shift_num ,t1.chks_remark chks_remark ,t1.chks_open_time chks_open_time ,t1.chks_open_loctime chks_open_loctime ,t1.chks_open_user_id chks_open_user_id ,t1.chks_open_stat_id chks_open_stat_id ,t1.chks_close_time chks_close_time ,t1.chks_close_loctime chks_close_loctime ,t1.chks_close_user_id chks_close_user_id ,t1.chks_close_stat_id chks_close_stat_id ,t1.chks_close_bper_id chks_close_bper_id ,t1.chks_modified_time chks_modified_time ,t1.chks_modified_loctime chks_modified_loctime ,t1.chks_modified_user_id chks_modified_user_id ,t1.chks_modified_stat_id chks_modified_stat_id ,t1.chks_print_time chks_print_time ,t1.chks_print_loctime chks_print_loctime ,t1.chks_print_user_id chks_print_user_id ,t1.chks_print_stat_id chks_print_stat_id ,t1.chks_lock_time chks_lock_time ,t1.chks_lock_loctime chks_lock_loctime ,t1.chks_lock_user_id chks_lock_user_id ,t1.chks_lock_stat_id chks_lock_stat_id ,t1.chks_void_time chks_void_time ,t1.chks_void_loctime chks_void_loctime ,t1.chks_void_user_id chks_void_user_id ,t1.chks_void_stat_id chks_void_stat_id ,t1.chks_void_vdrs_id chks_void_vdrs_id ,t1.chks_owner_user_id chks_owner_user_id ,COALESCE(t1.chks_status,'') chks_status ,t1.chks_slave_id chks_slave_id ,t1.chks_slave_created chks_slave_created ,t1.chks_slave_modified chks_slave_modified ,t1.chks_sync_id chks_sync_id ,t1.created created ,substr(CAST(from_unixtime((ceil((to_unix_timestamp(CAST(COALESCE(t1.chks_open_loctime,'1900-01-01 00:00:00') AS timestamp)) / 900-1)) * 900)) AS STRING),12,5) quarter_hour ,t1.modified modified FROM dv_pos_checks t1 LEFT JOIN dv_out_shops out_shops ON t1.shop_id = out_shops.shop_id AND t1.tenant_id = out_shops.tenant_id LEFT JOIN dv_currency_rates currency_rates ON currency_rates.tenant_id = out_shops.tenant_id AND currency_rates.shop_curr_code = out_shops.currency_id AND currency_rates.del_flag = '0' LEFT JOIN dv_pos_custom_types t2 ON t1.tenant_id = t2.tenant_id AND t1.chks_ctyp_id = t2.ctyp_id AND t2.ctyp_status <> 'd' WHERE t1.chks_slave_created >= currency_rates.start_date AND t1.chks_slave_created < COALESCE(currency_rates.end_date,'2099-12-12') and out_shops.shop_status <> 'd' and out_shops.status = '1'")

spark.snappyExecute("create or replace view XY.dv_pos_check_items_currency as SELECT concat(coalesce(citm_name_l1,''),'$mls$',coalesce(citm_name_l2,''),'$mls$',coalesce(citm_name_l3,''),'$mls$',coalesce( citm_name_l4,''), '$mls$',coalesce( citm_name_l5,'') ) AS citm_name, t1.tenant_id as tenant_id , t1.citm_id as citm_id , t1.citm_bday_id as citm_bday_id , t1.bday_date as bday_date, t1.citm_bper_id as citm_bper_id , t1.shop_id as shop_id , t1.citm_olet_id as citm_olet_id , t1.citm_chks_id as citm_chks_id , t1.citm_cpty_id as citm_cpty_id , t1.citm_item_id as citm_item_id , t1.citm_code as citm_code , t1.citm_name_l1 as citm_name_l1 , t1.citm_name_l2 as citm_name_l2 , t1.citm_name_l3 as citm_name_l3 , t1.citm_name_l4 as citm_name_l4 , t1.citm_name_l5 as citm_name_l5 , t1.citm_short_name_l1 as citm_short_name_l1 , t1.citm_short_name_l2 as citm_short_name_l2 , t1.citm_short_name_l3 as citm_short_name_l3 , t1.citm_short_name_l4 as citm_short_name_l4 , t1.citm_short_name_l5 as citm_short_name_l5 , t1.citm_parent_citm_id as citm_parent_citm_id , t1.citm_role as citm_role , cast(t1.citm_child_count as decimal(15,4)) as citm_child_count , cast(t1.citm_modifier_count as decimal(15,4)) as citm_modifier_count , cast(t1.citm_seat as decimal(15,4)) as citm_seat , t1.citm_mix_and_match_citm_id as citm_mix_and_match_citm_id , cast(t1.citm_seq as decimal(15,4)) as citm_seq , cast(t1.citm_round_total as decimal(15,4)) as citm_round_total , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_round_total as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_round_total, cast(t1.citm_total as decimal(15,4)) as citm_total , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_total as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_total, cast(t1.citm_round_amount as decimal(15,4)) as citm_round_amount , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_round_amount as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_round_amount, cast(t1.citm_carry_total as decimal(15,4)) as citm_carry_total , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_carry_total as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_carry_total, cast(t1.citm_qty as decimal(15,4)) as citm_qty , cast(t1.citm_base_qty as decimal(15,4)) as citm_base_qty , cast(t1.citm_price as decimal(15,4)) as citm_price , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_price as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_price, cast(t1.citm_original_price as decimal(15,4)) as citm_original_price , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_original_price as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_original_price, cast(t1.citm_basic_price as decimal(15,4)) as citm_basic_price , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_basic_price as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_basic_price, t1.citm_basic_calculate_method as citm_basic_calculate_method , cast(t1.citm_sc1 as decimal(15,4)) as citm_sc1 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_sc1 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_sc1, cast(t1.citm_sc2 as decimal(15,4)) as citm_sc2 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_sc2 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_sc2, cast(t1.citm_sc3 as decimal(15,4)) as citm_sc3 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_sc3 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_sc3, cast(t1.citm_sc4 as decimal(15,4)) as citm_sc4 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_sc4 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_sc4, cast(t1.citm_sc5 as decimal(15,4)) as citm_sc5 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_sc5 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_sc5, cast(t1.citm_tax1 as decimal(15,4)) as citm_tax1 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax1 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax1, cast(t1.citm_tax2 as decimal(15,4)) as citm_tax2 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax2 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax2, cast(t1.citm_tax3 as decimal(15,4)) as citm_tax3 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax3 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax3, cast(t1.citm_tax4 as decimal(15,4)) as citm_tax4 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax4 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax4, cast(t1.citm_tax5 as decimal(15,4)) as citm_tax5 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax5 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax5, cast(t1.citm_tax6 as decimal(15,4)) as citm_tax6 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax6 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax6, cast(t1.citm_tax7 as decimal(15,4)) as citm_tax7 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax7 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax7, cast(t1.citm_tax8 as decimal(15,4)) as citm_tax8 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax8 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax8, cast(t1.citm_tax9 as decimal(15,4)) as citm_tax9 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax9 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax9, cast(t1.citm_tax10 as decimal(15,4)) as citm_tax10 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax10 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax10, cast(t1.citm_tax11 as decimal(15,4)) as citm_tax11 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax11 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax11, cast(t1.citm_tax12 as decimal(15,4)) as citm_tax12 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax12 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax12, cast(t1.citm_tax13 as decimal(15,4)) as citm_tax13 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax13 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax13, cast(t1.citm_tax14 as decimal(15,4)) as citm_tax14 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax14 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax14, cast(t1.citm_tax15 as decimal(15,4)) as citm_tax15 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax15 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax15, cast(t1.citm_tax16 as decimal(15,4)) as citm_tax16 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax16 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax16, cast(t1.citm_tax17 as decimal(15,4)) as citm_tax17 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax17 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax17, cast(t1.citm_tax18 as decimal(15,4)) as citm_tax18 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax18 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax18, cast(t1.citm_tax19 as decimal(15,4)) as citm_tax19 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax19 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax19, cast(t1.citm_tax20 as decimal(15,4)) as citm_tax20 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax20 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax20, cast(t1.citm_tax21 as decimal(15,4)) as citm_tax21 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax21 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax21, cast(t1.citm_tax22 as decimal(15,4)) as citm_tax22 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax22 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax22, cast(t1.citm_tax23 as decimal(15,4)) as citm_tax23 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax23 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax23, cast(t1.citm_tax24 as decimal(15,4)) as citm_tax24 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax24 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax24, cast(t1.citm_tax25 as decimal(15,4)) as citm_tax25 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_tax25 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_tax25, cast(t1.citm_incl_tax_ref1 as decimal(15,4)) as citm_incl_tax_ref1 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_incl_tax_ref1 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_incl_tax_ref1, cast(t1.citm_incl_tax_ref2 as decimal(15,4)) as citm_incl_tax_ref2 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_incl_tax_ref2 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_incl_tax_ref2, cast(t1.citm_incl_tax_ref3 as decimal(15,4)) as citm_incl_tax_ref3 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_incl_tax_ref3 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_incl_tax_ref3, cast(t1.citm_incl_tax_ref4 as decimal(15,4)) as citm_incl_tax_ref4 , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_incl_tax_ref4 as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_incl_tax_ref4, cast(t1.citm_pre_disc as decimal(15,4)) as citm_pre_disc , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_pre_disc as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_pre_disc, cast(t1.citm_mid_disc as decimal(15,4)) as citm_mid_disc , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_mid_disc as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_mid_disc, cast(t1.citm_post_disc as decimal(15,4)) as citm_post_disc , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_post_disc as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_post_disc, cast(t1.citm_original_price_level as decimal(15,4)) as citm_original_price_level , cast(t1.citm_price_level as decimal(15,4)) as citm_price_level , cast(t1.citm_revenue as decimal(15,4)) as citm_revenue , cast(t1.citm_carry_revenue as decimal(15,4)) as citm_carry_revenue , cast(t1.citm_unit_cost as decimal(15,4)) as citm_unit_cost , case when currency_rates.exchange_rate is null or currency_rates.exchange_rate = 0 then 0 else cast(t1.citm_unit_cost as decimal(15,4) ) * currency_rates.exchange_rate end as cury_citm_unit_cost, t1.citm_print_queue1_itpq_id as citm_print_queue1_itpq_id , t1.citm_print_queue2_itpq_id as citm_print_queue2_itpq_id , t1.citm_print_queue3_itpq_id as citm_print_queue3_itpq_id , t1.citm_print_queue4_itpq_id as citm_print_queue4_itpq_id , t1.citm_print_queue5_itpq_id as citm_print_queue5_itpq_id , t1.citm_print_queue6_itpq_id as citm_print_queue6_itpq_id , t1.citm_print_queue7_itpq_id as citm_print_queue7_itpq_id , t1.citm_print_queue8_itpq_id as citm_print_queue8_itpq_id , t1.citm_print_queue9_itpq_id as citm_print_queue9_itpq_id , t1.citm_print_queue10_itpq_id as citm_print_queue10_itpq_id , t1.citm_no_print as citm_no_print , t1.citm_charge_sc1 as citm_charge_sc1 , t1.citm_charge_sc2 as citm_charge_sc2 , t1.citm_charge_sc3 as citm_charge_sc3 , t1.citm_charge_sc4 as citm_charge_sc4 , t1.citm_charge_sc5 as citm_charge_sc5 , t1.citm_charge_tax1 as citm_charge_tax1 , t1.citm_charge_tax2 as citm_charge_tax2 , t1.citm_charge_tax3 as citm_charge_tax3 , t1.citm_charge_tax4 as citm_charge_tax4 , t1.citm_charge_tax5 as citm_charge_tax5 , t1.citm_charge_tax6 as citm_charge_tax6 , t1.citm_charge_tax7 as citm_charge_tax7 , t1.citm_charge_tax8 as citm_charge_tax8 , t1.citm_charge_tax9 as citm_charge_tax9 , t1.citm_charge_tax10 as citm_charge_tax10 , t1.citm_charge_tax11 as citm_charge_tax11 , t1.citm_charge_tax12 as citm_charge_tax12 , t1.citm_charge_tax13 as citm_charge_tax13 , t1.citm_charge_tax14 as citm_charge_tax14 , t1.citm_charge_tax15 as citm_charge_tax15 , t1.citm_charge_tax16 as citm_charge_tax16 , t1.citm_charge_tax17 as citm_charge_tax17 , t1.citm_charge_tax18 as citm_charge_tax18 , t1.citm_charge_tax19 as citm_charge_tax19 , t1.citm_charge_tax20 as citm_charge_tax20 , t1.citm_charge_tax21 as citm_charge_tax21 , t1.citm_charge_tax22 as citm_charge_tax22 , t1.citm_charge_tax23 as citm_charge_tax23 , t1.citm_charge_tax24 as citm_charge_tax24 , t1.citm_charge_tax25 as citm_charge_tax25 , t1.citm_hide as citm_hide , t1.citm_original_olet_id as citm_original_olet_id , t1.citm_icat_id as citm_icat_id , t1.citm_idep_id as citm_idep_id , t1.citm_icou_id as citm_icou_id , t1.citm_digp_id as citm_digp_id , t1.citm_get_revenue as citm_get_revenue , t1.citm_serving_status as citm_serving_status , t1.citm_pending as citm_pending , t1.citm_ordering_type as citm_ordering_type , t1.citm_round_status as citm_round_status , t1.citm_order_time as citm_order_time , t1.citm_order_loctime as citm_order_loctime , t1.citm_order_user_id as citm_order_user_id , t1.citm_order_stat_id as citm_order_stat_id , t1.citm_rush_time as citm_rush_time , t1.citm_rush_loctime as citm_rush_loctime , t1.citm_rush_user_id as citm_rush_user_id , t1.citm_rush_stat_id as citm_rush_stat_id , cast(t1.citm_rush_count as decimal(15,4)) as citm_rush_count , t1.citm_delivery_time as citm_delivery_time , t1.citm_void_time as citm_void_time , t1.citm_void_loctime as citm_void_loctime , t1.citm_void_user_id as citm_void_user_id , t1.citm_void_stat_id as citm_void_stat_id , t1.citm_void_vdrs_id as citm_void_vdrs_id , t1.citm_void_consumed as citm_void_consumed , COALESCE(t1.citm_status,'') as citm_status , t1.citm_slave_id as citm_slave_id , t1.citm_slave_created as citm_slave_created , t1.citm_slave_modified as citm_slave_modified , t1.citm_sync_id as citm_sync_id , t1.created as created , t1.modified as modified FROM dv_pos_check_items t1 left join dv_out_shops out_shops on t1.shop_id = out_shops.shop_id and t1.tenant_id = out_shops.tenant_id left join dv_currency_rates currency_rates on currency_rates.tenant_id = out_shops.tenant_id and currency_rates.shop_curr_code = out_shops.currency_id and currency_rates.del_flag = '0' where t1.citm_slave_created >= currency_rates.start_date and t1.citm_slave_created < coalesce(currency_rates.end_date,'2099-12-12') and out_shops.shop_status <> 'd' and out_shops.status = '1'")

spark.snappyExecute("create or replace view XY.dv_cube_test as select t1.tenant_id as tenant_id, t1.shop_id as shop_id, t1.chks_olet_id as olet_id, t1.bday_date as bday_date, t1.chks_non_revenue_deal as chks_non_revenue, t1.chks_bper_id as bper_id, t3.citm_item_id as citm_item_id, sum(t3.cury_citm_tax1) as cury_citm_tax1_sum, sum(t3.cury_citm_tax2) as cury_citm_tax2_sum, sum(t3.cury_citm_tax3) as cury_citm_tax3_sum, sum(t3.cury_citm_tax4) as cury_citm_tax4_sum, sum(t3.cury_citm_tax1+t3.cury_citm_incl_tax_ref1) as cury_citm_tax_ref1_sum, sum(t3.cury_citm_tax2+t3.cury_citm_incl_tax_ref2) as cury_citm_tax_ref2_sum, sum(t3.cury_citm_tax3+t3.cury_citm_incl_tax_ref3) as cury_citm_tax_ref3_sum, sum(t3.cury_citm_tax4+t3.cury_citm_incl_tax_ref4) as cury_citm_tax_ref4_sum, sum(t3.cury_citm_tax5) as cury_citm_tax5_sum, sum(t3.cury_citm_tax6) as cury_citm_tax6_sum, sum(t3.cury_citm_tax7) as cury_citm_tax7_sum, sum(t3.cury_citm_tax8) as cury_citm_tax8_sum, sum(t3.cury_citm_tax9) as cury_citm_tax9_sum, sum(t3.cury_citm_tax10) as cury_citm_tax10_sum, sum(t3.cury_citm_tax11) as cury_citm_tax11_sum, sum(t3.cury_citm_tax12) as cury_citm_tax12_sum, sum(t3.cury_citm_tax13) as cury_citm_tax13_sum, sum(t3.cury_citm_tax14) as cury_citm_tax14_sum, sum(t3.cury_citm_tax15) as cury_citm_tax15_sum, sum(t3.cury_citm_tax16) as cury_citm_tax16_sum, sum(t3.cury_citm_tax17) as cury_citm_tax17_sum, sum(t3.cury_citm_tax18) as cury_citm_tax18_sum, sum(t3.cury_citm_tax19) as cury_citm_tax19_sum, sum(t3.cury_citm_tax20) as cury_citm_tax20_sum, sum(t3.cury_citm_tax21) as cury_citm_tax21_sum, sum(t3.cury_citm_tax22) as cury_citm_tax22_sum, sum(t3.cury_citm_tax23) as cury_citm_tax23_sum, sum(t3.cury_citm_tax24) as cury_citm_tax24_sum, sum(t3.cury_citm_tax25) as cury_citm_tax25_sum, sum(case when t3.cury_citm_tax1 <> 0 or t3.cury_citm_incl_tax_ref1 <> 0 then 1 else 0 end ) as cury_citm_tax1_count, sum(case when t3.cury_citm_tax2 <> 0 or t3.cury_citm_incl_tax_ref2 <> 0 then 1 else 0 end ) as cury_citm_tax2_count, sum(case when t3.cury_citm_tax3 <> 0 or t3.cury_citm_incl_tax_ref3 <> 0 then 1 else 0 end ) as cury_citm_tax3_count, sum(case when t3.cury_citm_tax4 <> 0 or t3.cury_citm_incl_tax_ref4 <> 0 then 1 else 0 end ) as cury_citm_tax4_count, sum(case when t3.cury_citm_tax5 <> 0 then 1 else 0 end ) as cury_citm_tax5_count, sum(case when t3.cury_citm_tax6 <> 0 then 1 else 0 end ) as cury_citm_tax6_count, sum(case when t3.cury_citm_tax7 <> 0 then 1 else 0 end ) as cury_citm_tax7_count, sum(case when t3.cury_citm_tax8 <> 0 then 1 else 0 end ) as cury_citm_tax8_count, sum(case when t3.cury_citm_tax9 <> 0 then 1 else 0 end ) as cury_citm_tax9_count, sum(case when t3.cury_citm_tax10 <> 0 then 1 else 0 end ) as cury_citm_tax10_count, sum(case when t3.cury_citm_tax11 <> 0 then 1 else 0 end ) as cury_citm_tax11_count, sum(case when t3.cury_citm_tax12 <> 0 then 1 else 0 end ) as cury_citm_tax12_count, sum(case when t3.cury_citm_tax13 <> 0 then 1 else 0 end ) as cury_citm_tax13_count, sum(case when t3.cury_citm_tax14 <> 0 then 1 else 0 end ) as cury_citm_tax14_count, sum(case when t3.cury_citm_tax15 <> 0 then 1 else 0 end ) as cury_citm_tax15_count, sum(case when t3.cury_citm_tax16 <> 0 then 1 else 0 end ) as cury_citm_tax16_count, sum(case when t3.cury_citm_tax17 <> 0 then 1 else 0 end ) as cury_citm_tax17_count, sum(case when t3.cury_citm_tax18 <> 0 then 1 else 0 end ) as cury_citm_tax18_count, sum(case when t3.cury_citm_tax19 <> 0 then 1 else 0 end ) as cury_citm_tax19_count, sum(case when t3.cury_citm_tax20 <> 0 then 1 else 0 end ) as cury_citm_tax20_count, sum(case when t3.cury_citm_tax21 <> 0 then 1 else 0 end ) as cury_citm_tax21_count, sum(case when t3.cury_citm_tax22 <> 0 then 1 else 0 end ) as cury_citm_tax22_count, sum(case when t3.cury_citm_tax23 <> 0 then 1 else 0 end ) as cury_citm_tax23_count, sum(case when t3.cury_citm_tax24 <> 0 then 1 else 0 end ) as cury_citm_tax24_count, sum(case when t3.cury_citm_tax25 <> 0 then 1 else 0 end ) as cury_citm_tax25_count, sum(case when t3.citm_tax1 <> 0 or t3.citm_incl_tax_ref1 <> 0 then t3.citm_round_total else 0 end ) as citm_total_1_sum, sum(case when t3.citm_tax2 <> 0 or t3.citm_incl_tax_ref2 <> 0 then t3.citm_round_total else 0 end ) as citm_total_2_sum, sum(case when t3.citm_tax3 <> 0 or t3.citm_incl_tax_ref3 <> 0 then t3.citm_round_total else 0 end ) as citm_total_3_sum, sum(case when t3.citm_tax4 <> 0 or t3.citm_incl_tax_ref4 <> 0 then t3.citm_round_total else 0 end ) as citm_total_4_sum, sum(case when t3.citm_tax5 <> 0 then t3.citm_round_total else 0 end ) as citm_total_5_sum, sum(case when t3.citm_tax6 <> 0 then t3.citm_round_total else 0 end ) as citm_total_6_sum, sum(case when t3.citm_tax7 <> 0 then t3.citm_round_total else 0 end ) as citm_total_7_sum, sum(case when t3.citm_tax8 <> 0 then t3.citm_round_total else 0 end ) as citm_total_8_sum, sum(case when t3.citm_tax9 <> 0 then t3.citm_round_total else 0 end ) as citm_total_9_sum, sum(case when t3.citm_tax10 <> 0 then t3.citm_round_total else 0 end ) as citm_total_10_sum, sum(case when t3.citm_tax11 <> 0 then t3.citm_round_total else 0 end ) as citm_total_11_sum, sum(case when t3.citm_tax12 <> 0 then t3.citm_round_total else 0 end ) as citm_total_12_sum, sum(case when t3.citm_tax13 <> 0 then t3.citm_round_total else 0 end ) as citm_total_13_sum, sum(case when t3.citm_tax14 <> 0 then t3.citm_round_total else 0 end ) as citm_total_14_sum, sum(case when t3.citm_tax15 <> 0 then t3.citm_round_total else 0 end ) as citm_total_15_sum, sum(case when t3.citm_tax16 <> 0 then t3.citm_round_total else 0 end ) as citm_total_16_sum, sum(case when t3.citm_tax17 <> 0 then t3.citm_round_total else 0 end ) as citm_total_17_sum, sum(case when t3.citm_tax18 <> 0 then t3.citm_round_total else 0 end ) as citm_total_18_sum, sum(case when t3.citm_tax19 <> 0 then t3.citm_round_total else 0 end ) as citm_total_19_sum, sum(case when t3.citm_tax20 <> 0 then t3.citm_round_total else 0 end ) as citm_total_20_sum, sum(case when t3.citm_tax21 <> 0 then t3.citm_round_total else 0 end ) as citm_total_21_sum, sum(case when t3.citm_tax22 <> 0 then t3.citm_round_total else 0 end ) as citm_total_22_sum, sum(case when t3.citm_tax23 <> 0 then t3.citm_round_total else 0 end ) as citm_total_23_sum, sum(case when t3.citm_tax24 <> 0 then t3.citm_round_total else 0 end ) as citm_total_24_sum, sum(case when t3.citm_tax25 <> 0 then t3.citm_round_total else 0 end ) as citm_total_25_sum, sum(case when t3.cury_citm_tax1 <> 0 or t3.cury_citm_incl_tax_ref1 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_1_sum, sum(case when t3.cury_citm_tax2 <> 0 or t3.cury_citm_incl_tax_ref2 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_2_sum, sum(case when t3.cury_citm_tax3 <> 0 or t3.cury_citm_incl_tax_ref3 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_3_sum, sum(case when t3.cury_citm_tax4 <> 0 or t3.cury_citm_incl_tax_ref4 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_4_sum, sum(case when t3.cury_citm_tax5 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_5_sum, sum(case when t3.cury_citm_tax6 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_6_sum, sum(case when t3.cury_citm_tax7 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_7_sum, sum(case when t3.cury_citm_tax8 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_8_sum, sum(case when t3.cury_citm_tax9 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_9_sum, sum(case when t3.cury_citm_tax10 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_10_sum, sum(case when t3.cury_citm_tax11 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_11_sum, sum(case when t3.cury_citm_tax12 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_12_sum, sum(case when t3.cury_citm_tax13 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_13_sum, sum(case when t3.cury_citm_tax14 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_14_sum, sum(case when t3.cury_citm_tax15 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_15_sum, sum(case when t3.cury_citm_tax16 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_16_sum, sum(case when t3.cury_citm_tax17 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_17_sum, sum(case when t3.cury_citm_tax18 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_18_sum, sum(case when t3.cury_citm_tax19 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_19_sum, sum(case when t3.cury_citm_tax20 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_20_sum, sum(case when t3.cury_citm_tax21 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_21_sum, sum(case when t3.cury_citm_tax22 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_22_sum, sum(case when t3.cury_citm_tax23 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_23_sum, sum(case when t3.cury_citm_tax24 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_24_sum, sum(case when t3.cury_citm_tax25 <> 0 then t3.cury_citm_round_total else 0 end ) as cury_citm_total_25_sum, sum(t3.citm_tax1) as citm_tax1_sum, sum(t3.citm_tax2) as citm_tax2_sum, sum(t3.citm_tax3) as citm_tax3_sum, sum(t3.citm_tax4) as citm_tax4_sum, sum(t3.citm_tax1+t3.citm_incl_tax_ref1) as citm_tax_ref1_sum, sum(t3.citm_tax2+t3.citm_incl_tax_ref2) as citm_tax_ref2_sum, sum(t3.citm_tax3+t3.citm_incl_tax_ref3) as citm_tax_ref3_sum, sum(t3.citm_tax4+t3.citm_incl_tax_ref4) as citm_tax_ref4_sum, sum(t3.citm_tax5) as citm_tax5_sum, sum(t3.citm_tax6) as citm_tax6_sum, sum(t3.citm_tax7) as citm_tax7_sum, sum(t3.citm_tax8) as citm_tax8_sum, sum(t3.citm_tax9) as citm_tax9_sum, sum(t3.citm_tax10) as citm_tax10_sum, sum(t3.citm_tax11) as citm_tax11_sum, sum(t3.citm_tax12) as citm_tax12_sum, sum(t3.citm_tax13) as citm_tax13_sum, sum(t3.citm_tax14) as citm_tax14_sum, sum(t3.citm_tax15) as citm_tax15_sum, sum(t3.citm_tax16) as citm_tax16_sum, sum(t3.citm_tax17) as citm_tax17_sum, sum(t3.citm_tax18) as citm_tax18_sum, sum(t3.citm_tax19) as citm_tax19_sum, sum(t3.citm_tax20) as citm_tax20_sum, sum(t3.citm_tax21) as citm_tax21_sum, sum(t3.citm_tax22) as citm_tax22_sum, sum(t3.citm_tax23) as citm_tax23_sum, sum(t3.citm_tax24) as citm_tax24_sum, sum(t3.citm_tax25) as citm_tax25_sum, sum(t3.cury_citm_sc1) as cury_citm_sc1_sum, sum(t3.cury_citm_sc2) as cury_citm_sc2_sum, sum(t3.cury_citm_sc3) as cury_citm_sc3_sum, sum(t3.cury_citm_sc4) as cury_citm_sc4_sum, sum(t3.cury_citm_sc5) as cury_citm_sc5_sum, sum(t3.citm_sc1) as citm_sc1_sum, sum(t3.citm_sc2) as citm_sc2_sum, sum(t3.citm_sc3) as citm_sc3_sum, sum(t3.citm_sc4) as citm_sc4_sum, sum(t3.citm_sc5) as citm_sc5_sum, sum(case when t3.citm_sc1 <> 0 then 1 else 0 end) as citm_sc1_count, sum(case when t3.citm_sc2 <> 0 then 1 else 0 end) as citm_sc2_count, sum(case when t3.citm_sc3 <> 0 then 1 else 0 end) as citm_sc3_count, sum(case when t3.citm_sc4 <> 0 then 1 else 0 end) as citm_sc4_count, sum(case when t3.citm_sc5 <> 0 then 1 else 0 end) as citm_sc5_count, sum(t3.citm_pre_disc + t3.citm_mid_disc + t3.citm_post_disc) as citm_pre_mid_post_disc, sum(t3.cury_citm_pre_disc + t3.cury_citm_mid_disc + t3.cury_citm_post_disc) as cury_citm_pre_mid_post_disc, sum(t3.cury_citm_incl_tax_ref1 + t3.cury_citm_incl_tax_ref2 + t3.cury_citm_incl_tax_ref3 + t3.cury_citm_incl_tax_ref4) as cury_citm_incl_tax_ref1_4, sum(t3.citm_incl_tax_ref1 + t3.citm_incl_tax_ref2 + t3.citm_incl_tax_ref3 + t3.citm_incl_tax_ref4) as citm_incl_tax_ref1_4, sum(case when t1.chks_surcharge_total <> 0 then 1 else 0 end) as citm_surcharge_total_count, sum(case when t1.chks_tips_total <> 0 then 1 else 0 end) as citm_tips_total_count, sum(t3.cury_citm_round_total) as cury_citm_round_total, sum(t3.citm_round_total) as citm_round_total, max(t3.citm_charge_tax1) as citm_charge_tax1, max(t3.citm_charge_tax2) as citm_charge_tax2, max(t3.citm_charge_tax3) as citm_charge_tax3, max(t3.citm_charge_tax4) as citm_charge_tax4, max(t3.citm_charge_tax5) as citm_charge_tax5, max(t3.citm_charge_tax6) as citm_charge_tax6, max(t3.citm_charge_tax7) as citm_charge_tax7, max(t3.citm_charge_tax8) as citm_charge_tax8, max(t3.citm_charge_tax9) as citm_charge_tax9, max(t3.citm_charge_tax10) as citm_charge_tax10, max(t3.citm_charge_tax11) as citm_charge_tax11, max(t3.citm_charge_tax12) as citm_charge_tax12, max(t3.citm_charge_tax13) as citm_charge_tax13, max(t3.citm_charge_tax14) as citm_charge_tax14, max(t3.citm_charge_tax15) as citm_charge_tax15, max(t3.citm_charge_tax16) as citm_charge_tax16, max(t3.citm_charge_tax17) as citm_charge_tax17, max(t3.citm_charge_tax18) as citm_charge_tax18, max(t3.citm_charge_tax19) as citm_charge_tax19, max(t3.citm_charge_tax20) as citm_charge_tax20, max(t3.citm_charge_tax21) as citm_charge_tax21, max(t3.citm_charge_tax22) as citm_charge_tax22, max(t3.citm_charge_tax23) as citm_charge_tax23, max(t3.citm_charge_tax24) as citm_charge_tax24, max(t3.citm_charge_tax25) as citm_charge_tax25, sum(t1.chks_tax1 + t1.chks_tax2 + t1.chks_tax3 + t1.chks_tax4 + t1.chks_tax5 + t1.chks_tax6 + t1.chks_tax7 + t1.chks_tax8 + t1.chks_tax9 + t1.chks_tax10 + t1.chks_tax11 + t1.chks_tax12 + t1.chks_tax13 + t1.chks_tax14 + t1.chks_tax15 + t1.chks_tax16 + t1.chks_tax17 + t1.chks_tax18 + t1.chks_tax19 + t1.chks_tax20 + t1.chks_tax21 + t1.chks_tax22 + t1.chks_tax23 + t1.chks_tax24 + t1.chks_tax25 ) as chks_tax1_25_all, sum(t1.cury_chks_tax1 + t1.cury_chks_tax2 + t1.cury_chks_tax3 + t1.cury_chks_tax4 + t1.cury_chks_tax5 + t1.cury_chks_tax6 + t1.cury_chks_tax7 + t1.cury_chks_tax8 + t1.cury_chks_tax9 + t1.cury_chks_tax10 + t1.cury_chks_tax11 + t1.cury_chks_tax12 + t1.cury_chks_tax13 + t1.cury_chks_tax14 + t1.cury_chks_tax15 + t1.cury_chks_tax16 + t1.cury_chks_tax17 + t1.cury_chks_tax18 + t1.cury_chks_tax19 + t1.cury_chks_tax20 + t1.cury_chks_tax21 + t1.cury_chks_tax22 + t1.cury_chks_tax23 + t1.cury_chks_tax24 + t1.cury_chks_tax25 ) as cury_chks_tax1_25_all, sum(t1.cury_chks_pre_disc + t1.cury_chks_mid_disc + t1.cury_chks_post_disc ) as cury_chks_pre_mid_post_disc_all, sum(t1.chks_pre_disc + t1.chks_mid_disc + t1.chks_post_disc ) as chks_pre_mid_post_disc_all, sum(t1.cury_chks_incl_tax_ref1 + t1.cury_chks_incl_tax_ref2 + t1.cury_chks_incl_tax_ref3 + t1.cury_chks_incl_tax_ref4) as cury_chks_incl_tax_ref1_4, sum(t1.chks_incl_tax_ref1 + t1.chks_incl_tax_ref2 + t1.chks_incl_tax_ref3 + t1.chks_incl_tax_ref4) as chks_incl_tax_ref1_4, sum(coalesce(t7.ctsr_value,0)) as ctsr_value, sum(coalesce(t7.cury_ctsr_value,0)) as cury_ctsr_value from dv_pos_check_items_currency t3 left join dv_pos_checks_currency t1 ON t1.tenant_id = t3.tenant_id AND t1.chks_id = t3.citm_chks_id left join ( select tenant_id as tenant_id, ctsr_chks_id as ctsr_chks_id, sum(ctsr_value) as ctsr_value, sum(cury_ctsr_value) as cury_ctsr_value from dv_pos_check_tax_sc_refs_currency where ctsr_variable LIKE 'incl_sc_ref%' AND ctsr_by = 'check' AND ctsr_status <> 'd' group by ctsr_chks_id, tenant_id ) t7 ON t7.ctsr_chks_id = t1.chks_id AND t7.tenant_id=t1.tenant_id where t1.chks_status <> 'd' and t3.citm_status<>'d' and (t3.citm_tax1 + t3.citm_tax2 + t3.citm_tax3 + t3.citm_tax4 + t3.citm_tax5 + t3.citm_tax6 + t3.citm_tax1 + t3.citm_tax8 + t3.citm_tax9 + t3.citm_tax10 + t3.citm_tax11 + t3.citm_tax12 + t3.citm_tax13 + t3.citm_tax14 + t3.citm_tax15 + t3.citm_tax16 + t3.citm_tax17 + t3.citm_tax18 + t3.citm_tax19 + t3.citm_tax20 + t3.citm_tax21 + t3.citm_tax22 + t3.citm_tax23 + t3.citm_tax24 + t3.citm_tax25 + t3.citm_sc1 + t3.citm_sc2 + t3.citm_sc3 + t3.citm_sc4 + t3.citm_sc5 + t3.cury_citm_incl_tax_ref1 + t3.citm_incl_tax_ref2 + t3.citm_incl_tax_ref3 + t3.citm_incl_tax_ref4) > 0 group by t3.citm_item_id, t1.chks_bper_id, t1.chks_olet_id, t1.bday_date, t1.shop_id, t1.tenant_id, t1.chks_non_revenue_deal")

spark.snappyExecute("select count(*) from XY.dv_cube_test").explain `

At the same time, I found that the condition statement causing the fault in this SQL is: 't3.citm_tax1 + t3.citm_tax2 + t3.citm_tax3 + t3.citm_tax4 + t3.citm_tax5 + t3.citm_tax6 + t3.citm_tax1 + t3.citm_tax8 + t3.citm_tax9 + t3.citm_tax10 + t3.citm_tax11 + t3.citm_tax12 + t3.citm_tax13 + t3.citm_tax14 + t3.citm_tax15 + t3.citm_tax16 + t3.citm_tax17 + t3.citm_tax18 + t3.citm_tax19 + t3.citm_tax20 + t3.citm_tax21 + t3.citm_tax22 + t3.citm_tax23 + t3.citm_tax24 + t3.citm_tax25 + t3.citm_sc1 + t3.citm_sc2 + t3.citm_sc3 + t3.citm_sc4 + t3.citm_sc5 + t3.cury_citm_incl_tax_ref1 + t3.citm_incl_tax_ref2 + t3.citm_incl_tax_ref3 + t3.citm_incl_tax_ref4 > 0'. Why is that? Is it a bug?