Re^2: Interpolating subroutine call in SQL INSERT INTO SELECT statement

Replies are listed 'Best First'.
Re^3: Interpolating subroutine call in SQL INSERT INTO SELECT statement by 1nickt (Canon) on Aug 27, 2015 at 13:42 UTC
Even a broken clock is right once every 86400 seconds. But did you read and follow the other advice given in response to your question, where a number of experienced programmers explained how you can create a function in your SQL? The way forward always starts with a minimal test.	[reply] [d/l]
Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement by chacham (Prior) on Aug 27, 2015 at 17:01 UTC
Even a broken clock is right once every 86400 seconds. I appreciate your attempt to paraphrase Lewis Carroll's "The Two Clocks". Though, i see attribution to Marie von Ebner-Eschenbach as well. The idea is, it is correct twice every day, assuming a 12-hour clock. However, due to the leap second a "day" might take 86,401 seconds. Therefore, while correct twice a day, it may only be once in any given 86,400 seconds.	[reply]
Re^5: Interpolating subroutine call in SQL INSERT INTO SELECT statement by 1nickt (Canon) on Aug 28, 2015 at 06:33 UTC
I use the 24-hour clock on my wristwatch and all my clocks ... now do you, as a Perl programmer, know off the top of your head how many seconds are in 12 hours? Probably not, because it is a useless unit! But I bet you knew 86400 seconds in a day :-) As for leap seconds; ugh. `perl -MDateTime -E' say DateTime->new(time_zone=>"UTC",year=>1972,month=>12,day=>31,hour=> +23,minute=>59,second=>60)->add(months=>$_)->month_name for (0,1); ' December February` [download] ... we escaped this year because July is longer than June (hmm, I wonder if that's why they moved it to June?): `perl -MDateTime -E' say DateTime->new(time_zone=>"UTC",year=>2015,month=>6,day=>30,hour=>2 +3,minute=>59,second=>60)->add(months=>$_)->month_name for (0,1); ' June July` [download] The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re^6: Interpolating subroutine call in SQL INSERT INTO SELECT statement by chacham (Prior) on Aug 28, 2015 at 14:16 UTC
Re^7: Interpolating subroutine call in SQL INSERT INTO SELECT statement by 1nickt (Canon) on Sep 01, 2015 at 16:45 UTC
Re^3: Interpolating subroutine call in SQL INSERT INTO SELECT statement by chacham (Prior) on Aug 27, 2015 at 16:37 UTC
if there was a clever way to intersperse/embed my function within the INSERT INTO/SELECT construct That depends on what you want done and what language the function would be in. What exactly are you trying to do?	[reply]
Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement by shadowsong (Pilgrim) on Aug 28, 2015 at 09:16 UTC
chacham, What I was trying ascertain was whether I could invoke my perl function (at the end of this comment) within the midst of the SQL INSERT INTO/SELECT FROM statement. Now, the following responses from other monks confirmed my suspicions... You might be able to do simple interpolation using nothing but SQL inline with a few subqueries and some trivial math. If you truley need a perl function to do the job, you'll have to do a select in one call. And then prep an insert data set for a second call. KurtSchwind The very-short (but definitive) answer is ... “no.” And here’s why: “any ... SQL server has no idea who-or-what its client is ... nor does it care.” The server knows nothing about you ... therefore, nothing about your programming language (or even that you are using one ...) ... therefore, nothing about your “functions.” It has no ability,whatsoever, to “call them.” If you desire to “do something fancy,” then you must provide all of the code to do it. You must issue a SELECT statement, and, for each row returned, create and issue appropriate-to-you INSERTs, probably within a transaction. sundialsvc4 I also considered this from 1nickt... Even a broken clock is right once every 86400 seconds. But did you read and follow the other advice given in response to your question, where a number of experienced programmers explained how you can create a function in your SQL? Vicar poj was also very kind to provide examples of how to create SQL functions in perl and use them. So I believe I'm now straight on what I can and can't do... Here's the function I coded in perl to modify COLUMN3 before inserting it back into TABLE1. sub convert { ### This subroutine accepts a single parameter (a scalar which it +treats as a string) ### and sequentially substitutes out "invalid" characters with the +ir "valid" counterparts ### based on a fixed list of characters to replace. my $input_stream = shift; return undef unless $input_stream; my %char_swap_hash = ( 1 => {OLD => '&[^(amp;)\|(lt;)\|(gt;)]', NEW => '&'}, # don' +t match legit '&' codes 2 => {OLD => '<', NEW => '<'}, 3 => {OLD => '>', NEW => '>'} ); $input_stream =~ s/$char_swap_hash{$_}{OLD}/$char_swap_hash{$_}{NE +W}/g for keys %char_swap_hash; return $input_stream; } [download]	[reply] [d/l]
Re^5: Interpolating subroutine call in SQL INSERT INTO SELECT statement by poj (Abbot) on Aug 28, 2015 at 15:14 UTC
The match for 1 is incorrect, you can't use a character class like that. Try this test #!perl use strict; use YAPE::Regex::Explain; my $str = '> milk & honey < < bill&ben >'; sub convert { my $input_stream = shift; return undef unless $input_stream; my %char_swap_hash = ( 1 => {OLD => '&[^(amp;)\|(lt;)\|(gt;)]', NEW => '&'}, # don't mat +ch legit '&' codes 2 => {OLD => '<', NEW => '<'}, 3 => {OLD => '>', NEW => '>'} ); $input_stream =~ s/$char_swap_hash{$_}{OLD}/$char_swap_hash{$_}{NEW} +/g for keys %char_swap_hash; return $input_stream; } print YAPE::Regex::Explain->new('&[^(amp;)\|(lt;)\|(gt;)]')->explain; print convert($str),"\n"; [download] Alternative `my %htm = ( '&' => '&', '>' => '>', '<' => '<', ); my $REx = qr'(&(?!(?:amp\|lt\|gt);)\|[><])'; $str =~ s/$REx/$htm{$1}/g; print $str."\n\n"; print YAPE::Regex::Explain->new($REx)->explain;` [download] poj	[reply] [d/l] [select]
Re^6: Interpolating subroutine call in SQL INSERT INTO SELECT statement by shadowsong (Pilgrim) on Sep 01, 2015 at 08:56 UTC
Re^7: Interpolating subroutine call in SQL INSERT INTO SELECT statement by poj (Abbot) on Sep 01, 2015 at 09:27 UTC
Some notes below your chosen depth have not been shown here
Re^5: Interpolating subroutine call in SQL INSERT INTO SELECT statement by chacham (Prior) on Aug 28, 2015 at 14:21 UTC
SQL forums are often filled with question of how to do fancy things, and the answer is usually not with a custom function. Between joins, where clauses, with clauses, and recursive queries, you can do quite a bit. It's important to state what you are to do as opposed to deciding a solution and asking how to implement it. For all you know, the best answer might even be perl based! You can choose to share or not to share. People here are often willing to help if you let them.	[reply]