Forum Stats

  • 3,826,214 Users
  • 2,260,607 Discussions
  • 7,896,830 Comments

Discussions

Categories

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);


    · Share on TwitterShare on Facebook
  • 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);


    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    https://community.oracle.com/tech/developers/discussion/comment/16816004#Comment_16816004

    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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,048 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?

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,048 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;

    · Share on TwitterShare on Facebook