
/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.apache.carbondata.cluster.sdv.generated

import org.apache.spark.sql.common.util._
import org.scalatest.BeforeAndAfterAll

/**
 * Test Class for QueriesNormalTestCase to verify all scenerios
 */

class QueriesNormalTestCase extends QueryTest with BeforeAndAfterAll {

  //OSCON_Query_CreateTable
  test("OSCON_Query_CreateTable", Include) {
    sql(s"""drop table if exists oscon_carbon""").collect

    sql(s"""CREATE TABLE oscon_carbon (CUST_ID String,CUST_COUNTRY String,CUST_STATE String,CUST_CITY String,ACTIVE_AREA_ID String,ACTIVE_COUNTRY String,ACTIVE_PROVINCE String,ACTIVE_CITY String,ACTIVE_DISTRICT String,LATEST_AREAID String,LATEST_COUNTRY String,LATEST_PROVINCE String,LATEST_CITY String,LATEST_DISTRICT String,WH_COUNTRY String,WH_STATE String,WH_CITY String,WH_COUNTY String,CUST_JOB_TITLE String,CUST_BUY_POTENTIAL String,PROD_UNQ_MDL_ID String,PROD_BRAND_NAME String,PRODUCT_NAME String,PRODUCT_MODEL String,PROD_MODEL_ID String,PROD_STD_CST int,REG_UNIT_PRICE int,TOTAL_PRD_COST int,TOTAL_PRD_DISC int,PROD_OFF_PRICE int,TOTAL_TX_AMT int,PROD_UNITS int,PROD_WEIGHT int,UNIT_PRICE_DSCNT_PCT int,DSCNT_AMT int,OL_SALES_PRICE int,PROD_COLOR String,ITM_ID String,ITM_NAME String,PRMTION_ID String,PRMTION_NAME String,SHP_MODE_ID String,SHP_MODE String,DELIVERY_COUNTRY String,DELIVERY_STATE String,DELIVERY_CITY String,DELIVERY_DISTRICT String,TRACKING_NO String,ACTIVE_EMUI_VERSION String,WH_NAME String,STR_ORDER_NO String,STR_ORDER_DATE String,OL_ORDER_NO String,OL_ORDER_DATE String,OL_SITE String,CUST_NICK_NAME String,CUST_FIRST_NAME String,CUST_LAST_NAME String,CUST_PRFRD_FLG String,CUST_BIRTH_DY String,CUST_BIRTH_MM String,CUST_BIRTH_YR String,CUST_BIRTH_COUNTRY String,CUST_LOGIN String,CUST_EMAIL_ADDR String,CUST_LAST_RVW_DATE String,CUST_SEX String,CUST_ADDRESS_ID String,CUST_STREET_NO String,CUST_STREET_NAME String,CUST_AGE String,CUST_SUITE_NO String,CUST_ZIP String,CUST_COUNTY String,PRODUCT_ID String,PROD_UNQ_DEVICE_ADDR String,PROD_UQ_UUID String,PROD_SHELL_COLOR String,DEVICE_NAME String,PROD_SHORT_DESC String,PROD_LONG_DESC String,PROD_THUMB String,PROD_IMAGE String,PROD_UPDATE_DATE String,PROD_BAR_CODE String,PROD_LIVE String,PROD_LOC String,PROD_RAM String,PROD_ROM String,PROD_CPU_CLOCK String,PROD_SERIES String,ITM_REC_START_DATE String,ITM_REC_END_DATE String,ITM_BRAND_ID String,ITM_BRAND String,ITM_CLASS_ID String,ITM_CLASS String,ITM_CATEGORY_ID String,ITM_CATEGORY String,ITM_MANUFACT_ID String,ITM_MANUFACT String,ITM_FORMULATION String,ITM_COLOR String,ITM_CONTAINER String,ITM_MANAGER_ID String,PRM_START_DATE String,PRM_END_DATE String,PRM_CHANNEL_DMAIL String,PRM_CHANNEL_EMAIL String,PRM_CHANNEL_CAT String,PRM_CHANNEL_TV String,PRM_CHANNEL_RADIO String,PRM_CHANNEL_PRESS String,PRM_CHANNEL_EVENT String,PRM_CHANNEL_DEMO String,PRM_CHANNEL_DETAILS String,PRM_PURPOSE String,PRM_DSCNT_ACTIVE String,SHP_CODE String,SHP_CARRIER String,SHP_CONTRACT String,CHECK_DATE String,CHECK_YR String,CHECK_MM String,CHECK_DY String,CHECK_HOUR String,BOM String,INSIDE_NAME String,PACKING_DATE String,PACKING_YR String,PACKING_MM String,PACKING_DY String,PACKING_HOUR String,DELIVERY_PROVINCE String,PACKING_LIST_NO String,ACTIVE_CHECK_TIME String,ACTIVE_CHECK_YR String,ACTIVE_CHECK_MM String,ACTIVE_CHECK_DY String,ACTIVE_CHECK_HOUR String,ACTIVE_NETWORK String,ACTIVE_FIRMWARE_VER String,ACTIVE_OS_VERSION String,LATEST_CHECK_TIME String,LATEST_CHECK_YR String,LATEST_CHECK_MM String,LATEST_CHECK_DY String,LATEST_CHECK_HOUR String,LATEST_FIRMWARE_VER String,LATEST_EMUI_VERSION String,LATEST_OS_VERSION String,LATEST_NETWORK String,WH_ID String,WH_STREET_NO String,WH_STREET_NAME String,WH_STREET_TYPE String,WH_SUITE_NO String,WH_ZIP String,OL_SITE_DESC String,OL_RET_ORDER_NO String,OL_RET_DATE String,CUST_DEP_COUNT int,CUST_VEHICLE_COUNT int,CUST_ADDRESS_CNT int,CUST_CRNT_CDEMO_CNT int,CUST_CRNT_HDEMO_CNT int,CUST_CRNT_ADDR_DM int,CUST_FIRST_SHIPTO_CNT int,CUST_FIRST_SALES_CNT int,CUST_GMT_OFFSET int,CUST_DEMO_CNT int,CUST_INCOME int,PROD_UNLIMITED int,EXTENDED_AMT int,FREIGHT_CHRG int,WAITING_PERIOD int,DELIVERY_PERIOD int,ITM_CRNT_PRICE int,ITM_UNITS int,ITM_WSLE_CST int,ITM_SIZE int,PRM_CST int,PRM_RESPONSE_TARGET int,PRM_ITM_DM int,SHP_MODE_CNT int,WH_GMT_OFFSET int,WH_SQ_FT int,STR_ORD_QTY int,STR_WSLE_CST int,STR_LIST_PRICE int,STR_SALES_PRICE int,STR_EXT_DSCNT_AMT int,STR_EXT_SALES_PRICE int,STR_EXT_WSLE_CST int,STR_EXT_LIST_PRICE int,STR_EXT_TX int,STR_COUPON_AMT int,STR_NET_PAID int,STR_NET_PAID_INC_TX int,STR_NET_PRFT int,STR_SOLD_YR_CNT int,STR_SOLD_MM_CNT int,STR_SOLD_ITM_CNT int,STR_TOTAL_CUST_CNT int,STR_AREA_CNT int,STR_DEMO_CNT int,STR_OFFER_CNT int,STR_PRM_CNT int,STR_TICKET_CNT int,STR_NET_PRFT_DM_A int,STR_NET_PRFT_DM_B int,STR_NET_PRFT_DM_C int,STR_NET_PRFT_DM_D int,STR_NET_PRFT_DM_E int,STR_RET_STR_ID int,STR_RET_REASON_CNT int,STR_RET_TICKET_NO int,STR_RTRN_QTY int,STR_RTRN_AMT int,STR_RTRN_TX int,STR_RTRN_AMT_INC_TX int,STR_RET_FEE int,STR_RTRN_SHIP_CST int,STR_RFNDD_CSH int,STR_REVERSED_CHRG int,STR_STR_CREDIT int,STR_RET_NET_LOSS int,STR_RTRNED_YR_CNT int,STR_RTRN_MM_CNT int,STR_RET_ITM_CNT int,STR_RET_CUST_CNT int,STR_RET_AREA_CNT int,STR_RET_OFFER_CNT int,STR_RET_PRM_CNT int,STR_RET_NET_LOSS_DM_A int,STR_RET_NET_LOSS_DM_B int,STR_RET_NET_LOSS_DM_C int,STR_RET_NET_LOSS_DM_D int,OL_ORD_QTY int,OL_WSLE_CST int,OL_LIST_PRICE int,OL_EXT_DSCNT_AMT int,OL_EXT_SALES_PRICE int,OL_EXT_WSLE_CST int,OL_EXT_LIST_PRICE int,OL_EXT_TX int,OL_COUPON_AMT int,OL_EXT_SHIP_CST int,OL_NET_PAID int,OL_NET_PAID_INC_TX int,OL_NET_PAID_INC_SHIP int,OL_NET_PAID_INC_SHIP_TX int,OL_NET_PRFT int,OL_SOLD_YR_CNT int,OL_SOLD_MM_CNT int,OL_SHIP_DATE_CNT int,OL_ITM_CNT int,OL_BILL_CUST_CNT int,OL_BILL_AREA_CNT int,OL_BILL_DEMO_CNT int,OL_BILL_OFFER_CNT int,OL_SHIP_CUST_CNT int,OL_SHIP_AREA_CNT int,OL_SHIP_DEMO_CNT int,OL_SHIP_OFFER_CNT int,OL_WEB_PAGE_CNT int,OL_WEB_SITE_CNT int,OL_SHIP_MODE_CNT int,OL_WH_CNT int,OL_PRM_CNT int,OL_NET_PRFT_DM_A int,OL_NET_PRFT_DM_B int,OL_NET_PRFT_DM_C int,OL_NET_PRFT_DM_D int,OL_RET_RTRN_QTY int,OL_RTRN_AMT int,OL_RTRN_TX int,OL_RTRN_AMT_INC_TX int,OL_RET_FEE int,OL_RTRN_SHIP_CST int,OL_RFNDD_CSH int,OL_REVERSED_CHRG int,OL_ACCOUNT_CREDIT int,OL_RTRNED_YR_CNT int,OL_RTRNED_MM_CNT int,OL_RTRITM_CNT int,OL_RFNDD_CUST_CNT int,OL_RFNDD_AREA_CNT int,OL_RFNDD_DEMO_CNT int,OL_RFNDD_OFFER_CNT int,OL_RTRNING_CUST_CNT int,OL_RTRNING_AREA_CNT int,OL_RTRNING_DEMO_CNT int,OL_RTRNING_OFFER_CNT int,OL_RTRWEB_PAGE_CNT int,OL_REASON_CNT int,OL_NET_LOSS int,OL_NET_LOSS_DM_A int,OL_NET_LOSS_DM_B int,OL_NET_LOSS_DM_C int)  STORED BY 'org.apache.carbondata.format' """).collect

  }


