Re^5: Query Does not work in Spreadsheet::WriteExcel

Hi Monks, just wanted to follow up with what i did to get this to finally work. I don't really understand why i had to do it, but maybe one of the Monks will.

The issue is that in an Oracle client (TOAD for example) the following col of a select statement worked, but within perl using DBI it did not. Ultimately, i had to escape the backslash character....so \W+...became....\\W+

From:

regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTINFO)\W+(:95[[:alnum:]]*)*\W+:
+97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACCTINFO',1,1,'n') as accnt_inf
+o,
[download]

To:

regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTINFO)\\W+(:95[[:alnum:]]*)*\\W
++:97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACCTINFO',1,1,'n') as accnt_i
+nfo,
[download]

After making that change it worked inside perl.

thanks for the help!

Comment on Re^5: Query Does not work in Spreadsheet::WriteExcel Select or Download Code

Replies are listed 'Best First'.
Re^6: Query Does not work in Spreadsheet::WriteExcel by choroba (Cardinal) on Jul 24, 2020 at 19:21 UTC
Did you use double quotes in Perl to store the sql query? `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l]
Re^7: Query Does not work in Spreadsheet::WriteExcel by dirtdog (Monk) on Jul 24, 2020 at 21:03 UTC
No. I did not use any quotes to store the query.	[reply]
Re^8: Query Does not work in Spreadsheet::WriteExcel by AnomalousMonk (Archbishop) on Jul 25, 2020 at 02:39 UTC
How then did you generate the query string in Perl? Did you use a here-doc? Some variations of here-doc handle escapes (and interpolation). I think the point choroba is making is that a double-quote string constructor processes escapes (and also interpolates): `c:\@Work\Perl\monks>perl -wMstrict -le "my $sq = '\Quick and \Easy \Upper \Limit \\UTTER \\LAUGH \Wow'; print qq{>$sq<}; " >\Quick and \Easy \Upper \Limit \UTTER \LAUGH \Wow< c:\@Work\Perl\monks>perl -wMstrict -le "my $dq = qq{\Quick and \Easy \Upper \Limit \\UTTER \\LAUGH \Wow}; print qq{>$dq<}; " Unrecognized escape \W passed through at -e line 1. >uick\ and\ asy PPER imit \utter \laugh wow<` [download] (Do you have warnings enabled?) Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]


P is for Practical
	PerlMonks