Re: Load duplicates
So are you reading the records one at a time through DBI and looking for duplicates? There is probably a better way. I work with Teradata, so I'll use Teradata syntax, but the syntax for other databases will be similar. To find the duplicates in your table, you can select all columns from the table (except the auto-increment) along with a count, like so: create volatile table ##dup_counts as (select acct, comp, accr, descrip, all-other-columns..., min(auto_increment) min_num /* or max() */ , count(*) kount from table1 group by acct, comp, accr, descrip, all-other-columns... ) with data; Now there are no duplicates in ##dup_counts, so you can insert them to your target table, either generating a new auto_increment or using min_num. The duplicates can now be inserted into your error table: insert into error_table select acct, comp, ...etc. from ##dup_counts where kount > 1; Et voilà ... the records are deduped with only three statements. As you probably know, in relational databases rows do not have an order, so I'm not sure that you necessarily need a sequence number on the duplicates. If you want to trace the duplicates back to a flat file source, it might be better to add a sequence number to the file before loading it to the database. I hope this helps. On Tue, Aug 15, 2023 at 11:50 AM Ian wrote: > Hi group, > > My perl skills are basic and my SQL skills almost match that. > Using perl 5.28 and mysql on windows. > > I have a couple million records that needs processing to go to their final > destination. > Currently I'm catchin duplicates with error 1062 in perl and using that to > write the duplicate records to a separate table for later processing. > > Question: Can DBI give me a field from the record on the main table > causing the duplicate so I can add that to the record going to > the duplicate table? > > Example: > MAIN table fields = "auto_increment", acct, comp,accr,desc,etc : > (auto_increment created when records are added, rest is the source record) > Duplicate table fields = "auto_increment from MAIN table", > acct,comp,accr,desc,etc. > > Thanks > Ian > > >
Re: Perl script excessively executing statement
I don't work with Oracle, but here's a guess. Maybe the database was unresponsive before your script started running, not as a result of it. If so, maybe your script tried to prepare the statement, failed, and retried 12000 times. Eventually the DBA noticed the problem and restarted the database, at which time your script was terminated along with everything else. On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn wrote: > Hi all, > > > > I have a perl script in my monitoring system that has been working for > months without an issue. > > > > Basically, it connects to an Oracle 12c database, prepares a statement, > then it executes the statement, then it has a while loop to process the > returned rows. > > > > So under normal conditions the statement is executed once every 5 minutes. > > > > Now on Friday last week, it did something really strange which I cannot > account for the behaviour. > > > > According to the DBA, the statement in the script was executed 12610 times > over a 50 minute period causing the database to become non-responsive. The > DBAs also stated that the script only connected to the database once at the > beginning of the 50 minute period. Average execution time of the statement > was 0.26 seconds. > > > > According to the log for my script, the script only executed once at the > beginning of the 50 minute period, and then after that, returned to > executing every 5 minutes. > > > > Since that incident, the statememt in question has only executed the > expected 12 times per hour. > > > > I have yet to find a satisfactory reason the SQL statement from this perl > script executed so many times in the 50 minute period. > > > > Script is running on an Oracle Linux 7.7 server with; > >- oracle 12c client installed >- perl 5.16.3 >- perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository) >- perl-DBD-ODBC 1.50.-3 (from EPEL) >- DBD::Oracle 1.80 (from CPAN) > > > > Oracle 12 database is on a remote server. > > > > Anyone have any ideas why the SQL statement would have been executed > 12000+ times in a 50minute period, when the script and its schedule should > not have executed the SQL any more frequiently than 12 times an hour? > > > > Regards, > > John > > > > >
Re: suppress quoting in prepared sql
The general rule for using parameter markers ('?'s) is that a parameter
marker can appear wherever a literal can appear. (As far as I know, this is
true in any ANSI-compliant database; it goes back to the early years of DB2
and SQL/DS.) The substituted parameters are equivalent to literals. Hence,
it makes sense that MySQL would interpret the list of values as a single
string.
One way of getting around this would be to substitute the value in the perl
script before passing it to the database:
"select sum(column) as columnSum from table where value in ( $ValueIDs )
and row_date between cast( ? as date) and cast( ? as date) "); ... but this
might not be appropriate in your application.
On Tue, Apr 5, 2016 at 12:24 PM, Bruce Ferrell
wrote:
> I'm generating a sql statement like this:
>
> sth = $mysql_dbh->prepare(
> "select sum(column) as columnSum from table where value in ( ? ) and
> row_date between cast( ? as date) and cast( ? as date) ");
>
> sth->execute( $ValueIDs ,$week_start_date,$week_end_date);
>
> $ValueIDs is a series of unquoted values:
>
> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164
>
> When observed at the mysql server, the sql appears as follows:
>
> select sum(column) as columnSum where value in (
> '01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164' )
> and row_date between cast( '2016-03-29' as date) and
> cast( '2016-04-05' as date)
>
> resulting in no data being returned.
>
> When the sql is manually entered as follows:
>
> select sum(column) as columnSum where value in (
> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164 )
> and row_date between cast( '2016-03-29' as date) and
> cast( '2016-04-05' as date)
>
> The correct values are returned.
>
> How can I suppress the quoting for the IN clause?
>
>
>
Re: Perl Teradata
As you probably know, DBD::Teradata is not actively supported by the author
and probably hasn't been tested with TTU 15.0. You may wish to try using
Teradata::SQL instead.
When you say Teradata Client 15.00, are you referring to the client
software (TTU) on your Red Hat machine? What version is the database?
Is the name in /etc/hosts (or DNS) really myHost?
Sorry I can't be more helpful. If you want to try Teradata::SQL, you can
write directly to me for support.
On Wed, Feb 4, 2015 at 8:51 AM, Buehre, Joseph R joseph.r.bue...@boeing.com
wrote:
Good Morning,
I am looking for help to solve my problem with the Perl DBD::Teradata
install 1.52. After a successful, error free, build and install I get the
follow message when trying to make a connection. I can guarantee you the
host is ping-able and exists. Any help would be greatly appreciated.
DBD Terdata version 1.52
Teradata Client 15.00
Redhat verion 6.5
Error:
DBI connect(myHost;database=thedatabase','info',...) failed: Unable to get
host address. at ./test_teradata.pl line 23.
Can't call method prepare on an undefined value at ./test_teradata.pl
line 26.
Connect syntax:
my $tdh =
DBI-connect(dbi:Teradata:$SERVER;database=$DEFAULTDB,$USERNAME,$KEY,
{ RaiseError = 0, tdat_mode = 'ANSI' }
);
Environment variables set:
TD_ICU_DATA=/opt/teradata/client/15.00/tdicu/lib64
COPLIB=/opt/teradata/client/15.00/lib64
COPERR=/opt/teradata/client/15.00/lib64
Thanks,
Joseph (Joe) R. Buehre
Business Hours Phone: 314-791-9725
MOn - Fri 5:30 - 2:00 PM CDT