  //OSCON_Query_DataLoad
  test("OSCON_Query_DataLoad", Include) {

    sql(s"""LOAD DATA INPATH '$resourcesPath/Data/oscon/oscon.csv' INTO table oscon_carbon OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','FILEHEADER'='CUST_ID,CUST_COUNTRY,CUST_STATE,CUST_CITY,ACTIVE_AREA_ID,ACTIVE_COUNTRY,ACTIVE_PROVINCE,ACTIVE_CITY,ACTIVE_DISTRICT,LATEST_AREAID,LATEST_COUNTRY,LATEST_PROVINCE,LATEST_CITY,LATEST_DISTRICT,WH_COUNTRY,WH_STATE,WH_CITY,WH_COUNTY,CUST_JOB_TITLE,CUST_BUY_POTENTIAL,PROD_UNQ_MDL_ID,PROD_BRAND_NAME,PRODUCT_NAME,PRODUCT_MODEL,PROD_MODEL_ID,PROD_STD_CST,REG_UNIT_PRICE,TOTAL_PRD_COST,TOTAL_PRD_DISC,PROD_OFF_PRICE,TOTAL_TX_AMT,PROD_UNITS,PROD_WEIGHT,UNIT_PRICE_DSCNT_PCT,DSCNT_AMT,OL_SALES_PRICE,PROD_COLOR,ITM_ID,ITM_NAME,PRMTION_ID,PRMTION_NAME,SHP_MODE_ID,SHP_MODE,DELIVERY_COUNTRY,DELIVERY_STATE,DELIVERY_CITY,DELIVERY_DISTRICT,TRACKING_NO,ACTIVE_EMUI_VERSION,WH_NAME,STR_ORDER_NO,STR_ORDER_DATE,OL_ORDER_NO,OL_ORDER_DATE,OL_SITE,CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE,PROD_UPDATE_DATE,PROD_BAR_CODE,PROD_LIVE,PROD_LOC,PROD_RAM,PROD_ROM,PROD_CPU_CLOCK,PROD_SERIES,ITM_REC_START_DATE,ITM_REC_END_DATE,ITM_BRAND_ID,ITM_BRAND,ITM_CLASS_ID,ITM_CLASS,ITM_CATEGORY_ID,ITM_CATEGORY,ITM_MANUFACT_ID,ITM_MANUFACT,ITM_FORMULATION,ITM_COLOR,ITM_CONTAINER,ITM_MANAGER_ID,PRM_START_DATE,PRM_END_DATE,PRM_CHANNEL_DMAIL,PRM_CHANNEL_EMAIL,PRM_CHANNEL_CAT,PRM_CHANNEL_TV,PRM_CHANNEL_RADIO,PRM_CHANNEL_PRESS,PRM_CHANNEL_EVENT,PRM_CHANNEL_DEMO,PRM_CHANNEL_DETAILS,PRM_PURPOSE,PRM_DSCNT_ACTIVE,SHP_CODE,SHP_CARRIER,SHP_CONTRACT,CHECK_DATE,CHECK_YR,CHECK_MM,CHECK_DY,CHECK_HOUR,BOM,INSIDE_NAME,PACKING_DATE,PACKING_YR,PACKING_MM,PACKING_DY,PACKING_HOUR,DELIVERY_PROVINCE,PACKING_LIST_NO,ACTIVE_CHECK_TIME,ACTIVE_CHECK_YR,ACTIVE_CHECK_MM,ACTIVE_CHECK_DY,ACTIVE_CHECK_HOUR,ACTIVE_NETWORK,ACTIVE_FIRMWARE_VER,ACTIVE_OS_VERSION,LATEST_CHECK_TIME,LATEST_CHECK_YR,LATEST_CHECK_MM,LATEST_CHECK_DY,LATEST_CHECK_HOUR,LATEST_FIRMWARE_VER,LATEST_EMUI_VERSION,LATEST_OS_VERSION,LATEST_NETWORK,WH_ID,WH_STREET_NO,WH_STREET_NAME,WH_STREET_TYPE,WH_SUITE_NO,WH_ZIP,OL_SITE_DESC,OL_RET_ORDER_NO,OL_RET_DATE,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PROD_UNLIMITED,EXTENDED_AMT,FREIGHT_CHRG,WAITING_PERIOD,DELIVERY_PERIOD,ITM_CRNT_PRICE,ITM_UNITS,ITM_WSLE_CST,ITM_SIZE,PRM_CST,PRM_RESPONSE_TARGET,PRM_ITM_DM,SHP_MODE_CNT,WH_GMT_OFFSET,WH_SQ_FT,STR_ORD_QTY,STR_WSLE_CST,STR_LIST_PRICE,STR_SALES_PRICE,STR_EXT_DSCNT_AMT,STR_EXT_SALES_PRICE,STR_EXT_WSLE_CST,STR_EXT_LIST_PRICE,STR_EXT_TX,STR_COUPON_AMT,STR_NET_PAID,STR_NET_PAID_INC_TX,STR_NET_PRFT,STR_SOLD_YR_CNT,STR_SOLD_MM_CNT,STR_SOLD_ITM_CNT,STR_TOTAL_CUST_CNT,STR_AREA_CNT,STR_DEMO_CNT,STR_OFFER_CNT,STR_PRM_CNT,STR_TICKET_CNT,STR_NET_PRFT_DM_A,STR_NET_PRFT_DM_B,STR_NET_PRFT_DM_C,STR_NET_PRFT_DM_D,STR_NET_PRFT_DM_E,STR_RET_STR_ID,STR_RET_REASON_CNT,STR_RET_TICKET_NO,STR_RTRN_QTY,STR_RTRN_AMT,STR_RTRN_TX,STR_RTRN_AMT_INC_TX,STR_RET_FEE,STR_RTRN_SHIP_CST,STR_RFNDD_CSH,STR_REVERSED_CHRG,STR_STR_CREDIT,STR_RET_NET_LOSS,STR_RTRNED_YR_CNT,STR_RTRN_MM_CNT,STR_RET_ITM_CNT,STR_RET_CUST_CNT,STR_RET_AREA_CNT,STR_RET_OFFER_CNT,STR_RET_PRM_CNT,STR_RET_NET_LOSS_DM_A,STR_RET_NET_LOSS_DM_B,STR_RET_NET_LOSS_DM_C,STR_RET_NET_LOSS_DM_D,OL_ORD_QTY,OL_WSLE_CST,OL_LIST_PRICE,OL_EXT_DSCNT_AMT,OL_EXT_SALES_PRICE,OL_EXT_WSLE_CST,OL_EXT_LIST_PRICE,OL_EXT_TX,OL_COUPON_AMT,OL_EXT_SHIP_CST,OL_NET_PAID,OL_NET_PAID_INC_TX,OL_NET_PAID_INC_SHIP,OL_NET_PAID_INC_SHIP_TX,OL_NET_PRFT,OL_SOLD_YR_CNT,OL_SOLD_MM_CNT,OL_SHIP_DATE_CNT,OL_ITM_CNT,OL_BILL_CUST_CNT,OL_BILL_AREA_CNT,OL_BILL_DEMO_CNT,OL_BILL_OFFER_CNT,OL_SHIP_CUST_CNT,OL_SHIP_AREA_CNT,OL_SHIP_DEMO_CNT,OL_SHIP_OFFER_CNT,OL_WEB_PAGE_CNT,OL_WEB_SITE_CNT,OL_SHIP_MODE_CNT,OL_WH_CNT,OL_PRM_CNT,OL_NET_PRFT_DM_A,OL_NET_PRFT_DM_B,OL_NET_PRFT_DM_C,OL_NET_PRFT_DM_D,OL_RET_RTRN_QTY,OL_RTRN_AMT,OL_RTRN_TX,OL_RTRN_AMT_INC_TX,OL_RET_FEE,OL_RTRN_SHIP_CST,OL_RFNDD_CSH,OL_REVERSED_CHRG,OL_ACCOUNT_CREDIT,OL_RTRNED_YR_CNT,OL_RTRNED_MM_CNT,OL_RTRITM_CNT,OL_RFNDD_CUST_CNT,OL_RFNDD_AREA_CNT,OL_RFNDD_DEMO_CNT,OL_RFNDD_OFFER_CNT,OL_RTRNING_CUST_CNT,OL_RTRNING_AREA_CNT,OL_RTRNING_DEMO_CNT,OL_RTRNING_OFFER_CNT,OL_RTRWEB_PAGE_CNT,OL_REASON_CNT,OL_NET_LOSS,OL_NET_LOSS_DM_A,OL_NET_LOSS_DM_B,OL_NET_LOSS_DM_C')""").collect

  }


