http://git-wip-us.apache.org/repos/asf/carbondata/blob/14624953/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesNormalTestCase.scala
----------------------------------------------------------------------
diff --git
a/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesNormalTestCase.scala
b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesNormalTestCase.scala
new file mode 100644
index 0000000..4ce4012
--- /dev/null
+++
b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesNormalTestCase.scala
@@ -0,0 +1,504 @@
+
+/*
+ * 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_VERSIO
N 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_CATEG
ORY_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,LA
TEST_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,S
TR_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_DE
MO_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'
TBLPROPERTIES('DICTIONARY_EXCLUDE'='PROD_UNQ_MDL_ID,CUST_NICK_NAME,CUST_LOGIN,CUST_EMAIL_ADDR,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_BAR_CODE,TRACKING_NO,STR_ORDER_NO')""").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,C
UST_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_CH
ECK_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_C
NT,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_VE
RSION 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_C
ATEGORY_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
Strin
g,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 i
nt,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_RTRNIN
G_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'
TBLPROPERTIES('DICTIONARY_EXCLUDE'='PROD_UNQ_MDL_ID,CUST_NICK_NAME,CUST_LOGIN,CUST_EMAIL_ADDR,PROD_UNQ_DEVICE_ADDR,PROD_UQ_UUID,PROD_BAR_CODE,TRACKING_NO,STR_ORDER_NO')""").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_COUNT
RY,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,ACTIV
E_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_AR
EA_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
+
+ }
+
+
+ //SmartPCC_CreateCube_TC_001
+ test("SmartPCC_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 Str
ing ,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
+
+ }
+
+
+ //CMBC_CreateCube_1
+ test("CMBC_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, me
asure31 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' TBLPROPERTIES
('DICTIONARY_INCLUDE'='dollarDeposits,euroDeposits,yenDeposits,wonDeposits,numberoftransactions,measure21,measure24,measure27,measure29,measure34,measure37,measure40,measure43,measure45,measure47,measure49,measure51,measure53')""").collect
+
+ sql(s"""CREATE table cmb_hive (Cust_UID String,year String, month String,
companyAddress String,companyNumber String,company String,occupation String,
certicardValidTime String,race String, CerticardCity String,birthday String,
VIPLevel String, ageRange String, familyaddress String,familyadNumber 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, meas
ure31 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
+
+ }
+
+
+ //CMBC_Query_1
+ test("CMBC_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')""").col
lect
+
+ sql(s"""LOAD DATA INPATH '$resourcesPath/Data/cmb/data.csv' INTO table
cmb_hive """).collect
+
+
+ }
+
+
+ //CMBC_Query_2
+ test("CMBC_Query_2", Include) {
+
+ checkAnswer(s"""select count(*) from cmb""",
+ s"""select count(*) from cmb_hive""",
"QueriesNormalTestCase_CMBC_Query_2")
+
+ }
+
+
+ //CMBC_Query_3
+ test("CMBC_Query_3", Include) {
+
+ checkAnswer(s"""select COUNT(DISTINCT Cust_UID) from cmb""",
+ s"""select COUNT(DISTINCT Cust_UID) from cmb_hive""",
"QueriesNormalTestCase_CMBC_Query_3")
+
+ }
+
+
+ //CMBC_Query_4
+ test("CMBC_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_CMBC_Query_4")
+
+ }
+
+
+ //CMBC_Query_5
+ test("CMBC_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_CMBC_Query_5")
+
+ }
+
+
+ //CMBC_Query_6
+ test("CMBC_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_CMBC_Query_6")
+
+ }
+
+
+ //CMBC_Query_7
+ test("CMBC_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_CMBC_Query_7")
+
+ }
+
+
+ //CMBC_Query_8
+ test("CMBC_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_CMBC_Query_8")
+
+ }
+
+
+ //CMBC_Query_9
+ test("CMBC_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_CMBC_Query_9")
+
+ }
+
+
+ //CMBC_Query_10
+ test("CMBC_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_CMBC_Query_10")
+
+ }
+
+
+ //CMBC_Query_11
+ test("CMBC_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_CMBC_Query_11")
+
+ }
+
+
+ //CMBC_Query_12
+ test("CMBC_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_CMBC_Query_12")
+
+ }
+
+
+ //CMBC_Query_13
+ test("CMBC_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_CMBC_Query_13")
+
+ }
+
+
+ //CMBC_Query_14
+ test("CMBC_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_CMBC_Query_14")
+
+ }
+
+
+ //CMBC_Query_15
+ test("CMBC_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_CMBC_Query_15")
+
+ }
+
+
+ //CMBC_Query_16
+ test("CMBC_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_CMBC_Query_16")
+
+ }
+
+
+ //CMBC_Query_17
+ test("CMBC_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_CMBC_Query_17")
+
+ }
+
+
+ //CMBC_Query_18
+ test("CMBC_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_CMBC_Query_18")
+
+ }
+
+
+ //CMBC_Query_19
+ test("CMBC_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_CMBC_Query_19")
+
+ }
+
+
+ //CMBC_Query_20
+ test("CMBC_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_CMBC_Query_20")
+
+ }
+
+
+ //CMBC_Query_21
+ test("CMBC_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_CMBC_Query_21")
+
+ }
+
+
+ //CMBC_Query_22
+ test("CMBC_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_CMBC_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")
+ }
+}
\ No newline at end of file
