问题

我试图将一个脚本从Hana转换到Oracle，但在第5行的Rule_def.DATEUPD上一直出现相同的错误，错误消息是该字段不适用于提取。
有人可以告诉我如何在Oracle中正确地翻译这部分吗？谢谢！

SELECT
P2_RULE,
P2_ORIGIN,
Rule_def.DATEUPD as P2_DATE,
substr('0' || extract(hour from Rule_def.DATEUPD),2)
|| ':' ||
substr('0' || extract(minute from Rule_def.DATEUPD),2)
|| ':' ||
substr('0' || extract(second from Rule_def.DATEUPD),2) AS P2_DATETIME,
Rule_def.USERUPD || ' | ' || DESC_UTENTE as P2_USER
FROM (
SELECT
P2_RULE,
P2_ORIGIN,
DATEUPD,
USERUPD,
ROW_NUMBER() over (partition by P2_RULE, p2_origin order by DATEUPD desc) as N_row
FROM AW_PII_P20018_000001
WHERE dateupd is not null) Rule_def
join UTENTE
ON Rule_def.USERUPD = COD_UTENTE
WHERE N_ROW = 1

我尝试了一些日期公式，但它们不起作用。

SELECT 
	P2_RULE,
	P2_ORIGIN,
	Rule_def.DATEUPD as P2_DATE,
	substr('0' || extract(hour from Rule_def.DATEUPD),2) 
	|| ':' || 
	substr('0' || extract(minute from Rule_def.DATEUPD),2)
	|| ':' || 
	substr('0' || extract(second from Rule_def.DATEUPD),2) AS P2_DATETIME,
	Rule_def.USERUPD || ' | ' || DESC_UTENTE as P2_USER
FROM (
	SELECT 
		P2_RULE,
		P2_ORIGIN,
		DATEUPD,
		USERUPD,
		ROW_NUMBER() over (partition by P2_RULE, p2_origin order by DATEUPD desc) as N_row
	FROM AW_PII_P20018_000001
	WHERE dateupd is not null) Rule_def
join UTENTE
ON Rule_def.USERUPD = COD_UTENTE	
WHERE N_ROW = 1

答案1

得分: 1

您似乎想要使用 `TO_CHAR` 而不是使用 `SUBSTR` 进行复杂格式化：

```lang-sql
SELECT r.P2_RULE,
       r.P2_ORIGIN,
       r.DATEUPD as P2_DATE,
       TO_CHAR(r.DATEUPD, 'HH24:MI:SS') AS P2_DATETIME,
       r.USERUPD || ' | ' || u.DESC_UTENTE as P2_USER
FROM   ( SELECT P2_RULE,
                P2_ORIGIN,
                DATEUPD,
                USERUPD,
                ROW_NUMBER() over (
                  partition by P2_RULE, p2_origin order by DATEUPD desc
                ) as N_row
         FROM   AW_PII_P20018_000001
         WHERE dateupd is not null
       ) r
       join UTENTE u
       ON r.USERUPD = u.COD_UTENTE    
WHERE  r.N_ROW = 1

对于示例数据：

CREATE TABLE AW_PII_P20018_000001 (P2_RULE, P2_ORIGIN, DATEUPD, USERUPD) AS
SELECT 1, 2, SYSDATE, 3 FROM DUAL;

CREATE TABLE utente (desc_utente, cod_utente) AS
SELECT 'A', 3 FROM DUAL;

输出：

fiddle

<details>
<summary>英文:</summary>

You appear to want to use `TO_CHAR` rather than complex formatting with `SUBSTR`:

```lang-sql
SELECT r.P2_RULE,
       r.P2_ORIGIN,
       r.DATEUPD as P2_DATE,
       TO_CHAR(r.DATEUPD, &#39;HH24:MI:SS&#39;) AS P2_DATETIME,
       r.USERUPD || &#39; | &#39; || u.DESC_UTENTE as P2_USER
FROM   ( SELECT P2_RULE,
                P2_ORIGIN,
                DATEUPD,
                USERUPD,
                ROW_NUMBER() over (
                  partition by P2_RULE, p2_origin order by DATEUPD desc
                ) as N_row
         FROM   AW_PII_P20018_000001
         WHERE dateupd is not null
       ) r
       join UTENTE u
       ON r.USERUPD = u.COD_UTENTE    
WHERE  r.N_ROW = 1

Which, for the sample data:

CREATE TABLE AW_PII_P20018_000001 (P2_RULE, P2_ORIGIN, DATEUPD, USERUPD) AS
SELECT 1, 2, SYSDATE, 3 FROM DUAL;

CREATE TABLE utente (desc_utente, cod_utente) AS
SELECT 'A', 3 FROM DUAL;

Outputs:

fiddle

答案2

得分: 0

从Oracle文档中，

如果要求HOUR、MINUTE或SECOND，则表达式必须评估为数据类型为TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE或INTERVAL DAY TO SECOND的表达式。在这里，DATE无效，因为Oracle数据库将其视为ANSI DATE数据类型，不具有时间字段。

因此，您需要将DATE字段的类型转换为TIMESTAMP，如下所示：

extract(hour from cast(Rule_def.DATEUPD as timestamp))

以及分钟和秒钟的转换也是如此。

extract(hour from cast(Rule_def.DATEUPD as timestamp))