PostgreSQL中“when”或附近的语法错误

When I run the following create statement I get the error syntax error at or near "when." I tried commenting out sections to figure out which one is causing it but it seem that they all have the same syntax error. I am new to postgreSQL, so I may have more than that wrong with this create statement.

当我运行以下create语句时,我在“when”或附近得到错误语法错误。我尝试评论部分以找出导致它的部分,但似乎它们都具有相同的语法错误。我是postgreSQL的新手,所以我对这个create语句可能有更多错误。

Create table as table_name as with other_table as
     (select order_id
            ,case when strpos(comment_txt,'LIST OF NEW TNs') > 0
                   and strpos(substr(comment_txt, strpos(comment_txt,'LIST OF NEW TNs')),chr(10)) + strpos(comment_txt,'LIST OF NEW TNs') -1 > 0 then
                    substr(comment_txt
                           ,strpos(comment_txt,'LIST OF NEW TNs')
                           ,strpos(substr(comment_txt, strpos(comment_txt,'LIST OF NEW TNs')),chr(10)) + strpos(comment_txt,'LIST OF NEW TNs') -1-strpos(comment_txt,'LIST OF NEW TNs')
                          )
                  when strpos(comment_txt,'LIST OF NEW TN') > 0 then
                    substr(comment_txt
                           ,strpos(comment_txt,'LIST OF NEW TN')
                           ,strpos(substr(comment_txt,strpos(comment_txt,'LIST OF NEW TN')),'NUMBER OF PORTED TN') + strpos(comment_txt,'LIST OF NEW TN') -1 -strpos(comment_txt,'LIST OF NEW TN')
                          )

-- There are several more when statements here that are like the above. 

              end as assigned_tns
            ,case when strpos(comment_txt,'LIST THE PORTED TNs') > 0
                   and strpos(substr(comment_txt,strpos(comment_txt,'LIST THE PORTED TN')),chr(10)) + strpos(comment_txt,'LIST THE PORTED TN') -1 > 0 then
                    substr(comment_txt
                           ,strpos(comment_txt,'LIST THE PORTED TNs')
                           ,strpos(substr(comment_txt,strpos(comment_txt,'LIST THE PORTED TN')),chr(10)) + strpos(comment_txt,'LIST THE PORTED TN') -1 -strpos(comment_txt,'LIST THE PORTED TN')
                          )
                  when strpos(comment_txt,'LIST THE PORTED TN') > 0
                   and strpos(comment_txt,'PRIMARY') > 0 then
                    substr(comment_txt
                           ,strpos(comment_txt,'LIST THE PORTED TN')
                           ,strpos(substr(comment_txt,strpos(comment_txt,'LIST THE PORTED TN')),'PRIMARY' + strpos(comment_txt,'LIST THE PORTED TN') -1
                           -strpos(comment_txt,'LIST THE PORTED TN')
                          )

-- There are several more when statements here that are like the above.

              end as ported_tns
            ,comment_txt
            ,comment_dt
        from second_table
     )
  select order_id
        ,replace(assigned_tns,chr(10))   as assigned_tns
        ,replace(ported_tns,chr(10))     as ported_tns
        ,comment_dt
    from other_table

I shortened the code quite a bit, but what I removed was structured the same as the above when statements. The overall code has probably close to 50 when statements so I didn't want to post the entire code. This code was originally written in oracle and I had to convert it to postgreSQL, so some of the things may not exist in postgreSQL.

我将代码缩短了很多,但是我删除的内容与上面的语句结构相同。语句的整体代码可能接近50,所以我不想发布整个代码。这段代码最初是用oracle编写的,我不得不把它转换成postgreSQL,所以postgreSQL中可能不存在一些东西。

1 个解决方案

#1

I figured out the problem. I was missing two instances of parenthesis in the when statements and when I fixed it it runs as expected

我解决了这个问题。我在when语句中缺少两个括号实例,当我修复它时,它按预期运行