Please read Writeup Formatting Tips, then edit your original post to make your question more readable.

You had a preview button and you didn't use it.

This is my interpretation of your code, laid out in a more readable manner. Just out of curiosity, what company is this code coming from?

# Query the database for a list of accounts,
# contracts and products that are of interest.
# Where "of interest" is defined as may have
# terminated a required product in the last
# 2 months.

my $sqlcmd=qq^
SELECT
  cmf.account_no,
  cc.contract_type,
  to_char(cc.start_dt,'mon dd YYYY HH:MM:ss') start_dt,
  ciem.external_id,
  emf_p.element_id,
  to_char(emf_p.product_inactive_dt,
    'mon dd YYYY HH:MM:ss') product_inactive_dt
FROM
  CMF cmf,
  CUSTOMER_CONTRACT cc,
  SERVICE emf,
  SERVICE emf_c,
  PRODUCT emf_p,
  CUSTOMER_ID_EQUIP_MAP ciem ,
  product_charge_map pcm
WHERE
  cmf.account_no = emf_c.parent_account_no AND
  ( (cmf.account_no = cc.parent_account_no AND
      cc.contract_level = 1
    ) OR
    (emf_c.subscr_no = cc.parent_subscr_no AND
      emf_c.subscr_no_resets = cc.parent_subscr_no_resets
      AND cc.contract_level = 3
    )
  )^;
# Add list of interesting contracts.
$sqlcmd ="$sqlcmd AND (";
$i = 0;
foreach $contract_key (sort(keys(%contracts))) {
    $sqlcmd="$sqlcmd cc.contract_type = $contract_key";
    $i++;
    if ($i < $contract_count) {
        $sqlcmd=" $sqlcmd OR ";
    }
}
$sqlcmd="$sqlcmd )";
$sqlcmd =qq^ $sqlcmd AND
  (cc.end_dt is NULL OR cc.end_dt >= emf_p.product_inactive_dt) AND
  cmf.account_no = emf.parent_account_no AND
  emf.subscr_no = emf_c.subscr_no AND
  emf.subscr_no = ciem.subscr_no AND
  emf.subscr_no_resets = ciem.subscr_no_resets AND
  ciem.external_id_type = 1 AND
  emf.subscr_no = emf_p.parent_subscr_no AND
  emf.subscr_no_resets = emf_p.parent_subscr_no_resets AND
  emf_p.tracking_id =pcm.tracking_id AND
  emf_p.tracking_id_serv=pcm.tracking_id_serv AND
  emf_p.parent_account_no=pcm.parent_account_no AND
  PCM.inactive_dt is not NULL AND
  PCM.billed_thru_dt is not NULL AND
  emf_p.product_inactive_dt <= to_date('$now','mm/dd/yyyy') AND
  emf_p.product_inactive_dt > to_date('$one_month','mm/dd/yyyy'
)^;
[download]

Oh, by the way, this seems fairly typical of code that I've witnessed in the investment banking industry. Biggest bunch of cowboys that ever lived.. God only knows how any of their financial transactions and calculations ever actually produced viable answers - clearly logical thought (such as programming) is way beyond the average investment banker coder..

I think you have found the secret of the present financial crisis. It was brought upon us by crappy SQL-code! LOL

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

So many questions ...

Hi what I meant by linking and all is that, as you can see, the SQL query is in two parts, separated by a Perl script. Now when I run the script the query should run as 1 which it did not. So I want to know is it because of something in the Perl script?

(edited the text for typos and so)

No code to run the SQL code, it must be somewhere else in the script and if what we have seen is in any way an example of how this script is written, I have no desire to look at it.

For instance, the dynamic part of the SQL query can be much clearer written as one line of code:

$sqlcmd .= 'AND (' . join 'OR ', map {"cc.contract_type = $_ "} sort k
+eys %contracts;
[download]

Quite a lot of comments for a small snippet of code. One wonders (and shudders) if one thinks of the whole script: obviously there are things no sane man is meant to know.