Re: JDBC Driver Problem - Mailing list pgsql-jdbc

Hi Nick,

Here is the query that is giving the problem,

select bill_num, bill_par_num, to_char(bill_from_dt,'DD-Mon-YYYY') as
bill_from_dt,to_char(BILL_TO_DT,'DD-Mon-YYYY') as BILL_TO_DT,
to_char(BilL_AMT,'999999999.99') as BilL_AMT, to_char(BILL_DT,'DD-Mon-YYYY')
as bill_dt, (bill_amt-paid_amt) as OUTSTANDING_AMT,
to_char(BilL_Py_By_Dt,'DD-Mon-YYYY') as bill_py_by_dt,BilL_STAT,BilL_CURr_CD
from((select bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, BilL_AMT,
coalesce(sum(pymt_amt),0)as paid_amt, BILL_DT,
BilL_Py_By_Dt,BilL_STAT,BilL_CURr_CD  from pc_t_payment,pc_t_bill where
pymt_bill_num = bill_num and pymt_cust_id = <cust_id> group by bill_num,
bill_par_num, bill_from_dt, BILL_TO_DT, bill_amt, BILL_DT, BilL_Py_By_Dt,
BilL_STAT, BilL_CURr_CD) union (select bill_num, bill_par_num, bill_from_dt,
BILL_TO_DT, BilL_AMT,  0 as paid_amt, BILL_DT, BilL_Py_By_Dt, BilL_STAT,
BilL_CURr_CD from pc_t_bill where bill_cust_id = <cust_id> except (select
bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, BilL_AMT,  0 as paid_amt ,
BILL_DT, BilL_Py_By_Dt,BilL_STAT,BilL_CURr_CD from pc_t_payment,pc_t_bill
where pymt_bill_num = bill_num and pymt_cust_id =<cust_id>))) as bill_tmp
order by bill_num desc;

This query returns 4 records when executed from the back end which is the
desired result. But when it is executed using the JDBC driver for postgreSQL
7.1 and JDK 1.2, it returns only 3 records. This is the result of the first
query excluding the union. So it appears as if only the first part of the
query is working through the JDBC driver.

This is the other query that is using CASE .. WHEN.
Select bm_link from pc_m_banner where bm_type= <type> and (CASE WHEN
bm_ctg_cd= '' THEN <val> ELSE bm_ctg_cd END) = <val>
It returns one record when excuted through the backend and no records when
executed through the JDBC driver.

Thanks & Regards,
Gautham.

----- Original Message -----
From: "Nick Fankhauser" <nickf@ontko.com>
To: "Gautham S. Rao" <gautam.rao@tatainfotech.com>;
<pgsql-jdbc@postgresql.org>
Sent: Thursday, April 04, 2002 6:26 PM
Subject: RE: [JDBC] JDBC Driver Problem


> Gautham-
>
> Supplying as much of the source code as possible will make it more likely
> that someone can help solve the problem. I don't recall hearing of such a
> problem before, so this is probably not a known issue.
>
> At the very least, copies of the queries might help so we can try
something
> similar in our environments.
>
> -Nick
>
> --------------------------------------------------------------------------
> Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
> Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Gautham S. Rao
> Sent: Thursday, April 04, 2002 7:53 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] JDBC Driver Problem
>
>
> Hi,
>
> We have a query that involves 'union' and 'except' clause. When it is run
on
> the psql database at the backend it retuns the required data. However,
when
> it is executed using the jdbc driver it is returning less number of
records.
> The statements after the union and except clause are not getting executed.
> Is this a bug in the JDBC driver?
>
> Also a similar problem is encountered in another query that uses 'case
when
> <> then <> else <> end' the equivalent of 'decode'. This query returns
> records when exceuted at the backend, but when exceuted using the JDBC
> driver, it retuns no records. What could be the problem? Have any of you
> encountered a similar problem and how do we solve it?
>
> Regards,
> Gautham.
>