  //OSCON_Query_CreateTableVIP
  test("OSCON_Query_CreateTableVIP", Include) {
    sql(s"""drop table if exists oscon_carbon_vip""").collect

    sql(s"""CREATE TABLE oscon_carbon_vip (CUST_ID String,CUST_COUNTRY String,CUST_STATE String,CUST_CITY String,ACTIVE_AREA_ID String,ACTIVE_COUNTRY String,ACTIVE_PROVINCE String,ACTIVE_CITY String,ACTIVE_DISTRICT String,LATEST_AREAID String,LATEST_COUNTRY String,LATEST_PROVINCE String,LATEST_CITY String,LATEST_DISTRICT String,WH_COUNTRY String,WH_STATE String,WH_CITY String,WH_COUNTY String,CUST_JOB_TITLE String,CUST_BUY_POTENTIAL String,PROD_UNQ_MDL_ID String,PROD_BRAND_NAME String,PRODUCT_NAME String,PRODUCT_MODEL String,PROD_MODEL_ID String,PROD_STD_CST int,REG_UNIT_PRICE int,TOTAL_PRD_COST int,TOTAL_PRD_DISC int,PROD_OFF_PRICE int,TOTAL_TX_AMT int,PROD_UNITS int,PROD_WEIGHT int,UNIT_PRICE_DSCNT_PCT int,DSCNT_AMT int,OL_SALES_PRICE int,PROD_COLOR String,ITM_ID String,ITM_NAME String,PRMTION_ID String,PRMTION_NAME String,SHP_MODE_ID String,SHP_MODE String,DELIVERY_COUNTRY String,DELIVERY_STATE String,DELIVERY_CITY String,DELIVERY_DISTRICT String,TRACKING_NO String,ACTIVE_EMUI_VERSION String,WH_NAME String,STR_ORDER_NO String,STR_ORDER_DATE String,OL_ORDER_NO String,OL_ORDER_DATE String,OL_SITE String,CUST_NICK_NAME String,CUST_FIRST_NAME String,CUST_LAST_NAME String,CUST_PRFRD_FLG String,CUST_BIRTH_DY String,CUST_BIRTH_MM String,CUST_BIRTH_YR String,CUST_BIRTH_COUNTRY String,CUST_LOGIN String,CUST_EMAIL_ADDR String,CUST_LAST_RVW_DATE String,CUST_SEX String,CUST_ADDRESS_ID String,CUST_STREET_NO String,CUST_STREET_NAME String,CUST_AGE String,CUST_SUITE_NO String,CUST_ZIP String,CUST_COUNTY String,PRODUCT_ID String,PROD_UNQ_DEVICE_ADDR String,PROD_UQ_UUID String,PROD_SHELL_COLOR String,DEVICE_NAME String,PROD_SHORT_DESC String,PROD_LONG_DESC String,PROD_THUMB String,PROD_IMAGE String,PROD_UPDATE_DATE String,PROD_BAR_CODE String,PROD_LIVE String,PROD_LOC String,PROD_RAM String,PROD_ROM String,PROD_CPU_CLOCK String,PROD_SERIES String,ITM_REC_START_DATE String,ITM_REC_END_DATE String,ITM_BRAND_ID String,ITM_BRAND String,ITM_CLASS_ID String,ITM_CLASS String,ITM_CATEGORY_ID String,ITM_CATEGORY String,ITM_MANUFACT_ID String,ITM_MANUFACT String,ITM_FORMULATION String,ITM_COLOR String,ITM_CONTAINER String,ITM_MANAGER_ID String,PRM_START_DATE String,PRM_END_DATE String,PRM_CHANNEL_DMAIL String,PRM_CHANNEL_EMAIL String,PRM_CHANNEL_CAT String,PRM_CHANNEL_TV String,PRM_CHANNEL_RADIO String,PRM_CHANNEL_PRESS String,PRM_CHANNEL_EVENT String,PRM_CHANNEL_DEMO String,PRM_CHANNEL_DETAILS String,PRM_PURPOSE String,PRM_DSCNT_ACTIVE String,SHP_CODE String,SHP_CARRIER String,SHP_CONTRACT String,CHECK_DATE String,CHECK_YR String,CHECK_MM String,CHECK_DY String,CHECK_HOUR String,BOM String,INSIDE_NAME String,PACKING_DATE String,PACKING_YR String,PACKING_MM String,PACKING_DY String,PACKING_HOUR String,DELIVERY_PROVINCE String,PACKING_LIST_NO String,ACTIVE_CHECK_TIME String,ACTIVE_CHECK_YR String,ACTIVE_CHECK_MM String,ACTIVE_CHECK_DY String,ACTIVE_CHECK_HOUR String,ACTIVE_NETWORK String,ACTIVE_FIRMWARE_VER String,ACTIVE_OS_VERSION String,LATEST_CHECK_TIME String,LATEST_CHECK_YR String,LATEST_CHECK_MM String,LATEST_CHECK_DY String,LATEST_CHECK_HOUR String,LATEST_FIRMWARE_VER String,LATEST_EMUI_VERSION String,LATEST_OS_VERSION String,LATEST_NETWORK String,WH_ID String,WH_STREET_NO String,WH_STREET_NAME String,WH_STREET_TYPE String,WH_SUITE_NO String,WH_ZIP String,OL_SITE_DESC String,OL_RET_ORDER_NO String,OL_RET_DATE String,CUST_DEP_COUNT int,CUST_VEHICLE_COUNT int,CUST_ADDRESS_CNT int,CUST_CRNT_CDEMO_CNT int,CUST_CRNT_HDEMO_CNT int,CUST_CRNT_ADDR_DM int,CUST_FIRST_SHIPTO_CNT int,CUST_FIRST_SALES_CNT int,CUST_GMT_OFFSET int,CUST_DEMO_CNT int,CUST_INCOME int,PROD_UNLIMITED int,EXTENDED_AMT int,FREIGHT_CHRG int,WAITING_PERIOD int,DELIVERY_PERIOD int,ITM_CRNT_PRICE int,ITM_UNITS int,ITM_WSLE_CST int,ITM_SIZE int,PRM_CST int,PRM_RESPONSE_TARGET int,PRM_ITM_DM int,SHP_MODE_CNT int,WH_GMT_OFFSET int,WH_SQ_FT int,STR_ORD_QTY int,STR_WSLE_CST int,STR_LIST_PRICE int,STR_SALES_PRICE int,STR_EXT_DSCNT_AMT int,STR_EXT_SALES_PRICE int,STR_EXT_WSLE_CST int,STR_EXT_LIST_PRICE int,STR_EXT_TX int,STR_COUPON_AMT int,STR_NET_PAID int,STR_NET_PAID_INC_TX int,STR_NET_PRFT int,STR_SOLD_YR_CNT int,STR_SOLD_MM_CNT int,STR_SOLD_ITM_CNT int,STR_TOTAL_CUST_CNT int,STR_AREA_CNT int,STR_DEMO_CNT int,STR_OFFER_CNT int,STR_PRM_CNT int,STR_TICKET_CNT int,STR_NET_PRFT_DM_A int,STR_NET_PRFT_DM_B int,STR_NET_PRFT_DM_C int,STR_NET_PRFT_DM_D int,STR_NET_PRFT_DM_E int,STR_RET_STR_ID int,STR_RET_REASON_CNT int,STR_RET_TICKET_NO int,STR_RTRN_QTY int,STR_RTRN_AMT int,STR_RTRN_TX int,STR_RTRN_AMT_INC_TX int,STR_RET_FEE int,STR_RTRN_SHIP_CST int,STR_RFNDD_CSH int,STR_REVERSED_CHRG int,STR_STR_CREDIT int,STR_RET_NET_LOSS int,STR_RTRNED_YR_CNT int,STR_RTRN_MM_CNT int,STR_RET_ITM_CNT int,STR_RET_CUST_CNT int,STR_RET_AREA_CNT int,STR_RET_OFFER_CNT int,STR_RET_PRM_CNT int,STR_RET_NET_LOSS_DM_A int,STR_RET_NET_LOSS_DM_B int,STR_RET_NET_LOSS_DM_C int,STR_RET_NET_LOSS_DM_D int,OL_ORD_QTY int,OL_WSLE_CST int,OL_LIST_PRICE int,OL_EXT_DSCNT_AMT int,OL_EXT_SALES_PRICE int,OL_EXT_WSLE_CST int,OL_EXT_LIST_PRICE int,OL_EXT_TX int,OL_COUPON_AMT int,OL_EXT_SHIP_CST int,OL_NET_PAID int,OL_NET_PAID_INC_TX int,OL_NET_PAID_INC_SHIP int,OL_NET_PAID_INC_SHIP_TX int,OL_NET_PRFT int,OL_SOLD_YR_CNT int,OL_SOLD_MM_CNT int,OL_SHIP_DATE_CNT int,OL_ITM_CNT int,OL_BILL_CUST_CNT int,OL_BILL_AREA_CNT int,OL_BILL_DEMO_CNT int,OL_BILL_OFFER_CNT int,OL_SHIP_CUST_CNT int,OL_SHIP_AREA_CNT int,OL_SHIP_DEMO_CNT int,OL_SHIP_OFFER_CNT int,OL_WEB_PAGE_CNT int,OL_WEB_SITE_CNT int,OL_SHIP_MODE_CNT int,OL_WH_CNT int,OL_PRM_CNT int,OL_NET_PRFT_DM_A int,OL_NET_PRFT_DM_B int,OL_NET_PRFT_DM_C int,OL_NET_PRFT_DM_D int,OL_RET_RTRN_QTY int,OL_RTRN_AMT int,OL_RTRN_TX int,OL_RTRN_AMT_INC_TX int,OL_RET_FEE int,OL_RTRN_SHIP_CST int,OL_RFNDD_CSH int,OL_REVERSED_CHRG int,OL_ACCOUNT_CREDIT int,OL_RTRNED_YR_CNT int,OL_RTRNED_MM_CNT int,OL_RTRITM_CNT int,OL_RFNDD_CUST_CNT int,OL_RFNDD_AREA_CNT int,OL_RFNDD_DEMO_CNT int,OL_RFNDD_OFFER_CNT int,OL_RTRNING_CUST_CNT int,OL_RTRNING_AREA_CNT int,OL_RTRNING_DEMO_CNT int,OL_RTRNING_OFFER_CNT int,OL_RTRWEB_PAGE_CNT int,OL_REASON_CNT int,OL_NET_LOSS int,OL_NET_LOSS_DM_A int,OL_NET_LOSS_DM_B int,OL_NET_LOSS_DM_C int)  STORED BY 'org.apache.carbondata.format' """).collect

  }


