使用Max创建的列名的Oracle sql标识符无效(大小写为then)

olqngx59 于 2023-03-22 发布在 Oracle

关注(0)|答案(2)|浏览(140)

我只需要过滤掉某些版本，但是当我尝试在where子句中过滤时，我得到了一个错误。你建议我如何过滤掉这个错误？
这是我的错误：ORA-00904：“APP_VER”：无效标识符00904。00000 -“%s：无效标识符”* 原因：

操作：行出错：181柱：5
查询很长，但这是一个摘录：

Select
prop.org,
DEVICE,
DISPLAY_TEXT,
MESSAGE_TEXT,
...
FROM
(
--history
select
SUBSTR(hist.code,1,14) as Device,
...
    MAX(CASE WHEN METRICCODE = 'display_text' THEN D.METRICVALUE END) DISPLAY_TEXT,
    MAX(CASE WHEN METRICCODE = 'message_text' THEN D.METRICVALUE END) MESSAGE_TEXT,
    MAX(CASE WHEN METRICCODE = 'device.timestamp' THEN D.METRICVALUE END) DEVICE_TIMESTAMP,
    MAX(CASE WHEN METRICCODE = 'alert_id' THEN D.METRICVALUE END) ALERT_ID,
    MAX(CASE WHEN METRICCODE = 'time_stamp' THEN D.METRICVALUE END) TIME_STAMP,
    MAX(CASE WHEN METRICCODE = 'error code' THEN D.METRICVALUE END) ERROR_CODE_VALUE,
    MAX(CASE WHEN METRICCODE = 'software.version.and.patch.name' THEN D.METRICVALUE END) APP_VER, **--this needs filtering**
    ...
from
(select  *
from 
hist_tbl
where stateId in (1.2.3)
)hist,
d_hist_tbl dmhh,
...
component_tbl cst,
...
where
(join info for the tables)
group by (list)

UNION
--current state
   substr(col,tbl,1,14) as Device,
   ...,
    MAX(CASE WHEN METRICCODE = 'display_text' THEN D.METRICVALUE END) DISPLAY_TEXT,
    MAX(CASE WHEN METRICCODE = 'message_text' THEN D.METRICVALUE END) MESSAGE_TEXT,
    MAX(CASE WHEN METRICCODE = 'device.timestamp' THEN D.METRICVALUE END) DEVICE_TIMESTAMP,
    MAX(CASE WHEN METRICCODE = 'alert_id' THEN D.METRICVALUE END) ALERT_ID,
    MAX(CASE WHEN METRICCODE = 'time_stamp' THEN D.METRICVALUE END) TIME_STAMP,
    MAX(CASE WHEN METRICCODE = 'error code' THEN D.METRICVALUE END) ERROR_CODE_VALUE,
    MAX(CASE WHEN METRICCODE = 'software.version.and.patch.name' THEN D.METRICVALUE END) APP_VER, **--this needs filtering**
    ...
    from
    (select * from state_tbl
    where stateId in (1.2.3)
    
    where
    ...
) dcs,
d_metric_val_tbl dmvt,
...
component_tbl cst,
...
where
(table join info)
**and APP_VER like '%V12.0%'  --error line**
(group by list)
)result,
--Properties
(
select 
o.org,
d.did propdmdid,
dm.dcode,
dm.isactive,
...
from
...,
...,
(join info)
(group by)
)prop
where
prop.dcode = result.device

我如何过滤app_ver的一个最大大小写，其中版本是V12.0？
我知道我没有提供所有的表信息，但它只是太多的表，我正在寻找我希望是一般信息。我试图谷歌它，但我没有看到这样的东西。如果我可以添加一个和到最大行的app_ver，那将是伟大的。也有可能我把过滤器部分的app_ver在错误的区域，并没有看到它。

oracle

来源：https://stackoverflow.com/questions/75805692/oracle-sql-invalid-identifier-with-column-name-created-with-maxcase-then

2条答案

按热度按时间

8oomwypt1#

您得到的错误意味着在您从中选择数据的任何表中都没有这样的列（APP_VER）。
我将试着用一个简单的例子来说明它，这就是你现在所拥有的（遵循标有1、2、3的注解）：

SQL> select *
  2  from (select job from       --> 2) outer subquery doesn't select DEPTNO
  3          (select job, deptno from emp where deptno = 10  --> 1) DEPTNO is here, in
  4           union all                                      -->    this (inner) subquery
  5           select job, deptno from emp where deptno = 20
  6          )
  7       )
  8  where deptno = 10  --> 3) therefore, you can't reference it here
  9  /
where deptno = 10  --> 3) therefore, you can't reference it here
      *
ERROR at line 8:
ORA-00904: "DEPTNO": invalid identifier

但是，如果您“移动”违规列，使其变得“可见”，则查询工作：

SQL> select *
  2  from (select job, deptno from       --> 2) outer subquery now selects DEPTNO
  3          (select job, deptno from emp where deptno = 10  --> 1) DEPTNO is here, in
  4           union all                                      -->    this (inner) subquery
  5           select job, deptno from emp where deptno = 20
  6          )
  7       )
  8  where deptno = 10  --> 3) therefore, you CAN reference it here
  9  /

JOB           DEPTNO
--------- ----------
MANAGER           10
PRESIDENT         10
CLERK             10

SQL>

你发布的代码（格式很差，一点帮助都没有）显示了很多子查询（还有很多“...”的部分）所以很难看清到底发生了什么。我建议你重新开始，（子）查询依据（子）查询，经常测试，直到得到的数据是你所期望的才继续。然后，当你在APP_VER上包含过滤器时，我希望你能按照甲骨文期望的方式去做。

赞(0）回复(0）举报 2023-03-22

83qze16e2#

使用HAVING子句并包含表达式而不是别名：

Select some_columns
FROM   (
  select SUBSTR(hist.code,1,14) as Device,
         MAX(
           CASE
           WHEN METRICCODE = 'software.version.and.patch.name'
           THEN D.METRICVALUE 
           END
         ) AS APP_VER
  from   some_tables
  group by
        SUBSTR(hist.code,1,14)
UNION
  select substr(col,tbl,1,14) as Device,
         MAX(
           CASE
           WHEN METRICCODE = 'software.version.and.patch.name'
           THEN D.METRICVALUE
           END
         ) AS APP_VER
  from   some_other_tables
  group by 
        substr(col,tbl,1,14)
  HAVING
        MAX(
          CASE
          WHEN METRICCODE = 'software.version.and.patch.name'
          THEN D.METRICVALUE
          END
        ) LIKE '%V12.0%'
)

或者，如果你想过滤UNION的两个部分，那么在外部查询中过滤：

Select some_columns
FROM   (
  select SUBSTR(hist.code,1,14) as Device,
         MAX(
           CASE
           WHEN METRICCODE = 'software.version.and.patch.name'
           THEN D.METRICVALUE 
           END
         ) AS APP_VER
  from   some_tables
  group by
        SUBSTR(hist.code,1,14)
UNION
  select substr(col,tbl,1,14) as Device,
         MAX(
           CASE
           WHEN METRICCODE = 'software.version.and.patch.name'
           THEN D.METRICVALUE
           END
         ) AS APP_VER
  from   some_other_tables
  group by 
        substr(col,tbl,1,14)
)
WHERE  app_ver LIKE '%V12.0%'

赞(0）回复(0）举报 2023-03-22

我来回答

使用Max创建的列名的Oracle sql标识符无效(大小写为then)

2条答案

相关问题

热门标签

最新问答