sql - 使用联合时如何检索嵌套表

Question

我正在尝试从 sql 视图中的嵌套表中检索数据，但是当我想添加一个联合时，oracle 说 inconsistent data type.）。

当我做 ：

    CREATE OR REPLACE FORCE VIEW MY_VIEW
(
    VAR_1,
    VAR_2,
    VAR_3,
    VAR_4,
    VAR_5
)
    BEQUEATH DEFINER
AS
    SELECT 
           '0-0'            CHILD_ENTITY_ID,
           NULL             PARENT_ENTITY_ID,
           T2.LEVEL_ID      ENTITY_ID,
           NULL             PARENT_LEVEL_TYPE_ID,
          (SELECT ALERT_DAYS FROM TABLE(T6.ALERT_DAYS)) ALERT_DAYS
      FROM MA_TABLE_1    T1,
           MA_TABLE_2    T2,
           MA_TABLE_3    T3,
           MA_TABLE_4    T4,
           MA_TABLE_5    T5,
           MA_TABLE_6    T6,
           MA_TABLE_7    T7
     WHERE     T1.LEVEL_TYPE_ID = 0
           AND T1.LEVEL_TYPE_ID = T2.LEVEL_TYPE_ID
           AND T4.RATE_ID = T2.RATE_ID
           AND T4.COUNTER_DIVIDED_ID = T5.COUNTER_ID
           AND T2.ALERT_ID = T6.ALERT_ID(+)
           AND T2.RATE_ID = T7.RATE_ID
    UNION
    SELECT 
           '1-' || T2.OPERATOR_ID CHILD_ENTITY_ID,
           '0-0'                  PARENT_ENTITY_ID,
           T2.OPERATOR_ID         ENTITY_ID,
           0                      PARENT_LEVEL_TYPE_ID,
          (SELECT ALERT_DAYS FROM TABLE(T6.ALERT_DAYS)) ALERT_DAYS
      FROM MA_TABLE_3             T2,
           MA_TABLE_2             T3,
           MA_TABLE_4             T4,
           MA_TABLE_5             T5,
           MA_TABLE_6             T6,
           MA_TABLE_7             T7
     WHERE     ((T3.LEVEL_TYPE_ID = 1 AND T3.LEVEL_ID = T2.OPERATOR_ID))
           AND T3.LEVEL_TYPE_ID =
                   (SELECT MAX (T4.LEVEL_TYPE_ID)
                      FROM SMS_MONITORING_THRESHOLD T4
                     WHERE ((T4.LEVEL_TYPE_ID = 1
                           AND T4.LEVEL_ID = T2.OPERATOR_ID))
                           AND T4.RATE_ID = T3.RATE_ID
                           AND T3.CHANNEL_ID = T4.CHANNEL_ID)
           AND T4.RATE_ID = T3.RATE_ID
           AND T4.COUNTER_DIVIDED_ID = T5.COUNTER_ID
           AND T3.ALERT_ID = T6.ALERT_ID(+)
           AND T3.RATE_ID = T7.RATE_ID;

我有一个错误说“错误的数据类型”。

我也尝试添加 to_char(...ALERT_DAYS) 但没有成功。

我成功避免了此更新的错误：

    CREATE OR REPLACE FORCE VIEW MY_VIEW
(
    VAR_1,
    VAR_2,
    VAR_3,
    VAR_4,
    VAR_5
)
    BEQUEATH DEFINER
AS
    SELECT 
           '0-0'            CHILD_ENTITY_ID,
           NULL             PARENT_ENTITY_ID,
           T2.LEVEL_ID      ENTITY_ID,
           NULL             PARENT_LEVEL_TYPE_ID,
          T8.*
      FROM MA_TABLE_1    T1,
           MA_TABLE_2    T2,
           MA_TABLE_3    T3,
           MA_TABLE_4    T4,
           MA_TABLE_5    T5,
           MA_TABLE_6    T6,
           MA_TABLE_7    T7,
           TABLE(T2.ALERT_DAYS) T8
     WHERE     T1.LEVEL_TYPE_ID = 0
           AND T1.LEVEL_TYPE_ID = T2.LEVEL_TYPE_ID
           AND T4.RATE_ID = T2.RATE_ID
           AND T4.COUNTER_DIVIDED_ID = T5.COUNTER_ID
           AND T2.ALERT_ID = T6.ALERT_ID(+)
           AND T2.RATE_ID = T7.RATE_ID
    UNION
    SELECT 
           '1-' || T2.OPERATOR_ID CHILD_ENTITY_ID,
           '0-0'                  PARENT_ENTITY_ID,
           T2.OPERATOR_ID         ENTITY_ID,
           0                      PARENT_LEVEL_TYPE_ID,
          T8.*
      FROM MA_TABLE_3             T2,
           MA_TABLE_2             T3,
           MA_TABLE_4             T4,
           MA_TABLE_5             T5,
           MA_TABLE_6             T6,
           MA_TABLE_7             T7,
           TABLE(T3.ALERT_DAYS) T8
     WHERE     ((T3.LEVEL_TYPE_ID = 1 AND T3.LEVEL_ID = T2.OPERATOR_ID))
           AND T3.LEVEL_TYPE_ID =
                   (SELECT MAX (T4.LEVEL_TYPE_ID)
                      FROM SMS_MONITORING_THRESHOLD T4
                     WHERE ((T4.LEVEL_TYPE_ID = 1
                           AND T4.LEVEL_ID = T2.OPERATOR_ID))
                           AND T4.RATE_ID = T3.RATE_ID
                           AND T3.CHANNEL_ID = T4.CHANNEL_ID)
           AND T4.RATE_ID = T3.RATE_ID
           AND T4.COUNTER_DIVIDED_ID = T5.COUNTER_ID
           AND T3.ALERT_ID = T6.ALERT_ID(+)
           AND T3.RATE_ID = T7.RATE_ID;

但是现在我的观点没有返回任何东西，我不明白为什么，如果您有任何想法，请告诉我

标签： sqloracleunionnested-table

Answer 1

你可以试试这个

CREATE OR REPLACE FORCE VIEW MY_VIEW 
(
    ID,
    ALERT_DAYS
)
   BEQUEATH DEFINER
AS
    SELECT
         T1.ID,
         SELECT ALERT_DAYS FROM TABLE(to_number(T1.ALERT_DAYS)) ALERT_DAYS
    FROM 
        MA_TABLE T1
   UNION
    SELECT
         T1.ID,
         SELECT ALERT_DAYS FROM TABLE(to_number(T1.ALERT_DAYS)) ALERT_DAYS
    FROM 
        MA_TABLE T1