sql - 将访问代码转换为 Oracle 语法(Power Query)
问题描述
我正在努力将 Access Query 转移到 Power Query (Excel) 中。我认为在条款/措辞方面存在一些挑战。它是一个 Oracle 数据库。
原始代码如下所示:
SELECT
POOLK.DATA.DOC,
Val(Min(Right([PNR],5))) AS Value,
POOLK.DATA.RE,
POOLK.DATA.ST INTO [tValue]
FROM
POOLK.DATA
GROUP BY
POOLK.DATA.DOC,
POOLK.DATA.RE,
POOLK.DATA.ST
HAVING (((POOLK.DATA.DOC)>25000) AND ((POOLK.DATA.RE)="B9"))
ORDER BY POOLK.DATA.DOC DESC
这是我从现在开始的方法:
SELECT
LTRIM((SUBSTR(POOLK.DATA."PNR", -5)), '0') AS Value,
POOLK.DATA.DOC,
POOLK.DATA.RE,
POOLK.DATA.ST
FROM
POOLK.DATA
WHERE
POOLK.DATA.DOC >25000
AND
POOLK.DATA.RE ='B9'
我使用 HAVING 和 GROUP BY 功能失败。我认为哪里有其他价值,对吧?
有人有想法吗?
最好的祝福
解决方案
你仍然需要GROUP BY
:
SELECT LTRIM(MIN(SUBSTR(D.PNR, -5)), '0') AS Value,
D.DOC, D.RE, D.ST
FROM POOLK.DATA d
WHERE D.DOC > 25000 AND
D.RE ='B9'
GROUP BY D.DOC, D.RE, D.ST;
我还添加了表别名,以便代码更易于编写和阅读。
INTO
正在创建一个表。如果你真的想这样做,那么:
create table tvalue as
SELECT LTRIM(MIN(SUBSTR(D.PNR, -5)), '0') AS Value,
D.DOC, D.RE, D.ST
FROM POOLK.DATA d
WHERE D.DOC > 25000 AND
D.RE ='B9'
GROUP BY D.DOC, D.RE, D.ST;