  //OSCON_Query_DataLoadVIP
  test("OSCON_Query_DataLoadVIP", Include) {

    sql(s"""LOAD DATA INPATH '$resourcesPath/Data/oscon/oscon.csv' INTO table oscon_carbon_vip OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','FILEHEADER'='CUST_ID,CUST_COUNTRY,CUST_STATE,CUST_CITY,ACTIVE_AREA_ID,ACTIVE_COUNTRY,ACTIVE_PROVINCE,ACTIVE_CITY,ACTIVE_DISTRICT,LATEST_AREAID,LATEST_COUNTRY,LATEST_PROVINCE,LATEST_CITY,LATEST_DISTRICT,WH_COUNTRY,WH_STATE,WH_CITY,WH_COUNTY,CUST_JOB_TITLE,CUST_BUY_POTENTIAL,PROD_UNQ_MDL_ID,PROD_BRAND_NAME,PRODUCT_NAME,PRODUCT_MODEL,PROD_MODEL_ID,PROD_STD_CST,REG_UNIT_PRICE,TOTAL_PRD_COST,TOTAL_PRD_DISC,PROD_OFF_PRICE,TOTAL_TX_AMT,PROD_UNITS,PROD_WEIGHT,UNIT_PRICE_DSCNT_PCT,DSCNT_AMT,OL_SALES_PRICE,PROD_COLOR,ITM_ID,ITM_NAME,PRMTION_ID,PRMTION_NAME,SHP_MODE_ID,SHP_MODE,DELIVERY_COUNTRY,DELIVERY_STATE,DELIVERY_CITY,DELIVERY_DISTRICT,TRACKING_NO,ACTIVE_EMUI_VERSION,WH_NAME,STR_ORDER_NO,STR_ORDER_DATE,OL_ORDER_NO,OL_ORDER_DATE,OL_SITE,CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE,PROD_UPDATE_DATE,PROD_BAR_CODE,PROD_LIVE,PROD_LOC,PROD_RAM,PROD_ROM,PROD_CPU_CLOCK,PROD_SERIES,ITM_REC_START_DATE,ITM_REC_END_DATE,ITM_BRAND_ID,ITM_BRAND,ITM_CLASS_ID,ITM_CLASS,ITM_CATEGORY_ID,ITM_CATEGORY,ITM_MANUFACT_ID,ITM_MANUFACT,ITM_FORMULATION,ITM_COLOR,ITM_CONTAINER,ITM_MANAGER_ID,PRM_START_DATE,PRM_END_DATE,PRM_CHANNEL_DMAIL,PRM_CHANNEL_EMAIL,PRM_CHANNEL_CAT,PRM_CHANNEL_TV,PRM_CHANNEL_RADIO,PRM_CHANNEL_PRESS,PRM_CHANNEL_EVENT,PRM_CHANNEL_DEMO,PRM_CHANNEL_DETAILS,PRM_PURPOSE,PRM_DSCNT_ACTIVE,SHP_CODE,SHP_CARRIER,SHP_CONTRACT,CHECK_DATE,CHECK_YR,CHECK_MM,CHECK_DY,CHECK_HOUR,BOM,INSIDE_NAME,PACKING_DATE,PACKING_YR,PACKING_MM,PACKING_DY,PACKING_HOUR,DELIVERY_PROVINCE,PACKING_LIST_NO,ACTIVE_CHECK_TIME,ACTIVE_CHECK_YR,ACTIVE_CHECK_MM,ACTIVE_CHECK_DY,ACTIVE_CHECK_HOUR,ACTIVE_NETWORK,ACTIVE_FIRMWARE_VER,ACTIVE_OS_VERSION,LATEST_CHECK_TIME,LATEST_CHECK_YR,LATEST_CHECK_MM,LATEST_CHECK_DY,LATEST_CHECK_HOUR,LATEST_FIRMWARE_VER,LATEST_EMUI_VERSION,LATEST_OS_VERSION,LATEST_NETWORK,WH_ID,WH_STREET_NO,WH_STREET_NAME,WH_STREET_TYPE,WH_SUITE_NO,WH_ZIP,OL_SITE_DESC,OL_RET_ORDER_NO,OL_RET_DATE,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PROD_UNLIMITED,EXTENDED_AMT,FREIGHT_CHRG,WAITING_PERIOD,DELIVERY_PERIOD,ITM_CRNT_PRICE,ITM_UNITS,ITM_WSLE_CST,ITM_SIZE,PRM_CST,PRM_RESPONSE_TARGET,PRM_ITM_DM,SHP_MODE_CNT,WH_GMT_OFFSET,WH_SQ_FT,STR_ORD_QTY,STR_WSLE_CST,STR_LIST_PRICE,STR_SALES_PRICE,STR_EXT_DSCNT_AMT,STR_EXT_SALES_PRICE,STR_EXT_WSLE_CST,STR_EXT_LIST_PRICE,STR_EXT_TX,STR_COUPON_AMT,STR_NET_PAID,STR_NET_PAID_INC_TX,STR_NET_PRFT,STR_SOLD_YR_CNT,STR_SOLD_MM_CNT,STR_SOLD_ITM_CNT,STR_TOTAL_CUST_CNT,STR_AREA_CNT,STR_DEMO_CNT,STR_OFFER_CNT,STR_PRM_CNT,STR_TICKET_CNT,STR_NET_PRFT_DM_A,STR_NET_PRFT_DM_B,STR_NET_PRFT_DM_C,STR_NET_PRFT_DM_D,STR_NET_PRFT_DM_E,STR_RET_STR_ID,STR_RET_REASON_CNT,STR_RET_TICKET_NO,STR_RTRN_QTY,STR_RTRN_AMT,STR_RTRN_TX,STR_RTRN_AMT_INC_TX,STR_RET_FEE,STR_RTRN_SHIP_CST,STR_RFNDD_CSH,STR_REVERSED_CHRG,STR_STR_CREDIT,STR_RET_NET_LOSS,STR_RTRNED_YR_CNT,STR_RTRN_MM_CNT,STR_RET_ITM_CNT,STR_RET_CUST_CNT,STR_RET_AREA_CNT,STR_RET_OFFER_CNT,STR_RET_PRM_CNT,STR_RET_NET_LOSS_DM_A,STR_RET_NET_LOSS_DM_B,STR_RET_NET_LOSS_DM_C,STR_RET_NET_LOSS_DM_D,OL_ORD_QTY,OL_WSLE_CST,OL_LIST_PRICE,OL_EXT_DSCNT_AMT,OL_EXT_SALES_PRICE,OL_EXT_WSLE_CST,OL_EXT_LIST_PRICE,OL_EXT_TX,OL_COUPON_AMT,OL_EXT_SHIP_CST,OL_NET_PAID,OL_NET_PAID_INC_TX,OL_NET_PAID_INC_SHIP,OL_NET_PAID_INC_SHIP_TX,OL_NET_PRFT,OL_SOLD_YR_CNT,OL_SOLD_MM_CNT,OL_SHIP_DATE_CNT,OL_ITM_CNT,OL_BILL_CUST_CNT,OL_BILL_AREA_CNT,OL_BILL_DEMO_CNT,OL_BILL_OFFER_CNT,OL_SHIP_CUST_CNT,OL_SHIP_AREA_CNT,OL_SHIP_DEMO_CNT,OL_SHIP_OFFER_CNT,OL_WEB_PAGE_CNT,OL_WEB_SITE_CNT,OL_SHIP_MODE_CNT,OL_WH_CNT,OL_PRM_CNT,OL_NET_PRFT_DM_A,OL_NET_PRFT_DM_B,OL_NET_PRFT_DM_C,OL_NET_PRFT_DM_D,OL_RET_RTRN_QTY,OL_RTRN_AMT,OL_RTRN_TX,OL_RTRN_AMT_INC_TX,OL_RET_FEE,OL_RTRN_SHIP_CST,OL_RFNDD_CSH,OL_REVERSED_CHRG,OL_ACCOUNT_CREDIT,OL_RTRNED_YR_CNT,OL_RTRNED_MM_CNT,OL_RTRITM_CNT,OL_RFNDD_CUST_CNT,OL_RFNDD_AREA_CNT,OL_RFNDD_DEMO_CNT,OL_RFNDD_OFFER_CNT,OL_RTRNING_CUST_CNT,OL_RTRNING_AREA_CNT,OL_RTRNING_DEMO_CNT,OL_RTRNING_OFFER_CNT,OL_RTRWEB_PAGE_CNT,OL_REASON_CNT,OL_NET_LOSS,OL_NET_LOSS_DM_A,OL_NET_LOSS_DM_B,OL_NET_LOSS_DM_C')""").collect

  }


