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!

Register Log in
  • Congratulations TouchToneTommy 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

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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
for me this regex seems to work:
Code: 
/insert\s+into\s+\S*DOC\S*[ (]/i
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 !
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bob6739
  • Locked
  • Question
Perl Beginner - Need Help
Replies
6
Views
203
feherke
feherke
rob54321
  • Locked
  • Question
Perl how to read line from file with interpolation 1
Replies
6
Views
324
rob54321
rob54321
kokch
  • Locked
  • Question
Need assistance with XML- Sort/Grouping (alpha sort; hide without groups)
Replies
1
Views
119
mikrom
mikrom
Dino_1000
  • Locked
  • Question
Use the perl debugger to dump all the data in a string variable
Replies
0
Views
144
Dino_1000
Dino_1000
dan
  • Locked
  • Question
add a variable to subject in formmail.pl
Replies
6
Views
197
feherke
feherke

Part and Inventory Search

Sponsor

Back
Top