- 论坛徽章:
- 0
|
在一个SQL文件中取出所有相关的表名,sql非常复杂,带不定层的嵌套语句。
写了个这样的,但遇到嵌套的就不正常,哪位大侠指导一下!
- sed -n -e '
- /[Pp]rompt/ d
- /^$/ d
- s/^[ \t]\{1,\}//g
- s/[ \t]\{1,\}$//g
- s/--.*//g
- /^\/\*/ d
- /^\*/ d
- /^\\\*/ d
- s/[\(\)]/ /g
- s/[\n]/\t/g
- /[Ii]nsert/ s/[Ii]nsert [Ii]nto\(.*\)/\1/p
- /[Ff]rom/ s/[Ff]rom \(.*\) [Ww]here/\1/p
- /[Ff]rom/ s/[Ff]rom \(.*\) [Mm]inus/\1/p
- /[Uu]date/ s/[Uu]pdate \(.*\) [Ss]et/\1/p
- ' sqlfile
复制代码
$ cat sqlfile
.....
.....
Insert Into Asset_Classifications_Dc025_St
(Asst_Id,
Audit_Skeleton_Ind,
Audit_Valid_From_Date,
Audit_Valid_To_Date,
Legal_Entity_Code,
Id,
Rac_Id,
Rtp_Code_Archived_By,
Rtp_Code_Created_By,
Effective_From,
Effective_To,
Ract_Code,
Rac_Code)
Select Distinct
ac.Asst_Id,
Nvl(ac.Audit_Skeleton_Ind, 'N'),
ac.Audit_Valid_From_Date,
To_Date('9999-12-31', 'yyyy-mm-dd'),
ac.Booking_Center,
ac.Id,
Decode(Rac.Code, 'PUT', (Select b.ID From ASSET_CLASS_DC025_TEMP_USE b Where b.CODE = '029' and b.LEGAL_ENTITY_CODE = ac.BOOKING_CENTER),
'CALL', (Select b.ID From ASSET_CLASS_DC025_TEMP_USE b Where b.CODE = '030' and b.LEGAL_ENTITY_CODE = ac.BOOKING_CENTER),
ac.Rac_ID),
ac.Rtp_Code_Archived_By,
ac.Rtp_Code_Created_By,
ac.Audit_Valid_From_Date,
Decode(ac.Audit_Current_Ind,
'Y',
To_Date('9999-12-31', 'YYYY-MM-DD'),
ac.Audit_Valid_To_Date),
Case When (In_Dc_Recon_Ind = 'Y' and Ract.Code in ('FXOPT', 'MH', 'OPTYPE')) Then 'SECTYPE' Else Ract.Code End,
Decode(Rac.Code, 'PUT', '029', 'CALL', '030', Rac.Code)
From Asset_Classifications_V131 Ac,
(Select code, booking_center, id, ract_id
From (Select code, booking_center, id, ract_id,
Row_Number() Over(Partition By booking_center, id Order By code) Rn
From Ref_Asset_Classifications_V131
Where Audit_Current_Ind = 'Y')
Where Rn = 1) rac,
(Select code, booking_center, id
From (Select code, booking_center, id,
Row_Number() Over(Partition By booking_center, id Order By code) Rn
From Ref_Asset_Class_Type_V131
Where Audit_Current_Ind = 'Y')
Where Rn = 1) ract
Where ac.booking_center = rac.booking_center(+)
and rac.booking_center = ract.booking_center(+)
and ac.rac_id = rac.id(+)
and rac.ract_id = ract.id (+)
and NOT EXISTS
(select 1
from Ref_Asset_Classifications_V131 rac, Ref_Asset_Class_Type_V131 ract
where rac.ract_id = ract.id
and rac.audit_current_ind = 'Y'
and ract.audit_current_ind = 'Y'
AND ract.code = 'SYMBOL'
and rac.id = ac.rac_id)
and ac.Audit_Valid_From_Date <>
Nvl(ac.Audit_Valid_To_Date,
To_Date('9999-12-31', 'YYYY-MM-DD'))
And ((In_Dc_Recon_Ind = 'N' And
ac.Audit_Valid_From_Date <= In_Rpt_Date And
Nvl(ac.Audit_Valid_To_Date,
To_Date('9999-12-31', 'YYYY-MM-DD')) > In_Rpt_Date) Or
(In_Dc_Recon_Ind = 'Y'));
Commit;
......
..... |
|