Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help with a regular expression

Status
Not open for further replies.

Beilstwh

Programmer
Jul 13, 2005
819
US
*** I didn't realize that this was a dead forum. I will ask my questions elsewhere ***


I am using the following match to search a sql script

if $ScanString =~ m/insert\s.+doc.+[\(|values|select|;]?/is;

It is returning true on the following code fragment. What I want is that if the table name of DOC follows the INSERT, but if it gets to any of "(", SELECT or ";" it will return false. In other words if the DOC is not there before I hit "(", SELECT or ";" it will always return false. The perl match from above is returning a false positive because the string "DOC_NUM" is in the attached select.

Any help would be appreciated.

The script fragment follows

INSERT INTO gl_trn
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);

Bill
Lead Application Developer
New York State, USA
 
No matter what forum you try regular expressions are probably not going to do what you want in a single pass. You will need to parse the document with a script that locates the first instance of the word THEN starts parsing from that point looking for the next instance of whatever word you use as the end point.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.
 
for me this regex seems to work:
Code:
/insert\s+into\s+\S*DOC\S*[ (]/i
 
Here is an example
beilstwh.pl
Code:
[COLOR=#800080]#!/usr/bin/perl[/color]
[COLOR=#a52a2a][b]use strict[/b][/color];
[COLOR=#a52a2a][b]use warnings[/b][/color];

[COLOR=#0000ff]# change record separator to SQL Statements seperator - i.e.: ";"[/color]
[COLOR=#a52a2a][b]local[/b][/color] [COLOR=#008b8b]$/[/color] = [COLOR=#ff00ff]"[/color][COLOR=#ff00ff];[/color][COLOR=#ff00ff]"[/color];

[COLOR=#a52a2a][b]my[/b][/color] [COLOR=#008b8b]$insert_table_name[/color] = [COLOR=#ff00ff]""[/color];

[COLOR=#a52a2a][b]while[/b][/color] ([COLOR=#a52a2a][b]my[/b][/color] [COLOR=#008b8b]$sql_stmt[/color]=[COLOR=#008b8b]<DATA>[/color]) {
    [COLOR=#a52a2a][b]chomp[/b][/color]([COLOR=#008b8b]$sql_stmt[/color]);
    [COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]s/[/b][/color][COLOR=#6a5acd]\n[/color][COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff] [/color][COLOR=#a52a2a][b]/g[/b][/color];
    [COLOR=#a52a2a][b]next[/b][/color] [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff]^[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]*[/color][COLOR=#ff00ff]$[/color][COLOR=#a52a2a][b]/[/b][/color]);

    [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#ff00ff]=== SQL Stalement #[/color][COLOR=#008b8b]$.[/color][COLOR=#ff00ff]:[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];
    [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#008b8b]$sql_stmt[/color][COLOR=#6a5acd]\n\n[/color][COLOR=#ff00ff]"[/color];

    [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff]insert[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]into[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+[/color][COLOR=#6a5acd]\S[/color][COLOR=#6a5acd]*[/color][COLOR=#ff00ff]DOC[/color][COLOR=#6a5acd]\S[/color][COLOR=#6a5acd]*[ (][/color][COLOR=#a52a2a][b]/i[/b][/color]) {
      [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]\t\t[/color][COLOR=#ff00ff]*** MATCHED !!! ***[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];
    }
}

[COLOR=#0000ff]__DATA__[/color]
[COLOR=#0000ff]INSERT INTO gl_trn[/color]
[COLOR=#0000ff]( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,[/color]
[COLOR=#0000ff]REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,[/color]
[COLOR=#0000ff]CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,[/color]
[COLOR=#0000ff]POST_ID_NUM, BNK_CO_CD)[/color]
[COLOR=#0000ff]SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),[/color]
[COLOR=#0000ff]'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),[/color]
[COLOR=#0000ff]decode(dc_cd, 'C', 'D',[/color]
[COLOR=#0000ff]'D', 'C'),[/color]
[COLOR=#0000ff]amt, TRUNC(SYSDATE), gl_trn_origin_cd,[/color]
[COLOR=#0000ff]NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),[/color]
[COLOR=#0000ff]'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,[/color]
[COLOR=#0000ff]DOC_NUM, POST_ID_NUM , BNK_CO_CD[/color]
[COLOR=#0000ff]FROM gl_trn[/color]
[COLOR=#0000ff]WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')[/color]
[COLOR=#0000ff]AND jrnl_cd = 'A'[/color]
[COLOR=#0000ff]AND co_cd = 'RAY'[/color]
[COLOR=#0000ff]AND NOT EXISTS (SELECT 'X'[/color]
[COLOR=#0000ff]FROM gl_reversed g[/color]
[COLOR=#0000ff]WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);[/color]

[COLOR=#0000ff]insert into MYDOCTAB(VAR1, VAR2, VAR3)[/color]
[COLOR=#0000ff]   values ('foo','bar','baz')[/color]
[COLOR=#0000ff];[/color]
[COLOR=#0000ff]insert into MYTABLE[/color]
[COLOR=#0000ff]  select * from DOCTAB t[/color]
[COLOR=#0000ff]where t.name = 'MYDOC'[/color]
[COLOR=#0000ff];[/color]
[COLOR=#0000ff]INSERT INTO gl_trn_DOC[/color]
[COLOR=#0000ff]( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,[/color]
[COLOR=#0000ff]REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,[/color]
[COLOR=#0000ff]CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,[/color]
[COLOR=#0000ff]POST_ID_NUM, BNK_CO_CD)[/color]
[COLOR=#0000ff]SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),[/color]
[COLOR=#0000ff]'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),[/color]
[COLOR=#0000ff]decode(dc_cd, 'C', 'D',[/color]
[COLOR=#0000ff]'D', 'C'),[/color]
[COLOR=#0000ff]amt, TRUNC(SYSDATE), gl_trn_origin_cd,[/color]
[COLOR=#0000ff]NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),[/color]
[COLOR=#0000ff]'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,[/color]
[COLOR=#0000ff]DOC_NUM, POST_ID_NUM , BNK_CO_CD[/color]
[COLOR=#0000ff]FROM gl_trn[/color]
[COLOR=#0000ff]WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')[/color]
[COLOR=#0000ff]AND jrnl_cd = 'A'[/color]
[COLOR=#0000ff]AND co_cd = 'RAY'[/color]
[COLOR=#0000ff]AND NOT EXISTS (SELECT 'X'[/color]
[COLOR=#0000ff]FROM gl_reversed g[/color]
[COLOR=#0000ff]WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);[/color]
[COLOR=#0000ff]select * from doc;[/color]

Output:
Code:
mikrom@mikrom-Lenovo-S500 ~/Work $ perl beilstwh.pl
=== SQL Stalement #1:
INSERT INTO gl_trn ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

=== SQL Stalement #2:
  insert into MYDOCTAB(VAR1, VAR2, VAR3)    values ('foo','bar','baz') 

		*** MATCHED !!! ***
=== SQL Stalement #3:
 insert into MYTABLE   select * from DOCTAB t where t.name = 'MYDOC' 

=== SQL Stalement #4:
 INSERT INTO gl_trn_DOC ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** MATCHED !!! ***
=== SQL Stalement #5:
 select * from doc
 
Other approach would be to extract table name from SQL-INSERT-statement and check if it contains the desired pattern.

I tried this:
beilstwh_02.pl
Code:
[COLOR=#800080]#!/usr/bin/perl[/color]
[COLOR=#a52a2a][b]use strict[/b][/color];
[COLOR=#a52a2a][b]use warnings[/b][/color];

[COLOR=#0000ff]# change record separator to SQL Statements seperator - i.e.: ";"[/color]
[COLOR=#a52a2a][b]local[/b][/color] [COLOR=#008b8b]$/[/color] = [COLOR=#ff00ff]"[/color][COLOR=#ff00ff];[/color][COLOR=#ff00ff]"[/color];

[COLOR=#a52a2a][b]my[/b][/color] [COLOR=#008b8b]$insert_table_name[/color] = [COLOR=#ff00ff]""[/color];

[COLOR=#a52a2a][b]while[/b][/color] ([COLOR=#a52a2a][b]my[/b][/color] [COLOR=#008b8b]$sql_stmt[/color]=[COLOR=#008b8b]<DATA>[/color]) {
    [COLOR=#a52a2a][b]chomp[/b][/color]([COLOR=#008b8b]$sql_stmt[/color]);
    [COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]s/[/b][/color][COLOR=#6a5acd]\n[/color][COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff] [/color][COLOR=#a52a2a][b]/g[/b][/color];
    [COLOR=#a52a2a][b]next[/b][/color] [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff]^[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]*[/color][COLOR=#ff00ff]$[/color][COLOR=#a52a2a][b]/[/b][/color]);

    [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#ff00ff]=== SQL Stalement #[/color][COLOR=#008b8b]$.[/color][COLOR=#ff00ff]:[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];
    [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#008b8b]$sql_stmt[/color][COLOR=#6a5acd]\n\n[/color][COLOR=#ff00ff]"[/color];

    [COLOR=#0000ff]# if it's INSERT statement then extract table name[/color]
    [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#008b8b]$sql_stmt[/color] =~ [COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff]insert[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]into[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+([/color][COLOR=#6a5acd]\w[/color][COLOR=#6a5acd]+)[ (][/color][COLOR=#a52a2a][b]/i[/b][/color]) {
       [COLOR=#a52a2a][b]my[/b][/color] [COLOR=#008b8b]$insert_table_name[/color] = [COLOR=#a52a2a][b]uc[/b][/color]([COLOR=#008b8b]$1[/color]);
       [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]\t\t[/color][COLOR=#ff00ff]*** INSERT into table: '[/color][COLOR=#008b8b]$insert_table_name[/color][COLOR=#ff00ff]'[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];
       [COLOR=#0000ff]# look at table name [/color]
       [COLOR=#a52a2a][b]if[/b][/color]  ([COLOR=#008b8b]$insert_table_name[/color] =~ [COLOR=#a52a2a][b]/[/b][/color][COLOR=#ff00ff]DOC[/color][COLOR=#a52a2a][b]/i[/b][/color]) {
         [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]\t\t[/color][COLOR=#ff00ff]*** MATCHED !!! ***[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];       
       }
    }
    [COLOR=#a52a2a][b]else[/b][/color] {
       [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]\t\t[/color][COLOR=#ff00ff]*** not an INSERT statement ![/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];
    } 
}

[COLOR=#0000ff]__DATA__[/color]
[COLOR=#0000ff]INSERT INTO gl_trn[/color]
[COLOR=#0000ff]( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,[/color]
[COLOR=#0000ff]REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,[/color]
[COLOR=#0000ff]CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,[/color]
[COLOR=#0000ff]POST_ID_NUM, BNK_CO_CD)[/color]
[COLOR=#0000ff]SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),[/color]
[COLOR=#0000ff]'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),[/color]
[COLOR=#0000ff]decode(dc_cd, 'C', 'D',[/color]
[COLOR=#0000ff]'D', 'C'),[/color]
[COLOR=#0000ff]amt, TRUNC(SYSDATE), gl_trn_origin_cd,[/color]
[COLOR=#0000ff]NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),[/color]
[COLOR=#0000ff]'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,[/color]
[COLOR=#0000ff]DOC_NUM, POST_ID_NUM , BNK_CO_CD[/color]
[COLOR=#0000ff]FROM gl_trn[/color]
[COLOR=#0000ff]WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')[/color]
[COLOR=#0000ff]AND jrnl_cd = 'A'[/color]
[COLOR=#0000ff]AND co_cd = 'RAY'[/color]
[COLOR=#0000ff]AND NOT EXISTS (SELECT 'X'[/color]
[COLOR=#0000ff]FROM gl_reversed g[/color]
[COLOR=#0000ff]WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);[/color]

[COLOR=#0000ff]insert into MYDOCTAB(VAR1, VAR2, VAR3)[/color]
[COLOR=#0000ff]   values ('foo','bar','baz')[/color]
[COLOR=#0000ff];[/color]
[COLOR=#0000ff]insert into MYTABLE[/color]
[COLOR=#0000ff]  select * from DOCTAB t[/color]
[COLOR=#0000ff]where t.name = 'MYDOC'[/color]
[COLOR=#0000ff];[/color]
[COLOR=#0000ff]INSERT INTO gl_trn_DOC[/color]
[COLOR=#0000ff]( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,[/color]
[COLOR=#0000ff]REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,[/color]
[COLOR=#0000ff]CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,[/color]
[COLOR=#0000ff]POST_ID_NUM, BNK_CO_CD)[/color]
[COLOR=#0000ff]SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),[/color]
[COLOR=#0000ff]'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),[/color]
[COLOR=#0000ff]decode(dc_cd, 'C', 'D',[/color]
[COLOR=#0000ff]'D', 'C'),[/color]
[COLOR=#0000ff]amt, TRUNC(SYSDATE), gl_trn_origin_cd,[/color]
[COLOR=#0000ff]NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),[/color]
[COLOR=#0000ff]'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,[/color]
[COLOR=#0000ff]DOC_NUM, POST_ID_NUM , BNK_CO_CD[/color]
[COLOR=#0000ff]FROM gl_trn[/color]
[COLOR=#0000ff]WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')[/color]
[COLOR=#0000ff]AND jrnl_cd = 'A'[/color]
[COLOR=#0000ff]AND co_cd = 'RAY'[/color]
[COLOR=#0000ff]AND NOT EXISTS (SELECT 'X'[/color]
[COLOR=#0000ff]FROM gl_reversed g[/color]
[COLOR=#0000ff]WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);[/color]
[COLOR=#0000ff]select * from doc;[/color]

Output:
Code:
mikrom@mikrom-Lenovo-S500 ~/Work $ perl beilstwh_02.pl
=== SQL Stalement #1:
INSERT INTO gl_trn ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** INSERT into table: 'GL_TRN'
=== SQL Stalement #2:
  insert into MYDOCTAB(VAR1, VAR2, VAR3)    values ('foo','bar','baz') 

		*** INSERT into table: 'MYDOCTAB'
		*** MATCHED !!! ***
=== SQL Stalement #3:
 insert into MYTABLE   select * from DOCTAB t where t.name = 'MYDOC' 

		*** INSERT into table: 'MYTABLE'
=== SQL Stalement #4:
 INSERT INTO gl_trn_DOC ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** INSERT into table: 'GL_TRN_DOC'
		*** MATCHED !!! ***
=== SQL Stalement #5:
 select * from doc

		*** not an INSERT statement !
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top