将JSON转换为表SAP HANA时的语法问题

2020-09-14 23:50发布

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


嗨,

我已经创建了如下的JSON表。

创建表T1(A INT,B NVARCHAR(5000));
 将T1值插入(1,'
 {
         " PONumber":1
         "参考":" BSMITH-74635645",
         "请求者":"倒钩史密斯",
         "用户":" BSMITH",
         " CostCenter":" A50",
         "运输说明":
                {
                        " name":" Barb Smith",
                        "地址":
                                {
                                       "街道":"仙童大街100号",
                                       " city":" San Diego",
                                        " state":" CA",
                                       " zipCode":23345,
                                       "国家":"美国"
                                },
                        "电话":[{"类型":"办公室","电话号码":" 519-555-6310"}}
                },
         " SpecialInstructions":" Surface Mail",
         " LineItems":[
                {" ItemNumber":1," Part":{" Description":" Basic Kit"," UnitPrice":19.95," UPCCode":73649587162}," Quantity":7},
                {" ItemNumber":2," Part":{" Description":" Base Kit 2"," UnitPrice":29.95," UPCCode":83600229374}," Quantity":1},
                {" ItemNumber":3," Part":{" Description":" Professional"," UnitPrice":39.95," UPCCode":33298003521}," Quantity":8},
                {" ItemNumber":4," Part":{" Description":" Enterprise"," UnitPrice":49.95," UPCCode":91827739856}," Quantity":8},
                {" ItemNumber":5," Part":{" Description":" Unlimited"," UnitPrice":59.95," UPCCode":22983303876}," Quantity":8}
         ]
 }
 ');
 

我想将订单项JSON转换为表格,并使用下面的查询来实现。

 SELECT JT。*
 从JSON_TABLE(T1.B,'$ .LineItems [*]'
 列
     (
         RN出于普通性,
         ITEM_NUMBER INT PATH'$ .ItemNumber',
         UPC_CODE BIGINT PATH'$ .Part.UPCCode'
     )
 )AS JT; 

但是我收到如下语法错误

无法执行'SELECT JT。* FROM JSON_TABLE(DEVTEST.T1.B,'$ .LineItems [*]'COLUMNS(RN FOR ORDINALITY,...'
Error:(dberror)257 -sql语法错误:" COLUMNS"附近的语法不正确:第45行第2列(在pos 66)

有人可以帮我吗?

此致

Viswateja

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


嗨,

我已经创建了如下的JSON表。

创建表T1(A INT,B NVARCHAR(5000));
 将T1值插入(1,'
 {
         " PONumber":1
         "参考":" BSMITH-74635645",
         "请求者":"倒钩史密斯",
         "用户":" BSMITH",
         " CostCenter":" A50",
         "运输说明":
                {
                        " name":" Barb Smith",
                        "地址":
                                {
                                       "街道":"仙童大街100号",
                                       " city":" San Diego",
                                        " state":" CA",
                                       " zipCode":23345,
                                       "国家":"美国"
                                },
                        "电话":[{"类型":"办公室","电话号码":" 519-555-6310"}}
                },
         " SpecialInstructions":" Surface Mail",
         " LineItems":[
                {" ItemNumber":1," Part":{" Description":" Basic Kit"," UnitPrice":19.95," UPCCode":73649587162}," Quantity":7},
                {" ItemNumber":2," Part":{" Description":" Base Kit 2"," UnitPrice":29.95," UPCCode":83600229374}," Quantity":1},
                {" ItemNumber":3," Part":{" Description":" Professional"," UnitPrice":39.95," UPCCode":33298003521}," Quantity":8},
                {" ItemNumber":4," Part":{" Description":" Enterprise"," UnitPrice":49.95," UPCCode":91827739856}," Quantity":8},
                {" ItemNumber":5," Part":{" Description":" Unlimited"," UnitPrice":59.95," UPCCode":22983303876}," Quantity":8}
         ]
 }
 ');
 

我想将订单项JSON转换为表格,并使用下面的查询来实现。

 SELECT JT。*
 从JSON_TABLE(T1.B,'$ .LineItems [*]'
 列
     (
         RN出于普通性,
         ITEM_NUMBER INT PATH'$ .ItemNumber',
         UPC_CODE BIGINT PATH'$ .Part.UPCCode'
     )
 )AS JT; 

但是我收到如下语法错误

无法执行'SELECT JT。* FROM JSON_TABLE(DEVTEST.T1.B,'$ .LineItems [*]'COLUMNS(RN FOR ORDINALITY,...'
Error:(dberror)257 -sql语法错误:" COLUMNS"附近的语法不正确:第45行第2列(在pos 66)

有人可以帮我吗?

此致

Viswateja

付费偷看设置
发送
2条回答
奄奄一息的小鱼
1楼-- · 2020-09-15 00:07

Namaste Viswateja

您是否还不需要使用DB模式,还是您使用schema = DEVTEST进行了设置? 预先?

SELECT JT。*
从JSON_TABLE(
DEVTEST.T1.B,'$ .LineItems [*]'
列(
RN FOR ORDINALITY,
ITEM_NUMBER INT PATH'$ .ItemNumber',
UPC_CODE BIGINT PATH'$ .Part.UPCCode'

)AS JT;

致谢

Dio。

小熊yu生菜
2楼-- · 2020-09-15 00:03

您是否在HANA数据库平台上启用了JSON Document Store?

一周热门 更多>