修改完整版的sql存储（积分兑换脚本），改写出可执行的shell update "rpt_wjq_jf_value_dhfz_list" as "a" set "a"."club_level" = case when "b"."star_level" = '3100' then '1星' when "b"."star_level" = '3200' then '2星' when "b"."star_level" = '3300' then '3星' when "b"."star_level" = '3400' then '4星' when "b"."star_level" = '3500' then '5星' when "b"."star_level" = '3600' then '6星' when "b"."star_level" = '3700' then '7星' else '0星' end from "rptdev"."rpt_comm_cm_serv_cdma" as "b" where "a"."cust_id" = "b"."cust_id"; commit work;

以下是修改后的可执行的 shell 脚本：

#!/bin/bash

# 更新会员等级
isql -Usa -Ppassword -SSYBASE -w 999 -b -o /dev/null <<EOF
update rpt_wjq_jf_value_dhfz_list as a
set a.club_level = case
when b.star_level = '3100' then '1星'
when b.star_level = '3200' then '2星'
when b.star_level = '3300' then '3星'
when b.star_level = '3400' then '4星'
when b.star_level = '3500' then '5星'
when b.star_level = '3600' then '6星'
when b.star_level = '3700' then '7星'
else '0星'
end
from rptdev.rpt_comm_cm_serv_cdma as b
where a.cust_id = b.cust_id;
commit work;
EOF

echo "脚本执行完成！"

相关问题

修改完整版的sql存储（积分兑换脚本），改写出可执行的shell set @last_year_last_month = cast("dateformat"(convert(integer,"dateformat"("dateadd"("yy",-1,@data_date),'yyyy1231')),'yyyymm') as integer); --上年尾月 set temporary option "conversion_error" = 'OFF'; set temporary option "Query_Temp_Space_Limit" = 0; --drop table if exists rpt_wjq_jf_value_dhfz_list;commit; --插入清单层 truncate table "rptdev"."rpt_wjq_jf_value_dhfz_list"; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '清空底层清单表：' || @@ROWCOUNT || ' 条' type info to client; insert into "rpt_wjq_jf_value_dhfz_list"( "cust_id","cust_name","price","gift_code","gift_name" ) select "cust_id","cust_name","price","gift_code","gift_name" --into rpt_wjq_jf_value_dhfz_list from "zwfxdev"."TB_COMM_CM_INT_USE_GIF_ALL" where convert(date,"change_date") >= @month_first_date and convert(date,"change_date") <= @stat_date; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '插入底层清单表：' || @@ROWCOUNT || ' 条' type info to client; --alter table rpt_wjq_jf_value_dhfz_list add (acc_nbr varchar(21),cust_nbr varchar(30),subst_id numeric(18));commit; --更新号码、局向 update "rpt_wjq_jf_value_dhfz_list" as "a" set "a"."acc_nbr" = "b"."acc_nbr", "a"."cust_nbr" = "b"."cust_nbr", "a"."subst_id" = "b"."subst_id" from "rptdev"."rpt_comm_cm_serv_cdma" as "b" where "a"."cust_id" = "b"."cust_id"; commit work;

以下是修改后的可执行的 shell 脚本：

#!/bin/bash

# 设置变量
last_year_last_month=$(echo 'select cast(dateformat(convert(integer,dateformat(dateadd(yy,-1,getdate()),"yyyy1231")),"yyyymm") as integer)' | isql -Usa -Ppassword -SSYBASE -w 999 -s "|" | tail -n1 | tr -d " ")
export last_year_last_month

# 关闭转换错误和查询临时空间限制
isql -Usa -Ppassword -SSYBASE -w 999 -b -o /dev/null <<EOF
set temporary option "conversion_error" = 'OFF';
set temporary option "Query_Temp_Space_Limit" = 0;
EOF

# 清空底层清单表
isql -Usa -Ppassword -SSYBASE -w 999 -b -o /dev/null <<EOF
truncate table rpt_wjq_jf_value_dhfz_list;
commit work;
message "DATEFORMAT"(getdate(),'YYYYMMDD HH:NN:SS') || '清空底层清单表：' || @@ROWCOUNT || ' 条' type info to client;
EOF

# 插入底层清单表
isql -Usa -Ppassword -SSYBASE -w 999 -b -o /dev/null <<EOF
insert into rpt_wjq_jf_value_dhfz_list(cust_id,cust_name,price,gift_code,gift_name)
select cust_id,cust_name,price,gift_code,gift_name
from zwfxdev.TB_COMM_CM_INT_USE_GIF_ALL
where convert(date,change_date) >= @month_first_date and convert(date,change_date) <= @stat_date;
commit work;
message "DATEFORMAT"(getdate(),'YYYYMMDD HH:NN:SS') || '插入底层清单表：' || @@ROWCOUNT || ' 条' type info to client;
EOF

# 更新号码、局向
isql -Usa -Ppassword -SSYBASE -w 999 -b -o /dev/null <<EOF
update rpt_wjq_jf_value_dhfz_list as a
set a.acc_nbr = b.acc_nbr,
a.cust_nbr = b.cust_nbr,
a.subst_id = b.subst_id
from rptdev.rpt_comm_cm_serv_cdma as b
where a.cust_id = b.cust_id;
commit work;
EOF

echo "脚本执行完成！"

修改完整版的sql存储（积分兑换脚本），改写出可执行的hive truncate table "rptdev"."rpt_wjq_jf_value_dhfz_list"; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '清空底层清单表：' || @@ROWCOUNT || ' 条' type info to client; insert into "rpt_wjq_jf_value_dhfz_list"( "cust_id","cust_name","price","gift_code","gift_name" ) select "cust_id","cust_name","price","gift_code","gift_name" --into rpt_wjq_jf_value_dhfz_list from "zwfxdev"."TB_COMM_CM_INT_USE_GIF_ALL" where convert(date,"change_date") >= @month_first_date and convert(date,"change_date") <= @stat_date; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '插入底层清单表：' || @@ROWCOUNT || ' 条' type info to client; --alter table rpt_wjq_jf_value_dhfz_list add (acc_nbr varchar(21),cust_nbr varchar(30),subst_id numeric(18));commit; --更新号码、局向 update "rpt_wjq_jf_value_dhfz_list" as "a" set "a"."acc_nbr" = "b"."acc_nbr", "a"."cust_nbr" = "b"."cust_nbr", "a"."subst_id" = "b"."subst_id" from "rptdev"."rpt_comm_cm_serv_cdma" as "b" where "a"."cust_id" = "b"."cust_id"; commit work;

以下是修改后的可执行的Hive代码：

-- 清空底层清单表
truncate table rptdev.rpt_wjq_jf_value_dhfz_list;
-- 插入底层清单表
insert into rptdev.rpt_wjq_jf_value_dhfz_list(cust_id, cust_name, price, gift_code, gift_name)
select cust_id, cust_name, price, gift_code, gift_name
from zwfxdev.TB_COMM_CM_INT_USE_GIF_ALL
where cast(change_date as date) >= '${month_first_date}' and cast(change_date as date) <= '${stat_date}';

-- 更新号码、局向
add columns if not exists (acc_nbr string, cust_nbr string, subst_id decimal(18));
update rptdev.rpt_wjq_jf_value_dhfz_list as a
set acc_nbr = b.acc_nbr, cust_nbr = b.cust_nbr, subst_id = b.subst_id
from rptdev.rpt_comm_cm_serv_cdma as b
where a.cust_id = b.cust_id;