| ==== |
| ---- QUERY: TPCDS-Q61 |
| -- RESULT MISMATCH FROM ORIGINAL in DECIMAL value |
| -- FIXED. CAST RESULT QUOTIENT TO DECIMAL(15, 4), TAKE ACTUAL RESULT AS EXPECTED |
| select promotions,total,cast(promotions*100.00/total as decimal(15,4)) |
| from |
| (select sum(ss_ext_sales_price) promotions |
| from store_sales |
| ,store |
| ,promotion |
| ,date_dim |
| ,customer |
| ,customer_address |
| ,item |
| where ss_sold_date_sk = d_date_sk |
| and ss_store_sk = s_store_sk |
| and ss_promo_sk = p_promo_sk |
| and ss_customer_sk= c_customer_sk |
| and ca_address_sk = c_current_addr_sk |
| and ss_item_sk = i_item_sk |
| and ca_gmt_offset = -5 |
| and i_category = 'Jewelry' |
| and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') |
| and s_gmt_offset = -5 |
| and d_year = 1998 |
| and d_moy = 1) promotional_sales, |
| (select sum(ss_ext_sales_price) total |
| from store_sales |
| ,store |
| ,date_dim |
| ,customer |
| ,customer_address |
| ,item |
| where ss_sold_date_sk = d_date_sk |
| and ss_store_sk = s_store_sk |
| and ss_customer_sk= c_customer_sk |
| and ca_address_sk = c_current_addr_sk |
| and ss_item_sk = i_item_sk |
| and ca_gmt_offset = -5 |
| and i_category = 'Jewelry' |
| and s_gmt_offset = -5 |
| and d_year = 1998 |
| and d_moy = 1) all_sales |
| order by promotions, total |
| ---- RESULTS |
| 969445.96,1772143.56,54.7047 |
| ---- TYPES |
| DECIMAL, DECIMAL, DECIMAL |
| ==== |