  //OSCON_Query_TC001
  test("OSCON_Query_TC001", Include) {

    sql(s"""select count(*) from  oscon_carbon""").collect

  }


  //OSCON_Query_TC002
  test("OSCON_Query_TC002", Include) {

    sql(s"""select PRODUCT_NAME,count(distinct CUST_ID), sum(PROD_UNITS) from  oscon_carbon where CUST_JOB_TITLE ="Marketing Manager" and CUST_COUNTRY ="Australia" and CUST_INCOME >50000 and PROD_BRAND_NAME="Acer" group by PRODUCT_NAME""").collect

  }


  //OSCON_Query_TC003
  test("OSCON_Query_TC003", Include) {

    sql(s"""select CUST_SEX,PROD_COLOR, count(distinct CUST_ID) from  oscon_carbon where CUST_JOB_TITLE ="Marketing Manager" and CUST_COUNTRY ="Australia" and CUST_INCOME >50000 and PRODUCT_NAME = "Acer Liquid Zest" group by CUST_SEX, PROD_COLOR""").collect

  }


  //OSCON_Query_TC004
  test("OSCON_Query_TC004", Include) {

    sql(s"""select CUST_SEX,PROD_COLOR, count(distinct CUST_ID) from  oscon_carbon where  CUST_INCOME >50000 and  TOTAL_TX_AMT < 1500 group by CUST_SEX, PROD_COLOR""").collect

  }


  //OSCON_Query_TC005
  test("OSCON_Query_TC005", Include) {

    sql(s"""select count(distinct CUST_ID), case when (CUST_AGE < 30) then '<30' when (CUST_AGE between 30 and 60) then '30-60' else '60+' END  from  oscon_carbon where PROD_COLOR="GOLD" and PRODUCT_NAME = "Acer Liquid Zest" group by CUST_AGE""").collect

  }


  //OSCON_Query_TC006
  test("OSCON_Query_TC006", Include) {

    sql(s"""select o1.PROD_BRAND_NAME,o1.PRODUCT_NAME,o1.PROD_COLOR,count(o1.CUST_ID) from (select CUST_ID,PROD_BRAND_NAME,PRODUCT_NAME,PROD_COLOR from  oscon_carbon_vip) o1 join (select CUST_ID,PROD_BRAND_NAME,PRODUCT_NAME,PROD_COLOR from  oscon_carbon) o2 on o1.CUST_ID=o2.CUST_ID group by o1.PROD_BRAND_NAME,o1.PRODUCT_NAME,o1.PROD_COLOR""").collect

  }


  //OSCON_Query_TC007
  test("OSCON_Query_TC007", Include) {

    sql(s"""select o1.PROD_BRAND_NAME,o1.PRODUCT_NAME,o1.PROD_COLOR,count(o1.CUST_ID)  from (select CUST_ID,PROD_BRAND_NAME,PRODUCT_NAME,PROD_COLOR from  oscon_carbon_vip) o1 join (select CUST_ID,PROD_BRAND_NAME,PRODUCT_NAME,PROD_COLOR  from  oscon_carbon) o2 on o1.CUST_ID=o2.CUST_ID group by o1.PROD_BRAND_NAME,o1.PRODUCT_NAME,o1.PROD_COLOR order by o1.PROD_BRAND_NAME desc limit 100""").collect

  }


