Execute Procedure

Get Help
1

Hi.
I need some help. I try to apply filters on my procedure, but it does not work correctly. Metabase does not correctly detect the tables to apply those filters in my procedure.

2

Hi @jireascos
Without seeing the query, it’s difficult to help you.
Also, which version of Metabase? And which database are you querying?

3

i met the same problem , it can’t work correctly with procedure. here is my sql query：execute dbo.a_stockage2 ‘2019’,‘09’ used datebase: sqlserver2005

procedure code:
ALTER PROCEDURE [dbo].[a_stockage2] (@pYear VARCHAR(4), @pMonth VARCHAR(2)) --库龄数据处理1
AS

BEGIN

if exists (select * from sysobjects where name=‘a_stockage_tb’)
DROP TABLE a_stockage_tb;
if exists (select * from sysobjects where name=‘a_stockage_tblj’)
drop TABLE a_stockage_tblj;
if exists (select * from sysobjects where name=‘a_stockage_tbcpt’)
drop table a_stockage_tbcpt;
–1次
WITH a AS
(–当前库存数量清单
SELECT dbo.a_get_LH_type_2 (料号) 材料分类
, 年
, 月
, 料号
, 料名
, 型号规格
, 单位
, 批号
, 料号 + ‘-’ + 批号 料批
, 期末数量
FROM a_tableByMonth
WHERE 年 = @pYear AND 月 = @pMonth AND 期末数量 <> 0 AND dbo.a_get_LH_type_2 (料号) <> ‘杂项’)

, a1 AS
(SELECT 材料分类
     , 年
     , 月
     , 料号
     , 料名
     , 型号规格
     , 单位
     , 批号
     , 料批
     , sum (期末数量) 期末数量
 FROM a
 GROUP BY 材料分类, 年, 月, 料号, 料名, 型号规格, 单位, 批号, 料批)

, b AS
(--所有筛选日期之内的异动业务单据
SELECT * --,料号+'-'+批号 料批
FROM a_allBill
WHERE 审核状态 = '审核' AND (substring (单据日期, 1, 4) < @pYear OR (substring (单据日期, 1, 4) = @pYear AND substring (单据日期, 6, 2) <= @pmonth)) AND 单身备注 NOT LIKE '%外购入库成本核算自动生成%')

,qc AS (
SELECT c.FFullNumber 料号

,A.FBatchNo 批号
,‘采购入库’ 单据类型
,‘2017-11-30’ 单据日期
,‘期初开账入库单’ 单据号
,‘1’ 项次
,fbegqty 实收数量
FROM ICInvInitial a
left join a_baseinfo c on a.fitemid=c.fitemid
WHERE FPeriod=‘0’
)

, c AS
(--所有产品入库，采购入库，销售出库红字的清单
SELECT 料号
     , 批号
     , 单据类型
     , 单据日期
     , 单据号
     , 项次
     , CASE WHEN 实收数量>=0 THEN 实收数量

ELSE 实收数量*-1 END 实收数量
FROM b
WHERE (单据类型 = ‘产品入库’ and 红蓝字=‘蓝字’)
OR (单据类型 = ‘采购入库’ and 红蓝字=‘蓝字’)
OR (单据类型 = ‘销售出库’ AND 红蓝字 = ‘红字’)
OR (单据类型=‘其他入库’ and 红蓝字=‘蓝字’)
or (单据类型 =‘盘盈入库’ and 红蓝字=‘蓝字’)
UNION ALL
SELECT * FROM qc
)

, cte AS
(SELECT 料号
     , 批号
     , 单据类型
     , 单据日期
     , 单据号
     , 项次
     , 实收数量
     , row_number () OVER (PARTITION BY 料号, 批号 ORDER BY 单据日期 desc) AS rnm
 FROM c)

SELECT * INTO a_stockage_tb FROM cte ORDER BY 料号,批号,单据日期 desc;

–2次
WITH d AS( --增加累加数
SELECT 料号,批号,单据日期,单据号,项次,实收数量,
(SELECT sum(实收数量)
FROM a_stockage_tb t1
WHERE (t1.rnm<=t2.rnm AND t1.料号=t2.料号 AND t1.批号=t2.批号)
) AS 累计库存数量
FROM a_stockage_tb t2
)

SELECT *
INTO a_stockage_tblj FROM d;

--3次
WITH a AS
(--当前库存数量清单
SELECT dbo.a_get_LH_type_2 (料号) 材料分类
    , 年
    , 月
    , 料号
    , 料名
    , 型号规格
    , 单位
    , 批号
    , 料号 + '-' + 批号 料批
    , 期末数量
FROM a_tableByMonth
WHERE 年 = @pYear AND 月 = @pMonth AND 期末数量 <> 0 AND dbo.a_get_LH_type_2 (料号) <> '杂项')

, a1 AS
(SELECT 材料分类
     , 年
     , 月
     , 料号
     , 料名
     , 型号规格
     , 单位
     , 批号
     , 料批
     , sum (期末数量) 期末数量
 FROM a
 GROUP BY 材料分类, 年, 月, 料号, 料名, 型号规格, 单位, 批号, 料批),

a2 as(
SELECT t1. *
, t2.单据日期
, t2.单据号
, t2.项次
, t2.实收数量
, t2.累计库存数量
, t2.累计库存数量 - t1.期末数量 计算值
–INTO a_stockage_tbcpt
FROM a1 t1
LEFT JOIN a_stockage_tblj t2 ON t1.料号 = t2.料号 AND t1.批号 = t2.批号
–ORDER BY t2.料号, t2.批号, t2.单据日期 DESC
),

a3 as(
SELECT tb1.*, row_number() over (PARTITION by 料批 order by 料批, 单据日期 desc) rn,
ROW_NUMBER() over (order by 料批,单据日期 desc) as id
–into a_stockage_tbcpt
from a2 tb1
),

a4 as(
SELECT t1.*,
(
SELECT
计算值
from a3 t2 where t2.id = t1.id-1
) as diff_num
from a3 t1
),

a5 as (
SELECT a.*,
case when rn=1 or (rn>1 and diff_num<0) then ‘Y’
else ‘N’ end bz
from a4 a
),

a6 as (
SELECT *,
case when bz=‘Y’ and 计算值<=0 then 实收数量
when bz=‘Y’ and 计算值>0 then 实收数量-计算值
when 单据号 is null then 期末数量
else 0 end 单据结余数量
from a5)

select * into a_stockage_tbcpt from a6;

–返回数据
with x as (
SELECT *,
case when 单据日期 is null then ‘2017-12-31’
else 单据日期 end 单据日期m
from a_stockage_tbcpt where bz=‘Y’
)

select 材料分类,年,月,料号,料名,型号规格,批号,料批,rn,期末数量,单据号,单据日期m,实收数量,累计库存数量,单据结余数量
from x;
End
GO

4

Hi @bisu
Which version of Metabase?
I still do not understand what the problem is, so can you please explain the problem?

5

1.Metabase 0.33.4
2.when i executed sql query " execute dbo.a_stockage2 ‘2019’,‘09’ " with sql server management studio , the return data is correct. but when i executed sql query " execute dbo.a_stockage2 ‘2019’,‘09’ " with metabase sql query , the result is wrong。 i tried to delete a table by Execute Procedure in metabase. but the code was not work. so i think metabse cant support Execute Procedure that include update insert or delete opertaion.

6

@bisu
What do you mean by “the result is wrong” ?
Metabase does not allow insert/update/delete operations, but I don’t think Metabase can see inside the procedure, since it’s executed on the server, so that shouldn’t be a problem.
What do you see the Metabase log or SQL log?