对临时表进行查询时表或对象无效（Pervasive SQL）

发布于 2024-10-22 11:37:03 字数 967 浏览 1 评论 0原文

我有一个 SP，它将记录插入到临时表中，然后选择记录并返回它们。 SQL是这样的。

我通过删除 INSERT INTO 语句并最小化 SQL 来解决该问题。罪魁祸首是 SELECT * FROM #Worklist1。不知道为什么这不起作用。如果有帮助的话，我（刚刚）升级到最新版本的 Pervasive 服务器版本 10，但这个问题在 10.3 中仍然存在。一定是缺少了什么。

CREATE PROCEDURE "Connect_Workflow"(
     :StartDate DATETIME, :EndDate DATETIME)
     RETURNS(Patient varchar(100) , 
        AccessionNo varchar(25)  
        );
BEGIN
    CREATE TABLE #WorkFlow1
        (Patient varchar(100) null, 
        AccessionNo varchar(25) null 
    );  
    INSERT INTO #Workflow1(
      SELECT 
        rtrim(p.LastName),--+ '^' + rtrim(p.FirstName) + isnull('^' + rtrim(p.Initial), ''),
        v.VisitID   -- equiv to EncounterID
    FROM visit v
    join patient p on v.patientnumber = p.patientnumber
    WHERE v.VisitYY = '99'
    );

    SELECT * FROM #WorkFlow1;
    DROP TABLE #Workflow1;


END

更新：注释掉 SELECT * FROM #Worklist1 后；它仍然给出无效表错误。如果我删除 INSERT INTO 和 SELECT * 那么最终错误就消失了。引用表格时一定有错误。

原文

I have a SP that inserts records into a temp table, then selects the records and returns them. The SQL is this.

I troubleshot it by removing the INSERT INTO statement, and minimizing the SQL. The culprit is the SELECT * FROM #Worklist1. No idea why this does not work. I upgraded (just now) to latest version of Pervasive server ver 10 if that helps, but this issue was in 10.3 and its still there. Must be missing something.

CREATE PROCEDURE "Connect_Workflow"(
     :StartDate DATETIME, :EndDate DATETIME)
     RETURNS(Patient varchar(100) , 
        AccessionNo varchar(25)  
        );
BEGIN
    CREATE TABLE #WorkFlow1
        (Patient varchar(100) null, 
        AccessionNo varchar(25) null 
    );  
    INSERT INTO #Workflow1(
      SELECT 
        rtrim(p.LastName),--+ '^' + rtrim(p.FirstName) + isnull('^' + rtrim(p.Initial), ''),
        v.VisitID   -- equiv to EncounterID
    FROM visit v
    join patient p on v.patientnumber = p.patientnumber
    WHERE v.VisitYY = '99'
    );

    SELECT * FROM #WorkFlow1;
    DROP TABLE #Workflow1;


END

Update: After commenting out the SELECT * FROM #Worklist1; it still gives a invalid table error. If I remove the INSERT INTO and the SELECT * then finally the error is gone. Must be error in referencing the table.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

骑趴 2024-10-29 11:37:03

删除删除表#Workflow1；根据您的查询。
我相信它会在 SP 返回数据之前删除该表。

回复收藏 0 原文

欢你一世 2024-10-29 11:37:03

好吧，我想通了。尽管该过程应该可以正常工作，但事实上 Pervasive 推荐了类似的方法。使用选择进入

CREATE PROCEDURE "Connect_Workflow"(
     :StartDate DATETIME, :EndDate DATETIME)
     RETURNS(Patient varchar(100) , 
        AccessionNo varchar(25)  
        );
BEGIN
    SELECT 
        rtrim(p.LastName),--+ '^' + rtrim(p.FirstName) + isnull('^' + rtrim(p.Initial), ''),
        v.VisitID   -- equiv to EncounterID
    FROM visit v
    INTO #Workflow1
    join patient p on v.patientnumber = p.patientnumber
    WHERE v.VisitYY = '99'
    );
    SELECT * FROM #WorkFlow1;
END

Okay i figured it out. Although the procedure should work fine, in fact Pervasive recommends something like this. use SELECT INTO

CREATE PROCEDURE "Connect_Workflow"(
     :StartDate DATETIME, :EndDate DATETIME)
     RETURNS(Patient varchar(100) , 
        AccessionNo varchar(25)  
        );
BEGIN
    SELECT 
        rtrim(p.LastName),--+ '^' + rtrim(p.FirstName) + isnull('^' + rtrim(p.Initial), ''),
        v.VisitID   -- equiv to EncounterID
    FROM visit v
    INTO #Workflow1
    join patient p on v.patientnumber = p.patientnumber
    WHERE v.VisitYY = '99'
    );
    SELECT * FROM #WorkFlow1;
END

回复收藏 0 原文

~没有更多了~