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.
