oracle 获取属性存储最大值的行对应的子类型信息

xeufq47z 于 2023-04-20 发布在 Oracle

关注(0)|答案(1)|浏览(108)

我需要检索信息，包括从分销商的地址，具有最高的销售价值：

CREATE OR REPLACE TYPE Distributor_address_ty AS OBJECT (
Street VARCHAR(20),
No VARCHAR(6),
City VARCHAR(15),
Postal_code VARCHAR(6),
Province VARCHAR(2));

CREATE OR REPLACE TYPE Distributor_ty AS OBJECT (
VAT VARCHAR(15),
Seq_No NUMBER,
Sold NUMBER,
Address Distributor_address_ty)
NOT FINAL;

我试过的是：

SELECT VAT, seq_no, sold, 
DEREF(VALUE(d)).address.street AS Street,
DEREF(VALUE(d)).address.No AS Number,
DEREF(VALUE(d)).address.cut AS City,
DEREF(VALUE(d)).address.postal_code AS Postal Code,
DEREF(VALUE(d)).address.province AS Province
FROM (SELECT * FROM distributor d
ORDER BY sold DESC) 
WHERE rownum = 1;

但返回错误：不一致的数据类型：期望的REF得到DB：_EXAM.Distributor_ty
建议？

oracle

来源：https://stackoverflow.com/questions/76010615/get-information-about-sub-type-corresponding-to-a-row-with-attribute-storing-a-m

1条答案

按热度按时间

q3qa4bjr1#

1.表别名d是在子查询中定义的，而不是在外部查询中定义的。如果您尝试在外部查询中使用它，则它将是无效的标识符;
1.您没有使用REF类型;

NUMBER是保留字，不能用作无引号标识符;和
1.地址类型没有cut属性。
在Oracle 12中，使用FETCH FIRST ROW ONLY，不要尝试取消引用地址：

SELECT VAT,
       seq_no,
       sold, 
       d.address.street AS Street,
       d.address.No AS "NUMBER",
       d.address.city AS City,
       d.address.postal_code AS Postal_Code,
       d.address.province AS Province
FROM   distributor d
ORDER BY sold DESC
FETCH FIRST ROW ONLY;

在早期版本中：

SELECT *
FROM   (
  SELECT VAT,
         seq_no,
         sold, 
         d.address.street AS Street,
         d.address.No AS "NUMBER",
         d.address.city AS City,
         d.address.postal_code AS Postal_Code,
         d.address.province AS Province
  FROM   distributor d
  ORDER BY sold DESC
)
WHERE  ROWNUM = 1;

fiddle

赞(0）回复(0）举报 2023-04-20

我来回答

oracle 获取属性存储最大值的行对应的子类型信息

1条答案

相关问题

热门标签

最新问答