• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Running a long SQL

I need to know how to run the Following SQL in perl:


////////////// start of sql
--  Get Total Cases to Assign
Declare
      nCases number(15,0) default 0;  --total new cases
    p16 number(4,3) default 0.125;  -- percentages to be assigned to TC's
    p17 number(4,3) default 0.141;
    p21 number(4,3) default 0.095;
    p26 number(4,3) default 0.149;
    p28 number(4,3) default 0.19;
    p29 number(4,3) default 0.044;
    p36 number(4,3) default 0.117;
    p37 number(4,3) default 0.139;
Begin
-- Count of new cases
select count(1) into nCases from tssuser.tss_elig
where assgn_dt is null and tssc_dt is null;

/*  1600  --------------------------------------------------------------------------*/
-- Assign 1600 to 1600
update tssuser.tss_elig
set assgn_tc = '1600', assgn_dt = to_date(sysdate)
where TC = '1600'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p16) from dual);

/*  1700  --------------------------------------------------------------------------*/
-- Assign 1700 to 1700
update tssuser.tss_elig
set assgn_tc = '1700', assgn_dt = to_date(sysdate)
where TC = '1700'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p17) from dual);

/*  2800  --------------------------------------------------------------------------*/
-- Assign 2800 to 2800
update tssuser.tss_elig
set assgn_tc = '2800', assgn_dt = to_date(sysdate)
where TC = '2800'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p28) from dual);

/*  2100  --------------------------------------------------------------------------*/
-- Assign 2100 to 2100
update tssuser.tss_elig
set assgn_tc = '2100', assgn_dt = to_date(sysdate)
where TC = '2100'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p21) from dual);
-- Assign 2800 to 2100
update tssuser.tss_elig
set assgn_tc = '2100', assgn_dt = to_date(sysdate)
where TC = '2800'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p21) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2100');

/*  2600  --------------------------------------------------------------------------*/
-- Assign 2600 to 2600
update tssuser.tss_elig
set assgn_tc = '2600', assgn_dt = to_date(sysdate)
where TC = '2600'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p26) from dual);
-- Assign 2800 to 2600
update tssuser.tss_elig
set assgn_tc = '2600', assgn_dt = to_date(sysdate)
where TC = '2800'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p26) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2600');

/*  2900  --------------------------------------------------------------------------*/
-- Assign 2900 to 2900
update tssuser.tss_elig
set assgn_tc = '2900', assgn_dt = to_date(sysdate)
where TC = '2900'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p29) from dual);

/*  3600  --------------------------------------------------------------------------*/
-- Assign 3600 to 3600
update tssuser.tss_elig
set assgn_tc = '3600', assgn_dt = to_date(sysdate)
where TC = '3600'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p36) from dual);

/*  3700  --------------------------------------------------------------------------*/
-- Assign 3700 to 3700
update tssuser.tss_elig
set assgn_tc = '3700', assgn_dt = to_date(sysdate)
where TC = '3700'
and assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p37) from dual);
--
/*  Unmet Quotas  -------------------------------------------------------------------*/
-- Fill un-met 1600 quota with any
update tssuser.tss_elig
set assgn_tc = '1600', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p16) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '1600');
-- Fill un-met 1700 quota with any
update tssuser.tss_elig
set assgn_tc = '1700', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p17) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '1700');
-- Fill un-met 2800 quota with any
update tssuser.tss_elig
set assgn_tc = '2800', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p28) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2800');
-- Fill un-met 2100 quota with any
update tssuser.tss_elig
set assgn_tc = '2100', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p21) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2100');
-- Fill un-met 2600 quota with any
update tssuser.tss_elig
set assgn_tc = '2600', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p26) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2600');
-- Fill un-met 2900 quota with any
update tssuser.tss_elig
set assgn_tc = '2900', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p29) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '2900');
-- Fill un-met 3600 quota with any
update tssuser.tss_elig
set assgn_tc = '3600', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p36) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '3600');
-- Fill un-met 3700 quota with any
update tssuser.tss_elig
set assgn_tc = '3700', assgn_dt = to_date(sysdate)
where assgn_dt is null and tssc_dt is null
and rownum <= (select round(nCases * p37) from dual)
              - (select count(1) from tssuser.tss_elig
                  where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '3700');
commit;
End;

////////////// end of sql



well my question is that can i just somthing like:

$dbh->do( $sql);

???

thanks.
0
degao
Asked:
degao
1 Solution
 
BioICommented:
Indeed, you can use the DBI-module for this purpose

#!/usr/bin/perl -w

use DBI;
use strict;

my $dbh = DBI->connect("DBI:mysql:database:host", 'user', 'password');
my $sql = "update tssuser.tss_elig set assgn_tc = '3700', assgn_dt = to_date(sysdate) where assgn_dt is null and tssc_dt is null and rownum <= (select round(nCases * p37) from dual) - (select count(1) from tssuser.tss_elig where to_date(assgn_dt) = to_date(sysdate) and assgn_tc = '3700')";
$dbh->do("$sql");

and this you can do for all other sql-commands
0
 
degaoAuthor Commented:
How can I run the entire sql statement at one time? instead of do a $dbh->do("$sql"); for each update
0
 
fantasy1001Commented:
I would rather like to use prepare() instead of do() for performance issue. For Do(), you could be preparing a statement handle many times more than is required when execution. Also, Do() only takes care of non querying events.

use DBI;

### The database handle
my $dbh = DBI->connect ( "dbi: Oracle :archaeo", "username", "password" );

### The statement handle
my $sth = $dbh->prepare ( "SELECT id, name FROM megaliths" );
$dbh->execute();

About Running Entire SQL at one time:
I remember there is a UNION keyword with sql. like this
select a from tbl1 where b=1 UNION update tbl2...

Thanks & cheers
0
 
BioICommented:
The Union command only works for "select" statements, not for update and things like that.  I don't know any method to run a large query like you in one time.  I have wirten a script to parse your mysql-statements and do it one by one [the mysql-commands are stored in a file mysql.txt].  The only problem is that you now also get the declare part and the commit and end-thing.  I don't know whether this is a problem.  
Hope this is a possible solution for you.
Good luck!!


#!/usr/bin/perl -w

use strict;
use DBI;

my $myDEBUG = 1;
my $file = "mysql.txt";
open FILE, "mysql.txt" || die "Unable to open file $file because $!\n";
my $dbh = DBI->connect("DBI:mysql:database:host", 'user', 'password');

my $sql_command;
while (<FILE>) {
    chomp;
    my $line = $_;
    if ($line =~ /^-|^\//) {
      # comment line -> ignore
      next;
    }
    $line =~ s/\s+$//g; #replace spaces in beginning and end
    $line =~ s/^\s+//g;
    $sql_command .= $line." "; #connect part of line to sql command
    if ($line =~ /;$/) {
      # end of mysql command so do update
      $myDEBUG && print "mysqlcommand --> ", $sql_command, "\n";;
      $dbh->do{$sql_command};
      # reset sqlcommand
      $sql_command = "";
    }
}

0
 
xxflipCommented:
if the database server supports multiple queries in the same transaction, you can(should be able) put several sql instructions separated by a ";"
in Postgre, for example, I can add all my update instructions into one string, and then send them to the server as a single transaction (the changes will only be comited if no error is found), but I think I read somewhere that the DBI doesn't  support multiple queries in same transaction.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now