Forum Stats

  • 3,827,423 Users
  • 2,260,772 Discussions
  • 7,897,237 Comments

Discussions

aggregate functions not allowed in where clause

2»

Answers

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    create TABLE FA.FA_DEPRN_SUMMARY2 
    (
     BOOK_TYPE_CODE VARCHAR2(15 BYTE) NOT NULL 
    , ASSET_ID NUMBER(15, 0) NOT NULL 
    , DEPRN_SOURCE_CODE VARCHAR2(15 BYTE) NOT NULL 
    , PERIOD_COUNTER NUMBER(15, 0) NOT NULL 
    ) 
    
    CREATE TABLE FA_DEPRN_DETAIL2 
    (
     BOOK_TYPE_CODE VARCHAR2(15 BYTE) NOT NULL 
    , ASSET_ID NUMBER(15, 0) NOT NULL 
    , PERIOD_COUNTER NUMBER(15, 0) NOT NULL 
    ) 
    ;
    

    SELECT version FROM V$INSTANCE: 12.1.0.2

    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40310,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40311,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40312,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40313,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40314,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40315,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40316,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40317,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40318,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40319,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40320,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40321,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40322,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40323,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40324,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40325,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40326,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40327,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40328,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40330,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40331,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40332,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40333,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40334,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40335,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40336,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40337,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40338,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40339,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40340,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40341,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40342,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40343,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40345,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40346,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40347,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40348,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40349,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40350,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40351,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40352,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40353,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40354,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40355,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40356,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40357,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40358,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40359,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40360,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40361,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40362,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40363,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40364,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40365,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40366,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40367,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40368,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40369,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40370,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40371,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40372,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40373,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40374,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40375,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40376,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40377,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40378,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40379,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40380,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40381,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40382,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40383,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40384,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40385,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40386,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40387,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40388,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40389,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40390,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40391,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40392,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40393,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40394,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40395,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40396,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40397,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40398,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40399,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40400,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40401,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40402,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40403,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40404,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40405,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40406,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40408,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40409,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40410,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40411,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40412,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40413,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40414,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40415,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40417,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40418,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40419,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40420,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40421,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40422,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40423,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40424,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40425,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40426,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40427,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40428,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40429,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40430,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40431,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40432,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40433,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40434,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40435,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40436,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40437,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40440,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40441,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40442,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40443,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40444,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40445,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40446,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40447,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40448,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40449,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40450,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40451,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40452,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40453,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40454,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40455,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40456,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40457,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40458,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40459,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40460,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40461,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40462,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40463,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40464,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40465,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40466,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40467,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40468,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40469,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40470,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40471,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40472,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40473,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40474,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40475,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40476,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40477,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40478,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40480,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40481,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40482,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40483,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40484,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40485,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40486,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40487,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40488,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40489,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40490,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40491,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40492,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40493,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40494,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40495,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40496,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40497,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40498,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40499,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40500,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40501,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40502,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40503,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40504,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40505,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40506,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40507,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40508,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40509,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40510,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40511,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40512,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40513,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40514,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40515,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40515,24140);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40310,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40311,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40312,24139);
    Insert into FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40313,24139);
    
    
    


  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    REM INSERTING into FA_DEPRN_SUMMARY2
    SET DEFINE OFF;
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('MTL CORP BOOK',40310,'DEPRN',24139);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40000,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40076,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40077,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40078,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40079,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40080,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40081,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40082,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40083,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40084,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40085,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40086,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40087,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40088,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40089,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40090,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40091,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40092,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40093,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40094,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40095,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40096,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40097,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40098,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40099,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40100,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40101,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40102,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40103,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40104,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40105,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40106,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40107,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40110,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40111,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40112,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40113,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40115,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40116,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40117,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40118,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40119,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40120,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40121,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40122,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40123,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40124,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40125,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40126,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40127,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40128,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40129,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40130,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40131,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40132,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40133,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40134,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40135,' DEPRN ',24140);
    Insert into FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40136,' DEPRN ',24140);
    


  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    hI @Frank Kulash

    I sent you the DDL and insert statements. The result for the original subquery is shown below.

    Can you please help me on how to create the view and join

    Original query with sub query

    select DS.BOOK_TYPE_CODE,DS.ASSET_ID from 
    FA.FA_DEPRN_DETAIL2 DD,
    FA.FA_DEPRN_SUMMARY2 DS WHERE DS.PERIOD_COUNTER =
              (SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter)
               FROM FA.FA_DEPRN_SUMMARY2 DS_SUB
               WHERE   DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
                  AND DS_SUB.ASSET_ID = DD.ASSET_ID
                  AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN') ;
         
    

    Expected result:

    BOOK_TYPE_CODE,ASSET_ID

    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    MTL CORP BOOK	40310
    CSL CORP BOOK	40000
    CSL CORP BOOK	40076
    CSL CORP BOOK	40077
    CSL CORP BOOK	40078
    CSL CORP BOOK	40079
    CSL CORP BOOK	40080
    CSL CORP BOOK	40081
    CSL CORP BOOK	40082
    CSL CORP BOOK	40083
    CSL CORP BOOK	40084
    CSL CORP BOOK	40085
    CSL CORP BOOK	40086
    CSL CORP BOOK	40087
    CSL CORP BOOK	40088
    CSL CORP BOOK	40089
    CSL CORP BOOK	40090
    CSL CORP BOOK	40091
    CSL CORP BOOK	40092
    CSL CORP BOOK	40093
    CSL CORP BOOK	40094
    CSL CORP BOOK	40095
    CSL CORP BOOK	40096
    CSL CORP BOOK	40097
    CSL CORP BOOK	40098
    CSL CORP BOOK	40099
    CSL CORP BOOK	40100
    CSL CORP BOOK	40101
    CSL CORP BOOK	40102
    CSL CORP BOOK	40103
    CSL CORP BOOK	40104
    CSL CORP BOOK	40105
    CSL CORP BOOK	40106
    CSL CORP BOOK	40107
    CSL CORP BOOK	40110
    CSL CORP BOOK	40111
    CSL CORP BOOK	40112
    CSL CORP BOOK	40113
    CSL CORP BOOK	40115
    CSL CORP BOOK	40116
    CSL CORP BOOK	40117
    CSL CORP BOOK	40118
    CSL CORP BOOK	40119
    CSL CORP BOOK	40120
    CSL CORP BOOK	40121
    CSL CORP BOOK	40122
    CSL CORP BOOK	40123
    CSL CORP BOOK	40124
    CSL CORP BOOK	40125
    CSL CORP BOOK	40126
    CSL CORP BOOK	40127
    CSL CORP BOOK	40128
    CSL CORP BOOK	40129
    CSL CORP BOOK	40130
    CSL CORP BOOK	40131
    CSL CORP BOOK	40132
    CSL CORP BOOK	40133
    CSL CORP BOOK	40134
    CSL CORP BOOK	40135
    CSL CORP BOOK	40136
    
  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    Like you mentioned above, use aggregate function in sub-query and use the value it returns in the WHERE clause of a super query. My goal is to convert this sub query to a view and join this view to the WHERE clause of the super-query.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,071 Red Diamond

    Hi, @Robeen

    I sent you the DDL and insert statements.

    Thanks. You still need to post an explanation of how you get the desired results from the sample data; then we can begin. What does each row in the view represent? How is each column derived?

    It would be a lot easier for you to post (and for everyone else to understand) if you had much less sample data, maybe 5 or 10 rows in each table.

    The result for the original subquery is shown below.

    Is that the same as the contents of the view that you want to create? If so, you can simply put CREATE OR REPLACE VIEW view_name AS immediately before the SELECT keyword.

    If those are not the contents of the view you want to create, then what are?

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    edited Nov 17, 2021 6:02PM

    Hi @Frank Kulash

    please find below simplified DML for each table:

    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK1',40310,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK2',40310,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK3',40310,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40313,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40314,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40315,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40316,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40317,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK2',40318,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40319,24139);
    Insert into FA.FA_DEPRN_DETAIL2 (BOOK_TYPE_CODE,ASSET_ID,PERIOD_COUNTER) values ('MTL CORP BOOK',40320,24139);
    
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('MTL CORP BOOK',40310,'DEPRN',24139);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40320,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40076,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODOutput oE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40077,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40318,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40079,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40080,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40081,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40082,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40083,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40084,' DEPRN ',24140);
    Insert into FA.FA_DEPRN_SUMMARY2 (BOOK_TYPE_CODE,ASSET_ID,DEPRN_SOURCE_CODE,PERIOD_COUNTER) values ('CSL CORP BOOK',40085,' DEPRN ',24140);
    

    Output is shown below:

    BOOK_TYPE_CODE,ASSET_ID BOOK_TYPE_CODE_1

    MTL CORP BOOK	40310	MTL CORP BOOK1
    MTL CORP BOOK	40310	MTL CORP BOOK2
    MTL CORP BOOK	40310	MTL CORP BOOK3
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK2
    MTL CORP BOOK	40310	MTL CORP BOOK
    MTL CORP BOOK	40310	MTL CORP BOOK
    

    I got the above desired result from the original query by joining DS.PERIOD_COUNTER with the NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) from the sub select.

    select DS.BOOK_TYPE_CODE,DS.ASSET_ID,DD.BOOK_TYPE_CODE from 
    FA.FA_DEPRN_DETAIL2 DD,
    FA.FA_DEPRN_SUMMARY2 DS WHERE DS.PERIOD_COUNTER =
              (SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter)
               FROM FA.FA_DEPRN_SUMMARY2 DS_SUB
               WHERE   DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
                  AND DS_SUB.ASSET_ID = DD.ASSET_ID
                  AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN') ;
    


    The view will contain

              create view vdd as (SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd_sub.period_counter) pcounter,DD_SUB.BOOK_TYPE_CODE,DD_SUB.ASSET_ID
    
    
               FROM FA.FA_DEPRN_SUMMARY2 DS_SUB,FA.FA_DEPRN_DETAIL2 DD_SUB
    
    
               WHERE   --DS_SUB.BOOK_TYPE_CODE = DD_SUB.BOOK_TYPE_CODE
    
    
                  --AND DS_SUB.ASSET_ID = DD_SUB.ASSET_ID
    
    
                  --AND 
                  DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN'
    
    
    group by dd_sub.period_counter,DD_SUB.BOOK_TYPE_CODE,DD_SUB.ASSET_ID);
    

    But if I join with super-query it returns only 1 record.

    select DS.BOOK_TYPE_CODE,DS.ASSET_ID from vdd,FA.FA_DEPRN_SUMMARY2 DS where vdd.pcounter=ds.period_counter;
    

    I got the answer by doing a LEFT JOIN of view 'VDD' and 'DS'

    select DS.BOOK_TYPE_CODE,DS.ASSET_ID,vdd.book_type_code from vdd left join FA.FA_DEPRN_SUMMARY2 DS ON (VDD.pcounter=ds.period_counter)
    

    Now both initial query and view return same records.


    MTL CORP BOOK1	40310	MTL CORP BOOK3
    MTL CORP BOOK1	40310	MTL CORP BOOK2
    MTL CORP BOOK1	40310	MTL CORP BOOK1
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	CSL CORP BOOK
    
    
    
    MTL CORP BOOK1	40310	MTL CORP BOOK1
    MTL CORP BOOK1	40310	MTL CORP BOOK2
    MTL CORP BOOK1	40310	MTL CORP BOOK3
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	CSL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    
  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    Why does left join vdd on DS returns the correct result (11 records) while the reverse DS left join on vdd returns 22 records? Normally it should be the super-query left join on the sub-query?

    MTL CORP BOOK1	40310	MTL CORP BOOK1
    MTL CORP BOOK1	40310	CSL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK2
    MTL CORP BOOK1	40310	MTL CORP BOOK3
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    MTL CORP BOOK1	40310	MTL CORP BOOK
    CSL CORP BOOK	40085	
    CSL CORP BOOK	40084	
    CSL CORP BOOK	40083	
    CSL CORP BOOK	40082	
    CSL CORP BOOK	40081	
    CSL CORP BOOK	40080	
    CSL CORP BOOK	40079	
    CSL CORP BOOK	40318	
    CSL CORP BOOK	40077	
    CSL CORP BOOK	40076	
    CSL CORP BOOK	40320	
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,071 Red Diamond
    Answer ✓

    Hi, @Robeen

    One more time: Explain what you're trying to do. You've posted some examples of queries that (apparently) do it, but you still haven't explained exactly what that is. What does each row in the desired result set represent? How do you figure the values in each column?

    It sounds like you're trying to use some front-end tool that doesn't support sub-queries, or at least certain types of sub-queries that you want to use. That's a shame: front-end tools are supposed to add functionality to Oracle, not remove it. Anyway, it seems like anything you want to do using a sub-query can be done with a view in the back end. Create a view using sub-queries or whatever other features Oracle supports in the database; then, in the front end simply say  SELECT * FROM the_view;