Search Postgresql Archives

Re: postgres 9.2.4 - ERROR: invalid input syntax for type numeric: ""

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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]
Sent: Tuesday, May 20, 2014 12:24 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: postgres 9.2.4 - ERROR: invalid input syntax for type numeric: ""

 

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

 

 

 

 

 

 

 

 

 

 


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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux