Assuming you added the or die clause I suggested, the script will die on the prepare. I know this because the next statement says the result was undefined. It will output $DBI::errstr - this should either provide you with the diagnostics to debug your SQL or will not make any sense, in which case the bug is in the Perl code. So, run the script again with the followed subbed for your prepare statement, and post the error message that results if it does not give you the necessary guidance:
$sth1=$Dbh->prepare("
select a.contracting_office_text||h.piin as CONTRACT_NO,
substr(h.dodaac, 4, 3)||h.piin||h.call_no as CAL
+L_NO,
b.cage_code as CAGE_CD,
b.cec_code as DUNNS_PLUS4,
vo.check_no,
h.dov_no as VOUCHER_NO,
h.invoice_no,
vo.eft_trace as EFT_NO,
to_char(h.dovi, 'YYYYMMDD') as INVOICE_ISSUE_DT,
to_char(h.dov_date, 'YYYYMMDD') as PAYMENT_DT,
h.invoice_amt,
h.dov_amt as CHECK_EFT_AMT,
decode(greatest(discount_due_date, h.dov_date),
+discount_due_date, h.discount_amt,0) as DISCOUNT_AMT,
h.penalty_amt as INTEREST_AMT,
decode(h.status, 90, 1, 60, 6, 2) as PAY_STATUs,
to_char(h.doiar, 'YYYYMMDD') as INVOICE_RECV_DT,
to_char(h.doras, 'YYYYMMDD') as MERCH_ACCEPT_DT,
s.fao_dssn as PAYING_DSSN,
(h.invoice_amt+h.adj_invoice_amt+h.adj_invoice_a
+mt_2+h.adj_invoice_amt_3+h.adj_invoice_amt_4+h.adj_invoice_amt_5+h.ad
+j_i
nvoice_amt_6) as GROSS_INV_AMT,
to_char(h.dov_date, 'YYYYMMDD') as DUE_DT,
f.routing_no as RECEIVING_BANK_ID,
h.adj_invoice_amt as ADJUSTMENT_AMOUNT_1,
h.reason_code_i as ADJUSTMENT_REASON_1,
decode(h.edi_disc_amt,
0,h.adj_invoice_amt_2 + h.adj_invoice_amt
+_3 + h.adj_invoice_amt_4 + h.adj_invoice_amt_5 + h.adj_invoice_amt_6,
h.edi_disc_amt) as ADJUSTMENT_AMOUNT_2,
decode(h.edi_disc_amt,
0,h.reason_code_i_2,
'SWELL') as ADJUSTMENT_REASON_2,
f.account_no as ACCOUNT_NUMBER,
h.currency_code,
h.address_line_1 as ADDRESS_1,
h.address_line_2 as ADDRESS_2,
h.city|| ',' ||h.state_cd||' '||h.zip_code as AD
+DRESS_3,
h.company_name as PAYEE_NAME,
e.tin,
h.vendor_code,
h.net_due_date as NET_DUE_DATE, #Added for Revis
+ion 1.6
h.status as STATUS #Added for Revision 1.6
from historydb h, voucher vo, cdb c, cont_office a, vdb b,
+ eft f, vcodedb e, svcuniqdb s
where h.dov_no = vo.dov_no
and h.dov_date = vo.dov_date
and h.piin = c.piin
and c.status = 20
and c.contracting_office_id = a.contracting_office_id
and c.mail_to_code = b.address_code
and c.vendor_code = b.vendor_code
and c.vendor_code = f.vendor_code (+)
and c.eft_addr_code = f.eft_addr_code (+)
and f.status (+) = 40
and h.vendor_code = e.vendor_code
and h.dov_date = ?
")
or die "Prepare failed: $DBI::errstr";
|