  //OSCON_Query_TC008
  test("OSCON_Query_TC008", Include) {

    sql(s"""select * from  oscon_carbon where CUST_CITY="Basildon" and CUST_PRFRD_FLG="Y" and CUST_LAST_RVW_DATE between "2015-11-30 00:00:00" and "2015-12-03 00:00:00"""").collect

  }


  //OSCON_Query_TC009
  test("OSCON_Query_TC009", Include) {

    sql(s"""select CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE from  oscon_carbon where CUST_CITY="Basildon" and CUST_PRFRD_FLG="Y" and CUST_LAST_RVW_DATE between "2015-11-30 00:00:00" and "2015-12-03 00:00:00"""").collect

  }


  //OSCON_Query_TC010
  test("OSCON_Query_TC010", Include) {

    sql(s"""select * from  oscon_carbon where CUST_ID = "Cust00000000" and CUST_CITY="Basildon" and CUST_LAST_RVW_DATE between "2015-11-30 00:00:00" and "2015-12-03 00:00:00"""").collect

  }


  //OSCON_Query_TC011
  test("OSCON_Query_TC011", Include) {

    sql(s"""select * from  oscon_carbon where PROD_COLOR ="GOLD" and ACTIVE_EMUI_VERSION  like"%73" limit 5000""").collect

  }


  //OSCON_Query_TC012
  test("OSCON_Query_TC012", Include) {

    sql(s"""select PROD_BRAND_NAME,count(CUST_ID) from  oscon_carbon group by PROD_BRAND_NAME""").collect

  }


  //OSCON_Query_TC013
  test("OSCON_Query_TC013", Include) {

    sql(s"""select PROD_BRAND_NAME, SUM(STR_ORD_QTY) from  oscon_carbon group by PROD_BRAND_NAME""").collect

  }


  //OSCON_Query_TC014
  test("OSCON_Query_TC014", Include) {

    sql(s"""select CUST_CITY, AVG(WAITING_PERIOD) from  oscon_carbon group by CUST_CITY""").collect

  }


  //OSCON_Query_TC015
  test("OSCON_Query_TC015", Include) {

    sql(s"""select PROD_COLOR, SUM(STR_ORD_QTY) from  oscon_carbon where CUST_COUNTRY ='Australia' and CUST_CITY = 'Echuca' and PRODUCT_NAME = 'Acer Liquid Zest' group by PROD_COLOR""").collect

  }


  //OSCON_Query_TC016
  test("OSCON_Query_TC016", Include) {

    sql(s"""select PRODUCT_NAME, SUM(STR_ORD_QTY) from  oscon_carbon where CUST_COUNTRY ='Australia' and CUST_CITY = 'Echuca' group by PRODUCT_NAME""").collect

  }


  //OSCON_Query_TC017
  test("OSCON_Query_TC017", Include) {

    sql(s"""select PROD_COLOR, SUM(STR_ORD_QTY) from  oscon_carbon where CUST_COUNTRY ='Australia' and CUST_CITY = 'Echuca' and PRODUCT_NAME = 'Acer Liquid Zest' and CUST_INCOME >50000 group by PROD_COLOR""").collect

  }


  //OSCON_Query_TC018
  test("OSCON_Query_TC018", Include) {

    sql(s"""select PROD_COLOR, SUM(STR_ORD_QTY) from  oscon_carbon where CUST_INCOME >50000 group by PROD_COLOR""").collect

  }


  //OSCON_Query_TC019
  test("OSCON_Query_TC019", Include) {

    sql(s"""select * from  oscon_carbon where CUST_CITY='Echuca' and CUST_PRFRD_FLG='N' and CUST_LAST_RVW_DATE between "2015-01-30 00:00:00" and "2015-12-31 00:00:00"""").collect

  }


  //OSCON_Query_TC020
  test("OSCON_Query_TC020", Include) {

    sql(s"""select SUM(STR_ORD_QTY) , CUST_AGE, case when (CUST_AGE < 30) then '<30' when (CUST_AGE between 30 and 60) then '30-60' else '60+' END  from  oscon_carbon where PROD_COLOR='GOLD' and PRODUCT_NAME = 'Acer Liquid Zest' group by CUST_AGE""").collect

  }


  //OSCON_Query_TC021
  test("OSCON_Query_TC021", Include) {

    sql(s"""select count(distinct CUST_ID), CUST_AGE,case when (CUST_AGE < 30) then '<30' when (CUST_AGE between 30 and 60) then '30-60' else '60+' END  from  oscon_carbon where PROD_COLOR='GOLD' and PRODUCT_NAME = 'Acer Liquid Zest' group by CUST_AGE""").collect

  }


  //OSCON_Query_TC022
  test("OSCON_Query_TC022", Include) {

    sql(s"""SELECT *  from oscon_carbon where CUST_PRFRD_FLG="Y" and PROD_BRAND_NAME = "Acer" and PROD_COLOR = "BLACK" and CUST_LAST_RVW_DATE = "2015-12-01 00:00:00" and CUST_COUNTRY = "United Kingdom" and product_name = "Acer Liquid X2" """).collect

  }


  //OSCON_Query_TC023
  test("OSCON_Query_TC023", Include) {

    sql(s"""SELECT CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE  from oscon_carbon where CUST_PRFRD_FLG="Y" and PROD_BRAND_NAME = "Acer" and PROD_COLOR = "BLACK" and CUST_LAST_RVW_DATE = "2015-12-01 00:00:00" and CUST_COUNTRY = "United Kingdom" and product_name = "Acer Liquid X2" """).collect

  }


  //OSCON_Query_TC024
  test("OSCON_Query_TC024", Include) {

    sql(s"""select CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE from oscon_carbon where CUST_LAST_RVW_DATE = "2015-12-01 00:00:00" and CUST_COUNTRY = "United Kingdom" and product_name = "Acer Liquid X2" """).collect

  }


  //OSCON_Query_TC025
  test("OSCON_Query_TC025", Include) {

    sql(s"""select CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE  from oscon_carbon where DELIVERY_COUNTRY="United Kingdom" and DELIVERY_STATE ="England"  and DELIVERY_CITY = "Basildon" and WH_NAME="Argentina Warehouse"  and OL_SITE="www.amazon.in" """).collect

  }


  //OSCON_Query_TC026
  test("OSCON_Query_TC026", Include) {

    sql(s"""select CUST_NICK_NAME,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_PRFRD_FLG,CUST_BIRTH_DY,CUST_BIRTH_MM,CUST_BIRTH_YR,CUST_BIRTH_COUNTRY,CUST_LOGIN,CUST_EMAIL_ADDR,CUST_LAST_RVW_DATE,CUST_SEX,CUST_ADDRESS_ID,CUST_STREET_NO,CUST_STREET_NAME,CUST_AGE,CUST_SUITE_NO,CUST_ZIP,CUST_COUNTY,CUST_DEP_COUNT,CUST_VEHICLE_COUNT,CUST_ADDRESS_CNT,CUST_CRNT_CDEMO_CNT,CUST_CRNT_HDEMO_CNT,CUST_CRNT_ADDR_DM,CUST_FIRST_SHIPTO_CNT,CUST_FIRST_SALES_CNT,CUST_GMT_OFFSET,CUST_DEMO_CNT,CUST_INCOME,PRODUCT_ID,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_SHELL_COLOR,DEVICE_NAME,PROD_SHORT_DESC,PROD_LONG_DESC,PROD_THUMB,PROD_IMAGE  from oscon_carbon where  CUST_ID = "Cust00000000" """).collect

  }


  //Sample_CreateCube_TC_001
  test("Sample_CreateCube_TC_001", Include) {
    sql(s"""drop table if exists traffic_2g_3g_4g""").collect

    sql(s"""create table IF NOT EXISTS traffic_2g_3g_4g (SOURCE_INFO String ,APP_CATEGORY_ID String ,APP_CATEGORY_NAME String ,APP_SUB_CATEGORY_ID String ,APP_SUB_CATEGORY_NAME String ,RAT_NAME String ,IMSI String ,OFFER_MSISDN String ,OFFER_ID String ,OFFER_OPTION_1 String ,OFFER_OPTION_2 String ,OFFER_OPTION_3 String ,MSISDN String ,PACKAGE_TYPE String ,PACKAGE_PRICE String ,TAG_IMSI String ,TAG_MSISDN String ,PROVINCE String ,CITY String ,AREA_CODE String ,TAC String ,IMEI String ,TERMINAL_TYPE String ,TERMINAL_BRAND String ,TERMINAL_MODEL String ,PRICE_LEVEL String ,NETWORK String ,SHIPPED_OS String ,WIFI String ,WIFI_HOTSPOT String ,GSM String ,WCDMA String ,TD_SCDMA String ,LTE_FDD String ,LTE_TDD String ,CDMA String ,SCREEN_SIZE String ,SCREEN_RESOLUTION String ,HOST_NAME String ,WEBSITE_NAME String ,OPERATOR String ,SRV_TYPE_NAME String ,TAG_HOST String ,CGI String ,CELL_NAME String ,COVERITY_TYPE1 String ,COVERITY_TYPE2 String ,COVERITY_TYPE3 String ,COVERITY_TYPE4 String ,COVERITY_TYPE5 String ,LATITUDE String ,LONGITUDE String ,AZIMUTH String ,TAG_CGI String ,APN String ,USER_AGENT String ,DAY String ,HOUR String ,MIN String ,IS_DEFAULT_BEAR int ,EPS_BEARER_ID String ,QCI int ,USER_FILTER String ,ANALYSIS_PERIOD String, UP_THROUGHPUT double,DOWN_THROUGHPUT double,UP_PKT_NUM double,DOWN_PKT_NUM double,APP_REQUEST_NUM double,PKT_NUM_LEN_1_64 double,PKT_NUM_LEN_64_128 double,PKT_NUM_LEN_128_256 double,PKT_NUM_LEN_256_512 double,PKT_NUM_LEN_512_768 double,PKT_NUM_LEN_768_1024 double,PKT_NUM_LEN_1024_ALL double,IP_FLOW_MARK double) STORED BY 'org.apache.carbondata.format'""").collect

    sql(s"""create table IF NOT EXISTS traffic_2g_3g_4g_hive (SOURCE_INFO String ,APP_CATEGORY_ID String ,APP_CATEGORY_NAME String ,APP_SUB_CATEGORY_ID String ,APP_SUB_CATEGORY_NAME String ,RAT_NAME String ,IMSI String ,OFFER_MSISDN String ,OFFER_ID String ,OFFER_OPTION_1 String ,OFFER_OPTION_2 String ,OFFER_OPTION_3 String ,MSISDN String ,PACKAGE_TYPE String ,PACKAGE_PRICE String ,TAG_IMSI String ,TAG_MSISDN String ,PROVINCE String ,CITY String ,AREA_CODE String ,TAC String ,IMEI String ,TERMINAL_TYPE String ,TERMINAL_BRAND String ,TERMINAL_MODEL String ,PRICE_LEVEL String ,NETWORK String ,SHIPPED_OS String ,WIFI String ,WIFI_HOTSPOT String ,GSM String ,WCDMA String ,TD_SCDMA String ,LTE_FDD String ,LTE_TDD String ,CDMA String ,SCREEN_SIZE String ,SCREEN_RESOLUTION String ,HOST_NAME String ,WEBSITE_NAME String ,OPERATOR String ,SRV_TYPE_NAME String ,TAG_HOST String ,CGI String ,CELL_NAME String ,COVERITY_TYPE1 String ,COVERITY_TYPE2 String ,COVERITY_TYPE3 String ,COVERITY_TYPE4 String ,COVERITY_TYPE5 String ,LATITUDE String ,LONGITUDE String ,AZIMUTH String ,TAG_CGI String ,APN String ,USER_AGENT String ,DAY String ,HOUR String ,`MIN` String ,IS_DEFAULT_BEAR int ,EPS_BEARER_ID String ,QCI int ,USER_FILTER String ,ANALYSIS_PERIOD String , UP_THROUGHPUT int,DOWN_THROUGHPUT int,UP_PKT_NUM int,DOWN_PKT_NUM int,APP_REQUEST_NUM int,PKT_NUM_LEN_1_64 int,PKT_NUM_LEN_64_128 int,PKT_NUM_LEN_128_256 int,PKT_NUM_LEN_256_512 int,PKT_NUM_LEN_512_768 int,PKT_NUM_LEN_768_1024 int,PKT_NUM_LEN_1024_ALL int,IP_FLOW_MARK int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','""").collect

  }


  //Sample1_CreateCube_1
  test("Sample1_CreateCube_1", Include) {
    sql(s"""drop table if exists cmb""").collect
    sql(s"""drop table if exists cmb_hive""").collect

    sql(s"""CREATE table cmb (Cust_UID String,year String, month String, companyNumber String, familyadNumber String,  companyAddress String, company String, occupation String, certicardValidTime String, race String, CerticardCity String, birthday String, VIPLevel String, ageRange String, familyaddress String, dimension16 String, SubsidaryBank String, AccountCreationTime String, dimension19 String, dimension20 String, DemandDeposits double, TimeDeposits double, financial double, TreasuryBonds double, fund double, incomeOneyear double, outcomeOneyear double, insurance double, Goldaccount double, dollarDeposits int, euroDeposits int, euroDeposits1 double, euroDeposits2 double, yenDeposits int, wonDeposits int, rupeeDeposits double, HongKongDeposits double, numberoftransactions int, measure19 double, measure20 double, measure21 int, measure22 double, measure23 double, measure24 int, measure25 double, measure26 double, measure27 int, measure28 double, measure29 int, measure30 double, measure31 double, measure32 double, measure33 double, measure34 int, measure35 double, measure36 double, measure37 int, measure38 double, measure39 double, measure40 int, measure41 double, measure42 double, measure43 int, measure44 double, measure45 int, measure46 double, measure47 int, measure48 double, measure49 int, measure50 double, measure51 int, measure52 double, measure53 int) STORED BY 'org.apache.carbondata.format' """).collect

    sql(s"""CREATE table cmb_hive (Cust_UID String,year String, month String, companyNumber String, familyadNumber String,  companyAddress String, company String, occupation String, certicardValidTime String, race String, CerticardCity String, birthday String, VIPLevel String, ageRange String, familyaddress String, dimension16 String, SubsidaryBank String, AccountCreationTime String, dimension19 String, dimension20 String, DemandDeposits double, TimeDeposits double, financial double, TreasuryBonds double, fund double, incomeOneyear double, outcomeOneyear double, insurance double, Goldaccount double, dollarDeposits int, euroDeposits int, euroDeposits1 double, euroDeposits2 double, yenDeposits int, wonDeposits int, rupeeDeposits double, HongKongDeposits double, numberoftransactions int, measure19 double, measure20 double, measure21 int, measure22 double, measure23 double, measure24 int, measure25 double, measure26 double, measure27 int, measure28 double, measure29 int, measure30 double, measure31 double, measure32 double, measure33 double, measure34 int, measure35 double, measure36 double, measure37 int, measure38 double, measure39 double, measure40 int, measure41 double, measure42 double, measure43 int, measure44 double, measure45 int, measure46 double, measure47 int, measure48 double, measure49 int, measure50 double, measure51 int, measure52 double, measure53 int)  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','""").collect

  }


  //Sample1_Query_1
  test("Sample1_Query_1", Include) {

    sql(s"""LOAD DATA INPATH '$resourcesPath/Data/cmb/data.csv'  INTO table cmb OPTIONS ('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='Cust_UID,year,month,companyAddress,companyNumber,company,occupation,certicardValidTime,race,CerticardCity,birthday,VIPLevel,ageRange,familyaddress,familyadNumber,dimension16,SubsidaryBank,AccountCreationTime,dimension19,dimension20,DemandDeposits,TimeDeposits,financial,TreasuryBonds,fund,incomeOneyear,outcomeOneyear,insurance,Goldaccount,dollarDeposits,euroDeposits,euroDeposits1,euroDeposits2,yenDeposits,wonDeposits,rupeeDeposits,HongKongDeposits,numberoftransactions,measure19,measure20,measure21,measure22,measure23,measure24,measure25,measure26,measure27,measure28,measure29,measure30,measure31,measure32,measure33,measure34,measure35,measure36,measure37,measure38,measure39,measure40,measure41,measure42,measure43,measure44,measure45,measure46,measure47,measure48,measure49,measure50,measure51,measure52,measure53')""").collect

    sql(s"""insert overwrite table cmb_hive select * from cmb""").collect


  }


  //Sample1_Query_2
  test("Sample1_Query_2", Include) {

    checkAnswer(s"""select count(*) from cmb""",
      s"""select count(*) from cmb_hive""", "QueriesNormalTestCase_Sample1_Query_2")

  }


  //Sample1_Query_3
  test("Sample1_Query_3", Include) {

    checkAnswer(s"""select COUNT(DISTINCT Cust_UID) from cmb""",
      s"""select COUNT(DISTINCT Cust_UID) from cmb_hive""", "QueriesNormalTestCase_Sample1_Query_3")

  }


  //Sample1_Query_4
  test("Sample1_Query_4", Include) {

    checkAnswer(s"""SELECT `year`, `month`, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE `year` = "2015" GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""",
      s"""SELECT `year`, `month`, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE `year` = "2015" GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""", "QueriesNormalTestCase_Sample1_Query_4")

  }


  //Sample1_Query_5
  test("Sample1_Query_5", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, occupation, VIPLevel, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE ( ( occupation = "Administrative Support") AND ( SubsidaryBank = "ABN AMRO")) AND ( SubsidaryBank = "ABN AMRO") GROUP BY SubsidaryBank, occupation, VIPLevel ORDER BY SubsidaryBank ASC, occupation ASC, VIPLevel ASC""",
      s"""SELECT SubsidaryBank, occupation, VIPLevel, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE ( ( occupation = "Administrative Support") AND ( SubsidaryBank = "ABN AMRO")) AND ( SubsidaryBank = "ABN AMRO") GROUP BY SubsidaryBank, occupation, VIPLevel ORDER BY SubsidaryBank ASC, occupation ASC, VIPLevel ASC""", "QueriesNormalTestCase_Sample1_Query_5")

  }


  //Sample1_Query_6
  test("Sample1_Query_6", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""",
      s"""SELECT SubsidaryBank, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""", "QueriesNormalTestCase_Sample1_Query_6")

  }


  //Sample1_Query_7
  test("Sample1_Query_7", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE SubsidaryBank IN ("ABN AMRO","Bank Sepah") GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""",
      s"""SELECT SubsidaryBank, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE SubsidaryBank IN ("ABN AMRO","Bank Sepah") GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""", "QueriesNormalTestCase_Sample1_Query_7")

  }


  //Sample1_Query_8
  test("Sample1_Query_8", Include) {

    checkAnswer(s"""SELECT company, CerticardCity, VIPLevel, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE ( company IN ("Agricultural Bank of China","COSCO1")) AND ( CerticardCity IN ("Beijing1","Huangyan1","Yakeshi1","Korla1")) GROUP BY company, CerticardCity, VIPLevel ORDER BY company ASC, CerticardCity ASC, VIPLevel ASC""",
      s"""SELECT company, CerticardCity, VIPLevel, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE ( company IN ("Agricultural Bank of China","COSCO1")) AND ( CerticardCity IN ("Beijing1","Huangyan1","Yakeshi1","Korla1")) GROUP BY company, CerticardCity, VIPLevel ORDER BY company ASC, CerticardCity ASC, VIPLevel ASC""", "QueriesNormalTestCase_Sample1_Query_8")

  }


  //Sample1_Query_9
  test("Sample1_Query_9", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, ageRange, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE ( ageRange IN ("(1-3)","(100-105)")) AND ( SubsidaryBank IN ("ABN AMRO","Busan Bank","Huaxia Bank")) GROUP BY SubsidaryBank, ageRange ORDER BY SubsidaryBank ASC, ageRange ASC""",
      s"""SELECT SubsidaryBank, ageRange, COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE ( ageRange IN ("(1-3)","(100-105)")) AND ( SubsidaryBank IN ("ABN AMRO","Busan Bank","Huaxia Bank")) GROUP BY SubsidaryBank, ageRange ORDER BY SubsidaryBank ASC, ageRange ASC""", "QueriesNormalTestCase_Sample1_Query_9")

  }


  //Sample1_Query_10
  // TODO Need to handle the decimal mismatch
  ignore("Sample1_Query_10", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, SUM(incomeOneyear) AS Sum_incomeOneyear, SUM(numberoftransactions) AS Sum_numberoftransactions FROM (select * from cmb) SUB_QRY WHERE SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank","Real-Estate Bank") GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""",
      s"""SELECT SubsidaryBank, SUM(incomeOneyear) AS Sum_incomeOneyear, SUM(numberoftransactions) AS Sum_numberoftransactions FROM (select * from cmb_hive) SUB_QRY WHERE SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank","Real-Estate Bank") GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""", "QueriesNormalTestCase_Sample1_Query_10")

  }


  //Sample1_Query_11
  test("Sample1_Query_11", Include) {

    checkAnswer(s"""SELECT `year`, `month`, SUM(DemandDeposits) AS Sum_DemandDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb) SUB_QRY WHERE ( SubsidaryBank = "CMB Financial Leasing Ltd") AND ( Cust_UID = "CMB0000000000000000000000") GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""",
      s"""SELECT `year`, `month`, SUM(DemandDeposits) AS Sum_DemandDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb_hive) SUB_QRY WHERE ( SubsidaryBank = "CMB Financial Leasing Ltd") AND ( Cust_UID = "CMB0000000000000000000000") GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""", "QueriesNormalTestCase_Sample1_Query_11")

  }


  //Sample1_Query_12
  // TODO Need to handle the decimal mismatch
  ignore("Sample1_Query_12", Include) {

    checkAnswer(s"""SELECT `year`, `month`, SUM(yenDeposits) AS Sum_yenDeposits, SUM(HongKongDeposits) AS Sum_HongKongDeposits, SUM(dollarDeposits) AS Sum_dollarDeposits, SUM(euroDeposits) AS Sum_euroDeposits FROM (select * from cmb) SUB_QRY WHERE ( SubsidaryBank = "Credit Suisse") AND ( `month` IN ("1","2","3")) GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""",
      s"""SELECT `year`, `month`, SUM(yenDeposits) AS Sum_yenDeposits, SUM(HongKongDeposits) AS Sum_HongKongDeposits, SUM(dollarDeposits) AS Sum_dollarDeposits, SUM(euroDeposits) AS Sum_euroDeposits FROM (select * from cmb_hive) SUB_QRY WHERE ( SubsidaryBank = "Credit Suisse") AND ( `month` IN ("1","2","3")) GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""", "QueriesNormalTestCase_Sample1_Query_12")

  }


  //Sample1_Query_13
  test("Sample1_Query_13", Include) {

    checkAnswer(s"""SELECT Cust_UID, `month`, `year`, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb) SUB_QRY WHERE Cust_UID IN ("CMB0000000000000000000119","CMB0000000000000000000308") and month="1" GROUP BY Cust_UID, `month`, `year` ORDER BY Cust_UID ASC, `month` ASC, `year` ASC""",
      s"""SELECT Cust_UID, `month`, `year`, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb_hive) SUB_QRY WHERE Cust_UID IN ("CMB0000000000000000000119","CMB0000000000000000000308") and month="1" GROUP BY Cust_UID, `month`, `year` ORDER BY Cust_UID ASC, `month` ASC, `year` ASC""", "QueriesNormalTestCase_Sample1_Query_13")

  }


  //Sample1_Query_14
  test("Sample1_Query_14", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb) SUB_QRY WHERE SubsidaryBank = "Daegu Bank" GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""",
      s"""SELECT SubsidaryBank, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE SubsidaryBank = "Daegu Bank" GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""", "QueriesNormalTestCase_Sample1_Query_14")

  }


  //Sample1_Query_15
  test("Sample1_Query_15", Include) {

    checkAnswer(s"""SELECT COUNT(Cust_UID) AS Count_Cust_UID, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb) SUB_QRY WHERE ( SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank","Minsheng Bank - First private bank in China")) AND ( dollarDeposits > 0)""",
      s"""SELECT COUNT(Cust_UID) AS Count_Cust_UID, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb_hive) SUB_QRY WHERE ( SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank","Minsheng Bank - First private bank in China")) AND ( dollarDeposits > 0)""", "QueriesNormalTestCase_Sample1_Query_15")

  }


  //Sample1_Query_16
  test("Sample1_Query_16", Include) {

    checkAnswer(s"""SELECT SubsidaryBank, SUM(numberoftransactions) AS Sum_numberoftransactions FROM (select * from cmb) SUB_QRY WHERE SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank") and month="1" GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""",
      s"""SELECT SubsidaryBank, SUM(numberoftransactions) AS Sum_numberoftransactions FROM (select * from cmb_hive) SUB_QRY WHERE SubsidaryBank IN ("Bank Bumiputera Indonesia","Daegu Bank") and month="1" GROUP BY SubsidaryBank ORDER BY SubsidaryBank ASC""", "QueriesNormalTestCase_Sample1_Query_16")

  }


  //Sample1_Query_17
  test("Sample1_Query_17", Include) {

    checkAnswer(s"""SELECT COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb) SUB_QRY WHERE ( SubsidaryBank = "ABC") AND ( numberoftransactions > 90.0)""",
      s"""SELECT COUNT(Cust_UID) AS Count_Cust_UID FROM (select * from cmb_hive) SUB_QRY WHERE ( SubsidaryBank = "ABC") AND ( numberoftransactions > 90.0)""", "QueriesNormalTestCase_Sample1_Query_17")

  }


  //Sample1_Query_18
  test("Sample1_Query_18", Include) {

    checkAnswer(s"""SELECT VIPLevel, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb) SUB_QRY GROUP BY VIPLevel ORDER BY VIPLevel ASC""",
      s"""SELECT VIPLevel, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb_hive) SUB_QRY GROUP BY VIPLevel ORDER BY VIPLevel ASC""", "QueriesNormalTestCase_Sample1_Query_18")

  }


  //Sample1_Query_19
  test("Sample1_Query_19", Include) {

    checkAnswer(s"""SELECT CerticardCity, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb) SUB_QRY GROUP BY CerticardCity ORDER BY CerticardCity ASC""",
      s"""SELECT CerticardCity, COUNT(DISTINCT Cust_UID) AS DistinctCount_Cust_UID FROM (select * from cmb_hive) SUB_QRY GROUP BY CerticardCity ORDER BY CerticardCity ASC""", "QueriesNormalTestCase_Sample1_Query_19")

  }


  //Sample1_Query_20
  test("Sample1_Query_20", Include) {

    checkAnswer(s"""SELECT VIPLevel, SUM(yenDeposits) AS Sum_yenDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb) SUB_QRY GROUP BY VIPLevel ORDER BY VIPLevel ASC""",
      s"""SELECT VIPLevel, SUM(yenDeposits) AS Sum_yenDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb_hive) SUB_QRY GROUP BY VIPLevel ORDER BY VIPLevel ASC""", "QueriesNormalTestCase_Sample1_Query_20")

  }


  //Sample1_Query_21
  test("Sample1_Query_21", Include) {

    checkAnswer(s"""SELECT CerticardCity, SUM(yenDeposits) AS Sum_yenDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb) SUB_QRY GROUP BY CerticardCity ORDER BY CerticardCity ASC""",
      s"""SELECT CerticardCity, SUM(yenDeposits) AS Sum_yenDeposits, SUM(numberoftransactions) AS Sum_numberoftransactions, SUM(dollarDeposits) AS Sum_dollarDeposits FROM (select * from cmb_hive) SUB_QRY GROUP BY CerticardCity ORDER BY CerticardCity ASC""", "QueriesNormalTestCase_Sample1_Query_21")

  }


  //Sample1_Query_22
  test("Sample1_Query_22", Include) {

    checkAnswer(s"""SELECT `year`, `month`, COUNT(Cust_UID) AS Count_Cust_UID, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb) SUB_QRY WHERE ( `month` = "1") AND ( numberoftransactions > 90.0) GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""",
      s"""SELECT `year`, `month`, COUNT(Cust_UID) AS Count_Cust_UID, SUM(yenDeposits) AS Sum_yenDeposits FROM (select * from cmb_hive) SUB_QRY WHERE ( `month` = "1") AND ( numberoftransactions > 90.0) GROUP BY `year`, `month` ORDER BY `year` ASC, `month` ASC""", "QueriesNormalTestCase_Sample1_Query_22")

  }

  override def afterAll {
  sql("drop table if exists oscon_carbon")
  sql("drop table if exists oscon_carbon_hive")
  sql("drop table if exists viptable")
  sql("drop table if exists viptable_hive")
  sql("drop table if exists oscon_carbon_vip")
  sql("drop table if exists oscon_carbon_vip_hive")
  sql("drop table if exists cmb")
  sql("drop table if exists cmb_hive")
  }
}