Sorry I found the problem, itâ??s data problem in another database, itâ??s trying to convert numeric null but of which itâ??s not a null data, itâ??s a blank data.  From: Khangelani Gama [mailto:kgama@xxxxxxxxxxxx]  Hi  The follow query below works in one database but not on another database. The databases are similar but independent of each other. From the previous post I found that: This is fixed by casting the first argument to    ERROR: invalid input syntax for type numeric: ""  Query :   SELECT (SELECT br_cde FROM br_prof) as "Store", hp_acc.cus_acno as "Account", hp_acc.hp_sub_acno as "Sub_account", (select con_recover_sum from contract where con_id = hp_acc.con_id) as "Contract_amount", (select con_goods from contract where con_id = hp_acc.con_id) as "Stock_RSP", (select con_doc_disc from contract where con_id = hp_acc.con_id) as "Discount", (hpacc_bdwo_amt - COALESCE(hpacc_bdwo_rec_amt,0) * -1) as "Balance_Outstanding_write_off_a", hpacc_inv_fpp as "Invoice_FPP", hp_acc.acc_cde as "Account_type", ( select endp_addr_line1 from end_point where endp_id = hp_acc.endp_id) as "Delivery_address_1", ( select endp_addr_line2 from end_point where endp_id = hp_acc.endp_id) as "Delivery_address_2", ( select endp_addr_line3 from end_point where endp_id = hp_acc.endp_id) as "Delivery_address_3", ( select endp_addr_line4 from end_point where endp_id = hp_acc.endp_id) as "Delivery_address_4", ( select endp_addr_cde from end_point where endp_id = hp_acc.endp_id) as "Delivery_address_cde", COALESCE(to_char(hpacc_deal_dte::date,'yyyy/mm/dd'),'0000/00/00') as "Date_created", COALESCE(to_char(hpacc_auth_dte::date,'yyyy/mm/dd'),'0000/00/00') as "Date_authorized", COALESCE(to_char(hpacc_inv_dte::date,'yyyy/mm/dd'),'0000/00/00') as "Date_invoiced", COALESCE(to_char(hpacc_cancelled::date,'yyyy/mm/dd'),'0000/00/00') as "Date_cancelled", COALESCE(to_char(hpacc_1_inst_dte::date,'yyyy/mm/dd'),'0000/00/00') as "First_installment_date", (select con_instalment from contract where con_id = hp_acc.con_id) as "Monthly_installment", (select con_fin_period from contract where con_id = hp_acc.con_id) as "Finance_period", (select con_fin_rate from contract where con_id = hp_acc.con_id) as "Interest_rate", (select con_cost from contract where con_id = hp_acc.con_id) as "Stock_cost", (select COALESCE(con_handling,0) from contract where con_id = hp_acc.con_id) as "Cartage", (select con_maint from contract where con_id = hp_acc.con_id) as "Maintenance", (select con_club from contract where con_id = hp_acc.con_id) as "Club_Fees", (select con_del from contract where con_id = hp_acc.con_id) as "Delivery", (select (con_ins + con_rins) from contract where con_id = hp_acc.con_id) as "Insurance", (select con_stamp_duty from contract where con_id = hp_acc.con_id) as "Stamp_duty", (select COALESCE(con_cash_dep,0) from contract where con_id = hp_acc.con_id) as "Cash_deposit", (select COALESCE(con_fin_chrg,0) from contract where con_id = hp_acc.con_id) as "Finance_charges", (select COALESCE(con_goods_tax+con_del_tax+con_maint_tax+con_ins_tax+con_rins_tax+con_other_tax+con_club_tax+con_addon_tax+con_doc_disc_tax+con_ln_disc_tax+con_prm_disc_tax+con_admin_fee_tax+con_rainbow_loan_amt_tax+con_installation_fee_tax+con_1st_mth_cr_life_tax+con_handling_tax,0) from contract where con_id = hp_acc.con_id ) as "Tax", COALESCE((select sum(TRUNC(CASE WHEN sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax !=0 THEN sku_line.skul_nett_rsp_tax/(sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax)*100 ELSE 0 END,2)) from sku_line join hpacc_sku using (skul_id) where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "Tax_rate", ( CASE WHEN (select con_fin_period from contract where con_id = hp_acc.con_id) <> 0 THEN (select con_club/con_fin_period from contract where con_id = hp_acc.con_id) ELSE 0 END)::numeric(16,2) as "Monthly_club_fee", COALESCE(hp_acc.hpacc_deal_no,'0') as "Invoice_number", (select con_addon from contract where con_id = hp_acc.con_id) as "Add_on_finance", COALESCE(deposit,0) as "Agreed_deposit", (total_period_in_days-months_not_paid) as "Live_periods", contract_month_left as "Period_to_go", COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "Arrears_Outstanding_write_off_a", amount_now_due as "Now_due", COALESCE(to_char((select hpfin_lst_pay_dte::date from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),'yyyy/mm/dd'),'0000/00/00') as "Last_paid_date", COALESCE((select hpfin_lst_pay_amt from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "Last_paid_amount", COALESCE(theoretical_instalment,0) as "Theoretical_installment", COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "Actual_months_since_last_paid_i", COALESCE((select con_initiation_fee from contract where con_id = hp_acc.con_id),0) as "Adn_Initiation_fee", COALESCE((select sum(aoc_value) from add_on_contract where addt_cde = '30' and hp_acc.con_id = con_id group by add_on_contract.con_id),0) as "License_fee", COALESCE(( select con_installation_fee from contract where con_id = hp_acc.con_id),0) as "Installation_fee", COALESCE((select con_addon from contract where con_id = hp_acc.con_id),0) as "Optional_insurance_amount", COALESCE(( select con_recover_sum from contract where con_id = hp_acc.con_id),0) as "Loan_amount", (select hpfin_pay_mtd from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno) as "Payments_MTD", (select hpfin_int_mtd from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno) as "AFC_MTD", (select con_doc_disc from contract where con_id = hp_acc.con_id) as "Discount__", provision_period as "Provided_periods", COALESCE(opening_balance,0) as "Raised_to_date_amount", COALESCE((select hpfin_fin_reb_ltd from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "UFC_rebate_total", COALESCE(( select con_goods_tax from contract where con_id = hp_acc.con_id),0) as "Goods_tax", COALESCE((select con_addon_tax from contract where con_id = hp_acc.con_id),0) as "Add_on_tax", COALESCE(total_period_in_days,0) as "UFC_total_days", COALESCE(earned_period_in_days,0) as "UFC_earned_days", COALESCE(unearned_period_in_days,0) as "UFC_unearned_days", COALESCE(to_char(last_payment_date::date,'yyyy/mm/dd'),'0000/00/00') as "Lastl_full_paid_date", COALESCE(last_payment_amount,0) as "Last_full_paid_amount", COALESCE(to_char((select hpfin_lst_pay_dte::date from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),'yyyy/mm/dd'),'0000/00/00') as "last_part_paid_date", COALESCE((select hpfin_lst_pay_amt from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as "last_part_paid_amount", COALESCE(EXTRACT (MONTH FROM AGE(last_payment_date::date,v1_ageing_date)),0) as "Months_since_last_full_payment", COALESCE(EXTRACT (MONTH FROM AGE((select hpfin_lst_pay_dte from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),v1_ageing_date)),0) as "Months_since_last_part_payment ", COALESCE(EXTRACT (MONTH FROM AGE(last_payment_date::date,v1_ageing_date)),0) as "Months_since_last_payment", terms/12 as "Account_age_in_years", COALESCE(amortized_unearned_cpp,0) as "UIC_provision", COALESCE(unearned_club_prov,0) as "Club_provision", COALESCE(finance_rebate,0) as "UFC_rebate", COALESCE(amortized_unearned_fc,0) as "Effective_UFC", COALESCE(balance,0) as "Balance", COALESCE(to_char(hpacc_repo_dte::date,'yyyy/mm/dd'),'0000/00/00') as "Repo_date", hpacc_repo_amt as "Repo_amount", COALESCE(to_char(hpacc_bdwo_dte::date,'yyyy/mm/dd'),'0000/00/00') as "Write_off_date", (hpacc_bdwo_amt * -1) as "Write_off_amount", (hp_acc.hpfin_int_ltd) as "AFC_LTD", hp_acc.hpfin_deb_ltd as "Debits_LTD", hp_acc.hpfin_crd_ltd as "Credits_LTD", hp_acc.hpfin_pay_ltd as "Payments_LTD", hp_acc.hpfin_deb_mtd as "Debits_MTD", hp_acc.hpfin_crd_mtd as "Credits_MTD", hp_acc.hpacc_pay_day as "Debit_Order_Pay_Day", bank_det.bnk_name as "Bank_Name", bank_det.bnk_br_name as "Bank_Branch_Name", bank_det.bnk_brno as "Bank_Branch_Code", bank_det.bnk_holder_name as "Bank_Ac_Name", bank_det.bnk_acno as "Bank_Ac_No", bank_det.bnkacc_cde as "Bank_Ac_Type", COALESCE(to_char(bank_det.bnk_acc_open_dte, 'yyyy/mm/dd'), '0000/00/00') as "Bank_Ac_Open_Date", case when hpacc_repo_id is null then (to_char(hpacc_repo_dte, 'YYYYMM')) else (select fpp_cde from audit where aud_id = hpacc_repo_id) end as "Repo_Loss_FPP", hpacc_repo_recovered_amt as "Repo_Recovery_Amt", case when hpacc_bdwo_id is null then (to_char(hpacc_bdwo_dte, 'YYYYMM')) else (select fpp_cde from audit where aud_id = hpacc_bdwo_id) end as "BDWO_Loss_FPP", hpacc_bdwo_rec_amt as "BDWO_Recovery_Amt", case when hpacc_cancelled is not null then case when (select fpp_cde from hp_tran join hp_doc using (hpdoc_id) join audit using (aud_id) where act_typ in ('5204','5207','5210') and hp_tran.cus_acno = hp_acc.cus_acno AND hp_tran.hp_sub_acno = hp_acc.hp_sub_acno limit 1) is not null then (select fpp_cde from hp_tran join hp_doc using (hpdoc_id) join audit using (aud_id) where act_typ in ('5204','5207','5210') and hp_tran.cus_acno = hp_acc.cus_acno AND hp_tran.hp_sub_acno = hp_acc.hp_sub_acno limit 1) else (to_char(hpacc_cancelled, 'YYYYMM')) end else null end as "Deal_Cancel_FPP", COALESCE(hp_acc.cus_acno::numeric+hp_acc.hp_sub_acno::numeric+coalesce(hp_acc.hpacc_inv_fpp::numeric,0),0) as "Hash_total" FROM hp_acc LEFT JOIN bank_det using(bnk_id) JOIN central_account USING (cus_acno, hp_sub_acno) WHERE central_account.fpp_cde = '201404';           CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. |
