doudouwen2763 2015-10-23 10:38
浏览 70
已采纳

在查询中使用多个条件时出错

I am working with PostgreSQL 8.4.4. I want to fetch record from table based on multiple conditions as,

1) check if record exists

2) if exists then -> if type_id is 1 then select name if type_id is 2 then select name, abbreviation

3) if record not exits then print as 'Missing'

Following is the query for the same

select 
    case 
        when exists (select NAME || ' ' || ABBREVIATION from CONTACTS_COMPANY where id = 15)
        then ( select
                case
                    when type_id = '1' 
                        then (select UPPER(NAME) from CONTACTS_COMPANY where id = 15) 
                    when type_id = '2' 
                        then (select UPPER(NAME) || ' ' || UPPER(ABBREVIATION) from CONTACTS_COMPANY where id = 15) 
                end
            ) 
    else UPPER('MISSING')
end

When firing above query I am getting error as

SQL error:

ERROR: column "type_id" does not exist

LINE 7: when type_id = '1'

I am not getting what exactly is causing error. Any help would be appreciated. Thanks in advance!

  • 写回答

1条回答 默认 最新

  • duanlieshuang5330 2015-10-23 11:30
    关注

    Assuming that "type_id" does in fact exist (you probably made sure of that..?), I would say that the query is not structured correctly. Your outermost select misses a "from" - statement. Furthermore, your inner statements are not correct. It should look something like this:

    select 
        case 
            when exists  
               (select ('NAME' || ' ' || 'ABBREVIATION')
               from "CONTACTS_COMPANY")
            then (
                case
                    when type_id = '1' 
                        then UPPER("NAME") 
                    when type_id = '2' 
                        then  UPPER("NAME") || ' ' || UPPER("ABBREVIATION") 
                end
            ) 
    
        else UPPER('MISSING')
        end
    from "CONTACTS_COMPANY"
    WHERE id='15'
    

    I haven't tested this, but the general structure should be ok. And I have hopefully understood correctly, what you are trying to do. Please post a comment, if that is not so :)

    UPDATE code to working solution

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Catia V5 R20 64位 安装过程中选择orbix配置创建套接字失败
  • ¥100 C51单片机设计交通灯时出现的问题
  • ¥15 R语言爬虫的时候元素和园代码不一样怎么解决呀
  • ¥15 VS2022多项目启动有问题
  • ¥15 SQL删除添加数据后序号不连续问题。
  • ¥15 首次运行OmniEvent运行报错
  • ¥15 有没有人知道这个问题怎么解决
  • ¥15 comsol电力电缆载流量仿真
  • ¥15 webSocket可以接TCP socket接口吗
  • ¥60 mpi并行出错,CFD++计算