时间戳

首页 > 解决方案 > 如何在 Teradata 中运行不带参数的程序

sql - 如何在 Teradata 中运行不带参数的程序

问题描述

如何在我尝试使用的 Teradata 中运行不带参数的程序：call db1.proc_dbOSA()

错误消息： 

Call failed 3707: PROC_DBOSA: Syntax error, expected something 
like a name or a Unicode delimited identifier between ‘(‘ and ‘)’

带有错误结果的新程序。当我只运行代码时，一切正常。

REPLACE PROCEDURE db1.proc_dbOSA()
BEGIN
    DELETE FROM db1.LOG_dbOSA;      
INSERT INTO
    db1.LOG_dbOSA
        (StoreNo, IDX, Flow, Status, MRP, OSA_IDX, OSA_AC)

WITH a AS (
SELECT    
    c.StoreCode,
    CAST(SUBSTRING(c.ArticleCode FROM 13 FOR 6) AS INT) AS IDX,
    RpType,
    CASE
        WHEN  c.MinimumTargetStockWrpl >= l.MinimumTargetStockWrpl THEN CAST(l.MinimumTargetStockWrpl AS INT)
        WHEN  c.MinimumTargetStockWrpl <  l.MinimumTargetStockWrpl THEN CAST(c.MinimumTargetStockWrpl AS INT)
    End AS StoreMin,
    c.ValUnrestrictedStock
FROM 
   db1.tab1 c
INNER JOIN
    (
    SELECT    
        StoreCode,
        ArticleCode,
        MinimumTargetStockWrpl    
    FROM 
        db1.tab1
    WHERE
        ProcessingDate = CURRENT_DATE - 14
    ) l ON c.StoreCode = l.StoreCode AND c.ArticleCode = l.ArticleCode
WHERE
    c.ProcessingDate = CURRENT_DATE AND c.MinimumTargetStockWrpl IS NOT NULL AND l.MinimumTargetStockWrpl IS NOT NULL AND l.MinimumTargetStockWrpl > 0
)

, t AS
(
SELECT    
    CAST(SUBSTRING(ArticleCode FROM 13 FOR 6) AS INT) AS IDX,
    RpType, 
    ArticlesPlanner
FROM 
    DWH_db_V.STK_B_ARTICLE_DAY_V
WHERE 
    ProcessingDate = CURRENT_DATE AND StoreCode = 'DR04'
)

SELECT 
    a.StoreCode,
    a.IDX,
    t.RpType,
    t.ArticlesPlanner,
    a.RpType,
    CASE
        WHEN a.ValUnrestrictedStock > 0 THEN 1
        WHEN a.ValUnrestrictedStock <= 0 THEN 0
    End AS OSA_IDX,
    CASE
        WHEN a.ValUnrestrictedStock >= StoreMin THEN 1
        WHEN a.ValUnrestrictedStock < StoreMin THEN 0
    End AS OSA_AC
FROM 
    a
LEFT JOIN
    t ON t.IDX = a.IDX;     
End;

BTEQ 错误：+---------+---------+---------+---------+------ ---+---------+---------+---- 调用proc_dbOSA；

调用proc_dbOSA；$ * 失败 3707 语法错误，在单词 'proc_dbOSA' 和 ';' 之间应该有类似 '(' 的内容。语句# 1，信息 =18 *总经过时间为 1 秒。

调用 proc_dbOSA(); * 失败 3707 PROC_DBOSA：语法错误，应为名称或 '(' 和 ')' 之间的 Unicode 分隔标识符。*总经过时间为 1 秒。

标签： sqlteradata-sql-assistant

解决方案

存储过程不支持以下内容：

存储过程中的 EXPLAIN 和 USING 请求修饰符

EXECUTE 宏语句

存储过程中的 WITH 子句。

存储过程以及宏不支持以下查询日志记录语句：

开始查询记录

结束查询记录

刷新查询记录

替换查询记录

推荐阅读