Error in SQL(SQLSATE = 01S01)

srinib
srinib used Ask the Experts™
on
I am getting the  error while executing the below SQL statement.
Error Description : SQLSTATE = 01S01
                           [IBM][CLI Driver] CLI0165E Error in row. SQLSTATE = 01S01

select s.FACT_TYP, f.FACT_TYP_DSC, sum(s.MTD_AMT) as Balance,
            sum (case when a.tax_id_typ = '' then s.MTD_AMT
                      when a.tax_id_typ = '1' then s.MTD_AMT end) as Taxable,
            sum (case when a.tax_id_typ = '2' then s.MTD_AMT
                      when a.tax_id_typ = '3' then s.MTD_AMT end) as NonTaxable
            from BSCX.A000_ACCT_BASE a, BSCX.FP02_ACCT_SUMMARY s
            left outer join BSCX.C006_FACT_TYP f
            on s.FACT_TYP = f.FACT_TYP
            where s.CAL_OBJ_ID = 339255
            and a.account_no = '22058902'
            and s.Account_obj_id  = a.account_obj_id
            and s.BSC_CURRENCY_CD = 'USD'
            group by s.FACT_TYP, f.FACT_TYP_DSC ;

If i remove the case statements then this quesry is working fine.
Any pointers?

Thanks in advance
Srinib
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

The 01S01 error indicates that a FETCH operation detected/returned an error on a row.

Since the error seems to occur only in conjunction with the case logic that's a great place to start looking.

One thing that jumps out is that your case statements don't have a default condition.  Try one of these simple changes:

select s.FACT_TYP, f.FACT_TYP_DSC, sum(s.MTD_AMT) as Balance,
            sum (case when a.tax_id_typ = '' then s.MTD_AMT
                            when a.tax_id_typ = '1' then s.MTD_AMT
                            else 0 end) as Taxable,
            sum (case when a.tax_id_typ = '2' then s.MTD_AMT
                            when a.tax_id_typ = '3' then s.MTD_AMT
                            else 0 end) as NonTaxable
            from BSCX.A000_ACCT_BASE a, BSCX.FP02_ACCT_SUMMARY s
            left outer join BSCX.C006_FACT_TYP f
            on s.FACT_TYP = f.FACT_TYP
            where s.CAL_OBJ_ID = 339255
            and a.account_no = '22058902'
            and s.Account_obj_id  = a.account_obj_id
            and s.BSC_CURRENCY_CD = 'USD'
            group by s.FACT_TYP, f.FACT_TYP_DSC ;

or even

select s.FACT_TYP, f.FACT_TYP_DSC, sum(s.MTD_AMT) as Balance,
            sum (case when a.tax_id_typ = '' then s.MTD_AMT
                            when a.tax_id_typ = '1' then s.MTD_AMT
                            else cast (NULL as integer) end) as Taxable,
            sum (case when a.tax_id_typ = '2' then s.MTD_AMT
                            when a.tax_id_typ = '3' then s.MTD_AMT
                            else cast (NULL as integer) end) as NonTaxable
            from BSCX.A000_ACCT_BASE a, BSCX.FP02_ACCT_SUMMARY s
            left outer join BSCX.C006_FACT_TYP f
            on s.FACT_TYP = f.FACT_TYP
            where s.CAL_OBJ_ID = 339255
            and a.account_no = '22058902'
            and s.Account_obj_id  = a.account_obj_id
            and s.BSC_CURRENCY_CD = 'USD'
            group by s.FACT_TYP, f.FACT_TYP_DSC ;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial