|
cut off string from union with sql-mode oracle
set sql-mode=oracle
and execute the query
set infinidb_use_decimal_scale=1;
|
set infinidb_decimal_scale=9 ;
|
|
select channel, item, TRIM(TRAILING '.' FROM TRIM(return_ratio))+0 as return_ratio, return_rank, currency_rank from
|
(select
|
'web' as channel
|
,web.item
|
,web.return_ratio
|
,web.return_rank
|
,web.currency_rank
|
from (
|
select
|
item
|
,return_ratio
|
,currency_ratio
|
,rank() over (order by return_ratio) as return_rank
|
,rank() over (order by currency_ratio) as currency_rank
|
from
|
( select ws.ws_item_sk as item
|
,(cast(sum(coalesce(wr.wr_return_quantity,0)) as decimal(15,4))/
|
cast(sum(coalesce(ws.ws_quantity,0)) as decimal(15,4) )) as return_ratio
|
,(cast(sum(coalesce(wr.wr_return_amt,0)) as decimal(15,4))/
|
cast(sum(coalesce(ws.ws_net_paid,0)) as decimal(15,4) )) as currency_ratio
|
from
|
web_sales ws left outer join web_returns wr
|
on (ws.ws_order_number = wr.wr_order_number and
|
ws.ws_item_sk = wr.wr_item_sk)
|
,date_dim
|
where
|
wr.wr_return_amt > 10000
|
and ws.ws_net_profit > 1
|
and ws.ws_net_paid > 0
|
and ws.ws_quantity > 0
|
and ws_sold_date_sk = d_date_sk
|
and d_year = 2001
|
and d_moy = 12
|
group by ws.ws_item_sk
|
) in_web
|
) web
|
where
|
(
|
web.return_rank <= 10
|
or
|
web.currency_rank <= 10
|
)
|
union
|
select
|
'catalog' as channel
|
,catalog.item
|
,catalog.return_ratio
|
,catalog.return_rank
|
,catalog.currency_rank
|
from (
|
select
|
item
|
,return_ratio
|
,currency_ratio
|
,rank() over (order by return_ratio) as return_rank
|
,rank() over (order by currency_ratio) as currency_rank
|
from
|
( select
|
cs.cs_item_sk as item
|
,(cast(sum(coalesce(cr.cr_return_quantity,0)) as decimal(15,4))/
|
cast(sum(coalesce(cs.cs_quantity,0)) as decimal(15,4) )) as return_ratio
|
,(cast(sum(coalesce(cr.cr_return_amount,0)) as decimal(15,4))/
|
cast(sum(coalesce(cs.cs_net_paid,0)) as decimal(15,4) )) as currency_ratio
|
from
|
catalog_sales cs left outer join catalog_returns cr
|
on (cs.cs_order_number = cr.cr_order_number and
|
cs.cs_item_sk = cr.cr_item_sk)
|
,date_dim
|
where
|
cr.cr_return_amount > 10000
|
and cs.cs_net_profit > 1
|
and cs.cs_net_paid > 0
|
and cs.cs_quantity > 0
|
and cs_sold_date_sk = d_date_sk
|
and d_year = 2001
|
and d_moy = 12
|
group by cs.cs_item_sk
|
) in_cat
|
) catalog
|
where
|
(
|
catalog.return_rank <= 10
|
or
|
catalog.currency_rank <=10
|
)
|
union
|
select
|
'store' as channel
|
,store.item
|
,store.return_ratio
|
,store.return_rank
|
,store.currency_rank
|
from (
|
select
|
item
|
,return_ratio
|
,currency_ratio
|
,rank() over (order by return_ratio) as return_rank
|
,rank() over (order by currency_ratio) as currency_rank
|
from
|
( select sts.ss_item_sk as item
|
,(cast(sum(coalesce(sr.sr_return_quantity,0)) as decimal(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) as decimal(15,4) )) as return_ratio
|
,(cast(sum(coalesce(sr.sr_return_amt,0)) as decimal(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) as decimal(15,4) )) as currency_ratio
|
from
|
store_sales sts left outer join store_returns sr
|
on (sts.ss_ticket_number = sr.sr_ticket_number and sts.ss_item_sk = sr.sr_item_sk)
|
,date_dim
|
where
|
sr.sr_return_amt > 10000
|
and sts.ss_net_profit > 1
|
and sts.ss_net_paid > 0
|
and sts.ss_quantity > 0
|
and ss_sold_date_sk = d_date_sk
|
and d_year = 2001
|
and d_moy = 12
|
group by sts.ss_item_sk
|
) in_store
|
) store
|
where (
|
store.return_rank <= 10
|
or
|
store.currency_rank <= 10
|
)
|
)f
|
order by 1,4,5,2
|
limit 100;
|
|
MariaDB [(none)]> select @@sql_mode ;
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| @@sql_mode |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
query returns cut off string in the channel columns
channel item return_ratio return_rank currency_rank
|
cat 17543 0.571428571 1 1
|
cat 14513 0.635416667 2 2
|
cat 12577 0.655913978 3 3
|
cat 3411 0.71641791 4 4
|
cat 361 0.746478873 5 5
|
cat 8189 0.746987952 6 6
|
cat 8929 0.7625 7 7
|
cat 14869 0.77173913 8 8
|
cat 9295 0.778947368 9 9
|
cat 16215 0.790697674 10 10
|
sto 9471 0.775 1 1
|
sto 9797 0.8 2 2
|
sto 12641 0.816091954 3 3
|
sto 15839 0.816326531 4 4
|
sto 1171 0.824175824 5 5
|
sto 11589 0.826530612 6 6
|
sto 6661 0.922077922 7 7
|
sto 13013 0.942028986 8 8
|
sto 14925 0.964705882 9 9
|
sto 4063 1 10 10
|
sto 9029 1 10 10
|
web 7539 0.59 1 1
|
web 3337 0.626506024 2 2
|
web 15597 0.661971831 3 3
|
web 2915 0.698630137 4 4
|
web 11933 0.717171717 5 5
|
web 3305 0.7375 6 16
|
web 483 0.8 7 6
|
web 85 0.857142857 8 7
|
web 97 0.903614458 9 8
|
web 117 0.925 10 9
|
web 5299 0.927083333 11 10
|
|
when set infinidb_vtable_mode = 0 with sql-mode oracle
or with infinidb_vtable_mode = 1 and w/o oracle mode
the longest string is displayed
channel item return_ratio return_rank currency_rank
|
catalog 17543 0.57142857 1 1
|
catalog 14513 0.63541667 2 2
|
catalog 12577 0.65591398 3 3
|
catalog 3411 0.71641791 4 4
|
catalog 361 0.74647887 5 5
|
catalog 8189 0.74698795 6 6
|
catalog 8929 0.7625 7 7
|
catalog 14869 0.77173913 8 8
|
catalog 9295 0.77894737 9 9
|
catalog 16215 0.79069767 10 10
|
store 9471 0.775 1 1
|
store 9797 0.8 2 2
|
store 12641 0.81609195 3 3
|
store 15839 0.81632653 4 4
|
store 1171 0.82417582 5 5
|
store 11589 0.82653061 6 6
|
store 6661 0.92207792 7 7
|
store 13013 0.94202899 8 8
|
store 14925 0.96470588 9 9
|
store 4063 1 10 10
|
store 9029 1 10 10
|
web 7539 0.59 1 1
|
web 3337 0.62650602 2 2
|
web 15597 0.66197183 3 3
|
web 2915 0.69863014 4 4
|
web 11933 0.71717172 5 5
|
web 3305 0.7375 6 16
|
web 483 0.8 7 6
|
web 85 0.85714286 8 7
|
web 97 0.90361446 9 8
|
web 117 0.925 10 9
|
web 5299 0.92708333 11 10
|
|
|