1.有个视图建表语句如下:
CREATE OR REPLACE VIEW "ASSET"."ip_area_view"
AS
SELECT
bbb."ID" as id,
bbb."IP" as ip ,
bbb."SECURITY_DOMAIN_NAME" as security_domain_name
,
bbb."COUNTRY" as country,
bbb."AREA" as area,
SUBSTRING(SUBSTRING(bbb."LONGITUDE_LATITUDE" FROM 1 FOR POSITION(' ' IN bbb."LONGITUDE_LATITUDE") - 1) FROM POSITION('经' IN SUBSTRING(bbb."LONGITUDE_LATITUDE" FROM 1 FOR POSITION(' ' IN bbb."LONGITUDE_LATITUDE") - 1)) + 1
) AS longitude,
SUBSTRING(
SUBSTRING(bbb."LONGITUDE_LATITUDE" FROM POSITION(' ' IN bbb."LONGITUDE_LATITUDE") + 1)
FROM POSITION('纬' IN SUBSTRING(bbb."LONGITUDE_LATITUDE" FROM POSITION(' ' IN bbb."LONGITUDE_LATITUDE") + 1)) + 1
) AS latitude,
bbb."IS_INTRANET" as is_intranet,
bbb."res_domain_id" AS res_domain_id
FROM
(
SELECT
md5( concat ( dev."dev_core_ipv4", sec."safe_name" ) ) AS id,
dev."dev_core_ipv4" AS ip,
CASE
WHEN sec."safe_name" IS NULL THEN
'未知安全域' ELSE sec."safe_name"
END AS security_domain_name,
'中国' AS country,
dev."dev_owner_area" AS area,
CASE
WHEN org."longitude_latitude" IS NOT NULL THEN
org."longitude_latitude"
WHEN org."longitude_latitude" IS NULL AND dev."dev_owner_area" IS NOT NULL THEN
( SELECT bbb_1."longitude_latitude" FROM "ASSET"."asset_device" aaa INNER JOIN "ASSET"."dmss_area" bbb_1 ON aaa."dev_owner_area" = bbb_1."area" LIMIT 1 ) ELSE NULL
END AS longitude_latitude,
CASE
Copy
WHEN sec."safe_type" = 1 THEN 1
WHEN sec."safe_type" = 2 THEN 0 ELSE 2
END AS is_intranet,
sec."res_domain_id"
FROM
"ASSET"."asset_device" dev
LEFT JOIN "ASSET"."asset_organization" org ON dev."org_id" = org."id"
LEFT JOIN "A
2查看对应视图里的字段名字使用sql如下“
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'ip_area_view'
结果如下:
均为大写
3.使用“”对字段原样输出调整如下:
CREATE OR REPLACE VIEW "ASSET"."ip_area_view"
AS
SELECT
bbb."id" ,
bbb."ip",
bbb."security_domain_name",
bbb."country",
bbb."area",
SUBSTRING (
SUBSTRING ( bbb."longitude_latitude" FROM 1 FOR POSITION ( ' ' IN bbb."longitude_latitude" ) - 1 )
FROM
POSITION ( '经' IN SUBSTRING ( bbb."longitude_latitude" FROM 1 FOR POSITION ( ' ' IN bbb."longitude_latitude" ) - 1 ) ) + 1
) AS "longitude",
SUBSTRING (
SUBSTRING ( bbb."longitude_latitude" FROM POSITION ( ' ' IN bbb."longitude_latitude" ) + 1 )
FROM
POSITION ( '纬' IN SUBSTRING ( bbb."longitude_latitude" FROM POSITION ( ' ' IN bbb."longitude_latitude" ) + 1 ) ) + 1
) AS "latitude",
bbb."is_intranet",
bbb."res_domain_id"
FROM
(
SELECT
md5( concat ( dev."dev_core_ipv4", sec."safe_name" ) ) AS "id",
dev."dev_core_ipv4" AS "ip",
CASE
WHEN sec."safe_name" IS NULL THEN
'未知安全域' ELSE sec."safe_name"
END AS "security_domain_name",
'中国' AS "country",
dev."dev_owner_area" AS "area",
CASE
WHEN org."longitude_latitude" IS NOT NULL THEN
org."longitude_latitude"
WHEN org."longitude_latitude" IS NULL
AND dev."dev_owner_area" IS NOT NULL THEN
( SELECT bbb_1."longitude_latitude" FROM "ASSET"."asset_device" aaa INNER JOIN "ASSET"."dmss_area" bbb_1 ON aaa."dev_owner_area" = bbb_1."area" LIMIT 1 ) ELSE NULL
END AS "longitude_latitude",
CASE
WHEN sec."safe_type" = 1 THEN
1
WHEN sec."safe_type" = 2 THEN
0 ELSE 2
END AS "is_intranet",
sec."res_domain_id" AS "res_domain_id"
FROM
"ASSET"."asset_device" dev
LEFT JOIN "ASSET"."asset_organization" org ON dev."org_id" = org."id"
LEFT JOIN "ASSET"."asset_safety_margin" sec ON dev."safety_margin_id" = sec."id"
) bbb;
这样就是全小写的呢,浏览数据如图:
记录一下