英文:
Executing a CASE statement with multiple conditions
问题
查询使用两个表进行左连接，然后根据偏好进行筛选。它运行良好，但是当我添加'case'语句时遇到了问题。我的想法是提取我需要的前几位数字，然后将它们分类为更广泛的类别。我需要能够将字段的名称更改为友好的内容。有什么建议吗？
字段tr.TENANT_NAICS的值遵循类似于以下格式：
- 543330- Other Computer Related | 2782 Science
- 548972- Socials
希望更改为：
- Other Computer Related
- Other Computer Related
查询：
select
tr.OCCUPANCY_DATE, tr.END_DATE, tr.TENANT_NAICS,
pr.PROPERTY_STATUS, pr.NRA_BUILDING, pr.MARKET, pr.SUBMARKET, pr.FULL_ADDRESS_ONE_LINE,
pr.LEGAL_OWNER, pr.TRUE_OWNER,
lr.LESSOR_SUBLESSOR,
/* 在这里尝试修复 */
(case
when left(tr.TENANT_NAICS, 2)::numeric = 11 THEN 'Other Computer Related'
when left(tr.TENANT_NAICS, 2)::numeric = 21 THEN 'Mining'
when left(tr.TENANT_NAICS, 4)::numeric = 4821 THEN 'Construction'
else tr.TENANT_NAICS
end) as "Tenant Industry"
from space.tran tr
left join property pr on tr.ID = pr.ID
left join lease lr on tr.OID = lr.OID
where
tr.STATUS = 'Existing' and tr.MARKET = 'Seattle'
;
编辑：我收到的确切错误信息是'Numeric value 'Bu' is not recognized'。
英文:
Query takes two tables does a left join, then filters out based off preferences. It runs well, however, when I add the 'case' statement I run into issues. The idea was to grab the first digits I need to classify them into a broader category. I need to be able to change the names of a field into something friendly. Any suggestions?
Values for the tr.TENANT_NAICS field follow a format similar to this:
- 543330- Other Computer Related | 2782 Science
- 548972- Socials
Would like to change to just:
- Other Computer Related
- Other Computer Related
Query
select
tr.OCCUPANCY_DATE, tr.END_DATE, tr.TENANT_NAICS,
pr.PROPERTY_STATUS, pr.NRA_BUILDING, pr.MARKET, pr.SUBMARKET, pr.FULL_ADDRESS_ONE_LINE,
pr.LEGAL_OWNER, pr.TRUE_OWNER,
lr.LESSOR_SUBLESSOR,
/* trying to fix here */
(case
when left(tr.TENANT_NAICS, 2)::numeric = 11 THEN 'Other Computer Related'
when left(tr.TENANT_NAICS, 2)::numeric = 21 THEN 'Mining'
when left(tr.TENANT_NAICS, 4)::numeric = 4821 THEN 'Construction'
else tr.TENANT_NAICS
end) as "Tenant Industry"
from space.tran tr
left join property pr on tr.ID = pr.ID
left join lease lr on tr.OID = lr.OID
where
tr.STATUS = 'Existing' and tr.MARKET = 'Seattle'
;
Edit: the exact error I receive is 'Numeric value 'Bu' is not recognized'.
答案1
得分: 1
显然，TENANT_NAICS是一个varchar列，必须至少有一个值以BU开头而不是数字。尝试这个SQL语句：
select
tr.OCCUPANCY_DATE, tr.END_DATE, tr.TENANT_NAICS,
pr.PROPERTY_STATUS, pr.NRA_BUILDING, pr.MARKET, pr.SUBMARKET, pr.FULL_ADDRESS_ONE_LINE,
pr.LEGAL_OWNER, pr.TRUE_OWNER,
lr.LESSOR_SUBLESSOR,
/* trying to fix here */
(case
when left(tr.TENANT_NAICS, 2) = '11' THEN 'Other Computer Related'
when left(tr.TENANT_NAICS, 2) = '21' THEN 'Mining'
when left(tr.TENANT_NAICS, 4) = '4821' THEN 'Construction'
else tr.TENANT_NAICS
end) as "Tenant Industry"
from space.tran tr
left join property pr on tr.ID = pr.ID
left join lease lr on tr.OID = lr.OID
where
tr.STATUS = 'Existing' and tr.MARKET = 'Seattle'
;
英文:
Apparently TENANT_NAICS is a varchar column and there must be at least one value that starts with BU instead of digits. Try this SQL:
select
tr.OCCUPANCY_DATE, tr.END_DATE, tr.TENANT_NAICS,
pr.PROPERTY_STATUS, pr.NRA_BUILDING, pr.MARKET, pr.SUBMARKET, pr.FULL_ADDRESS_ONE_LINE,
pr.LEGAL_OWNER, pr.TRUE_OWNER,
lr.LESSOR_SUBLESSOR,
/* trying to fix here */
(case
when left(tr.TENANT_NAICS, 2) = '11' THEN 'Other Computer Related'
when left(tr.TENANT_NAICS, 2) = '21' THEN 'Mining'
when left(tr.TENANT_NAICS, 4) = '4821' THEN 'Construction'
else tr.TENANT_NAICS
end) as "Tenant Industry"
from space.tran tr
left join property pr on tr.ID = pr.ID
left join lease lr on tr.OID = lr.OID
where
tr.STATUS = 'Existing' and tr.MARKET = 'Seattle'
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论