Re: postgres 9.2.4 - ERROR: invalid input syntax for type numeric: "" - Mailing list pgsql-general

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.

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 text:, but I can’t see which COALESCE to fix out the several COALESCE  in the query.

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';