CODEWITHSUNDEEP
oracle-databaseplsqloracle10gplsqldeveloper
hsuk
hsuk

Reputation: 6860

Hierarchical query when used as sub query gives : ORA-00911: invalid character

I have been trying to execute the following query and its throwing me following error:

ORA-00911: invalid character

select 
sum(coalesce(decode(FKTD_DR_CR_TYPE,'1',FKTD_NRS_AMOUNT,0),0)) as DEBIT_AMOUNT,
sum(coalesce(decode(FKTD_DR_CR_TYPE,'0',FKTD_NRS_AMOUNT,0),0)) as CREDIT_AMOUNT 
from FMS_KS_TRANS_DTL 
inner join FMS_KS_TRANS_MST 
    ON FMS_KS_TRANS_MST.FKTM_TRANS_MST_ID = FMS_KS_TRANS_DTL.FKTD_TRANS_MST_ID 
inner join FMS_FC_VOUCHER_CONFIG 
    ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_KS_TRANS_MST.FKTM_VOUCHER_ID 
where FFVC_ACCOUNT_TYPE = 5 and FKTM_FISCAL_YEAR='2066/67'
and FKTD_ACC_ID in 
(
    select FFAM_ACC_ID from FMS_FC_ACC_MST
    where ffam_group_flag = 2 and FFAM_FISCAL_YEAR='2066/67'
    start with ffam_acc_id in 
    (
        select distinct FKP_CASH_ACC_ID from FMS_KS_PARAMETER 
        where FKP_FISCAL_YEAR='2066/67'
    )
    connect by ffam_group_flag = 2 
    and prior ffam_acc_id = ffam_upper_acc_id;
    /*
      This sub query executes fine if executed separately 
      and this whole query block executes fine if 
      this hierarchical query is not used.
    */
)

Upvotes: 0

Views: 605

Answers (2)

Fortega
Fortega

Reputation: 19702

A subquery should not end with a ;

select 
sum(coalesce(decode(FKTD_DR_CR_TYPE,'1',FKTD_NRS_AMOUNT,0),0)) as DEBIT_AMOUNT,
sum(coalesce(decode(FKTD_DR_CR_TYPE,'0',FKTD_NRS_AMOUNT,0),0)) as CREDIT_AMOUNT 
from FMS_KS_TRANS_DTL 
inner join FMS_KS_TRANS_MST 
    ON FMS_KS_TRANS_MST.FKTM_TRANS_MST_ID = FMS_KS_TRANS_DTL.FKTD_TRANS_MST_ID 
inner join FMS_FC_VOUCHER_CONFIG 
    ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_KS_TRANS_MST.FKTM_VOUCHER_ID 
where FFVC_ACCOUNT_TYPE = 5 and FKTM_FISCAL_YEAR='2066/67'
and FKTD_ACC_ID in 
(
    select FFAM_ACC_ID from FMS_FC_ACC_MST
    where ffam_group_flag = 2 and FFAM_FISCAL_YEAR='2066/67'
    start with ffam_acc_id in 
    (
        select distinct FKP_CASH_ACC_ID from FMS_KS_PARAMETER 
        where FKP_FISCAL_YEAR='2066/67'
    )
    connect by ffam_group_flag = 2 
    and prior ffam_acc_id = ffam_upper_acc_id
);

should work I guess (semicolon moved), unless there is something else which is wrong :-)

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23757

A semicolon before commented query?

Upvotes: 2

Related Questions