条件sql之间的情况之间是否存在语句 [英] if exists statement between a case when condition sql

问题描述

在存储过程的中间，我有以下代码段:

In the middle of my stored procedure, I have the following snippet of code:

case
  when l.codeleasestatuscode = '5' and priorleaseid is null 
  and l.leaid in(select col1 from Waitlisthousehold)
then '2'
else l.codeleasestatuscode
end

但是，最终条件(我必须从表Waitlisthousehold中进行选择)存在问题.并非所有数据库都有该表.因此，我希望表存在时将最后一个条件包括在内.但是当我尝试执行此操作时出现错误:

however, the final condition, wherein i have to select from the table Waitlisthousehold, has an issue. Not all databases has that table. so I want that last condition to be included when the table exists. But i'm getting errors when i try to do this:

case
when l.codeleasestatuscode = '5' and priorleaseid is null
IF EXISTS(select * from information_schema.tables where table_name='WaitlistHousehold')
  begin
    and l.leaid in(select col1 from Waitlisthousehold)
  end
then '2'
else l.codeleasestatuscode
end

那我该怎么做呢?

此代码段位于from语句内(来自table1 a.id = b.id上的join table2 b，以及何时..)

This snippet is inside a from statement(from table1 a join table2 b on a.id=b.id and case when..)

推荐答案

您可以在您的子句中使用另一种when子句来检查表是否存在

you can have another case when clause inside your one to check if the table exist or not

CASE
WHEN l.codeleasestatuscode = '5' and priorleaseid is null
    CASE WHEN EXISTS(SELECT * FROM information_schema.tables WHERE table_name='WaitlistHousehold')
        THEN
            CASE WHEN l.leaid in(select col1 from Waitlisthousehold) THEN '2' 
            ELSE l.codeleasestatuscode END
        ELSE '2' END          
    THEN '2'END
ELSE l.codeleasestatuscode
END

这篇关于条件sql之间的情况之间是否存在语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！