Error:SQL Problems: ERROR :operator does not exists:date>= character varying

Question

i am using iReport 3.6.0 with postgresql database ,for date type parameter its giving the following error when writing query "Error:SQL Problems: ERROR :operator does not exists:date>= character varying" please help

Answer 1

See PostgreSQL 8.3 Release Notes for explanation and workaround. You should use

datefield::text>=textfield

Answer 2

There should be datefield >= textfield::date. I think that there would be comparing two date fields not two textfields so the datefield::text >= textfield would be rather wrong.

Answer 3

Dear friend , Now i'm getting new error invalid input syntax for type date:"" i have modified my query as suggested by u ,the query given below

**(select coalesce(V1.village_code,V2.village_code) as village_code, prev_issued, prev_recovered from (select village_code, sum (amt_issued) as prev_issued from Advance where CC_code =$P{cc_code} and issue_date < $P{fdate} and type_of_advance = 'Bank' group by village_code ) as V1 full outer join (select village_code, sum (actual_paid) as prev_recovered from Advance_recovery where CC_code = $P{cc_code} and date < $P{fdate} and type_of_advance = 'Bank' group by village_code ) as V2 on V1.village_code = V2.village_code ) as W1

  full outer join
  (select coalesce(V3.village_code,V4.village_code) as village_code, issued, recovered
   from
   (select village_code, sum (amt_issued) as issued
    from Advance
    where CC_code = $P{cc_code}
    and type_of_advance = 'Bank'
    and issue_date >= $P{fdate}::Date
    and issue_date <= $P{tdate}::Date

    group by village_code
   ) as V3
   full outer join
   (select village_code, sum (actual_paid) as recovered
    from Advance_recovery
    where CC_code = $P{cc_code}
    and type_of_advance = 'Bank'
    and date >= $P{fdate}::Date
    and date <= $P{tdate}::Date

    group by village_code
   ) as V4
   on V3.village_code = V4.village_code
  ) as W2
  on W1.village_code = W2.village_code
 ) as W3
) as W6
where W6.village_code = Village.code
and Village.area_code = Area.code
group by Area.code, Area.name**

i have also set the default value for the date type parameter and remaining parameters as string , if i remove cc_code(string type ) parameter in where clause query reads fine without any error.the problem is when comparing both date type and string type parameter in where clause ,i did many trial and error method still i cant come out ....please help me