Perl help: Merging results from 2 databases

January 20, 2011 at 23:16:47
Specs: Unix
Hi,

I am generating a CSV from 2 different databases(SQL server and Oracle)

The format fields are like,

Symbol Code Trading_Limit Net Position Net_Percentage Default_ Limit

Now its possible for the 2 databases to contain records of identical (symbol, code) combination. But since I generate the report for one database and append it with another the records of another database, in my CSV file I have multiple records with identical (symbol, code) combo. I need to merge them. (add up the "Net Position" " Net_Percentage" ) values for records containing identical (symbol, code) value.

How do I achieve this in perl?


See More: Perl help: Merging results from 2 databases

Report •


#1
January 21, 2011 at 05:18:40
How big of a data set are you working with?

Have you looked at using DBD::CSV to update the csv file?


Report •

#2
January 21, 2011 at 05:43:20
The data set isnt very big. I am relatively new to Perl and I am not aware of the DBV handle.

Basically this what I am trying to do.

I generate a query that populates records from SQL server. To this, I append addtional records from Oracle DB.

Now, here is the code snippet.

SQL SERVER:

$fhout->print("Account, A2002\n");
$fhout->print("Symbol, StkCode, Trading Limit, Limit Expiry, Net Long Position, Long Percentage, Net Short Position, Short Percentage, Default Limit\n");
my $sql = "select symbol, stkcode, tradinglimit, expirydate, ";
$sql = $sql . "call_net_long + abs(put_net_short) as long_position, ";
$sql = $sql . " case when tradinglimit = 0 then 0 else (call_net_long + abs(put_net_short))/tradinglimit * 100 end as long_percentage, ";
$sql = $sql . "abs(call_net_short) + put_net_long as short_position, ";
$sql = $sql . " case when tradinglimit = 0 then 0 else (abs(call_net_short) + put_net_long)/tradinglimit * 100 end as short_percentage, ";
$sql = $sql . "defaultlimits ";
$sql = $sql . "from v_trading_limit where cltcode = 'A2002'";
$sql = $sqshCmdPrefix."\"".$sql."\"";
@resultSet = `$sql`;
$rtncode = $?;
if ($rtncode){
printf stderr "DB ERROR $rtncode: Error in calling: $sql\n";
}
else
{
my $record;
foreach $record (@resultSet){
if ($record !~ /Warning/)
{
$fhout->print($record);
}
}


FUNCTION THAT APPENDS ORACLE RECORDS:


append_Vt_Daily_Summary_Trades("A2002", $vt_server, $vt_database, $vt_user, $vt_pwd);
$fhout->print("\n");
}


FUNCTION THAT APPENDS ORACLE RECORDS:

sub append_Vt_Daily_Summary_Trades
{
my $dbhSrc;
my ($trd_acc,$server, $db, $user, $pwd) = @_;

# connect Virtual trader database and append trades to the report
$dbhSrc = DBI->connect("dbi:Oracle:$server",$user,$pwd,
{
RaiseError => 1,
AutoCommit => 1
}) || die "Can't connect to $server as $user: ".$dbhSrc->errstr;
my $sql =

<<SQL
select
substr(SEC.SYMBOL,1,3) as SYMBOL,
UND.SYMBOL as StkCode,
UND_INFO.TRADING_LIMIT as TradingLimit,
sum((case when (OPT.TYPE = 'CALL' and TRD.SIDE = 'BUY') then TRD.QUANTITY else 0 end) + (case when (OPT.TYPE = 'PUT' and TRD.SIDE = 'SELL') then TRD.QUANTITY else 0 end)) as NetLongPosition,
sum((case when (OPT.TYPE = 'CALL' and TRD.SIDE = 'BUY') then TRD.QUANTITY else 0 end) + (case when (OPT.TYPE = 'PUT' and TRD.SIDE = 'SELL') then TRD.QUANTITY else 0 end))/UND_INFO.TRADING_LIMIT as Long_Percentage,
sum((case when (OPT.TYPE = 'CALL' and TRD.SIDE = 'SELL') then TRD.QUANTITY else 0 end) + (case when (OPT.TYPE = 'PUT' and TRD.SIDE = 'BUY') then TRD.QUANTITY else 0 end)) as NetShortPosition,
sum((case when (OPT.TYPE = 'CALL' and TRD.SIDE = 'SELL') then TRD.QUANTITY else 0 end) + (case when (OPT.TYPE = 'PUT' and TRD.SIDE = 'BUY') then TRD.QUANTITY else 0 end))/UND_INFO.TRADING_LIMIT as
Short_Percentage,
UND_INFO.TRADING_LIMIT as Default_Limit
from
INSTRUMENT SEC,
INSTRUMENT UND,
INSTRUMENT_OPTION OPT,
EXCHANGE_ORDER ORD,
EXCHANGE_TRADE TRD,
UNDERLYING_INFO UND_INFO
where
SEC.ID = OPT.ID and
SEC.SECURITY_TYPE = 'Option' and
OPT.UNDERLYING_ID = UND.ID and
UND.SECURITY_TYPE = 'Equity' and
ORD.INSTRUMENT_ID = SEC.ID and
ORD.ID = TRD.ORDER_ID and
TRD.TRADE_ACCOUNT_ID = '$trd_acc' and
UND.ID = UND_INFO.UNDERLYING_ID
GROUP BY
substr(SEC.SYMBOL,1,3),
UND.SYMBOL,
UND_INFO.TRADING_LIMIT
SQL
;
my $stmt = $dbhSrc->prepare($sql);
if( $stmt->execute() )
{
my @fields = (0 .. 7);
my $line;
my $index = 0;

$stmt->bind_columns(undef, \$fields[0], \$fields[1], \$fields[2], \$fields[3],
\$fields[4],\$fields[5],\$fields[6],\$fields[7]);
while ($stmt->fetch())
{
if ( @fields )
{
#No trading limit expiry in VT. Leaving the field blank
$line = "$fields[0], $fields[1], $fields[2], ,$fields[3],$fields[4],$fields[5],$fields[6],$fields[7],$fields[8]";
$fhout->print($line);
$fhout->print("\n");
}
}


$stmt->finish();
}
else
{
die "Failed to execute select statement";
}

}


I have to merge these 2 files bases on a primary key. Any help on this greatly appreciated.


Report •

#3
January 21, 2011 at 06:20:11
Perl has standard modules for interacting with databases which will give you better control over the DB interaction and error handling.

You may want to take a step back and at least read the documentation for the following modules.

DBI - Database independent interface for Perl
DBD::Oracle - Oracle database driver for the DBI module
DBD::ODBC - ODBC Driver for DBI
DBD::CSV - DBI driver for CSV files


Report •

Related Solutions

#4
January 21, 2011 at 06:27:18
Another option besides using the DBD::CSV module would be to load your sql result sets into a HoH (hash of hashes) and adjust the values as needed when you find duplicate keys. Once the data has been combined, then loop over and output the data to the csv file.

Report •

#5
January 25, 2011 at 19:55:06
Thanks a lot for replying. I used HOH and was able to accomplish the task.

Report •

Ask Question