Jump to content
Changes to the Jaspersoft community edition download ×

CHAR versus VARCHAR in Oracle problem


Recommended Posts

By: Keith Berman - bobbassen

CHAR versus VARCHAR in Oracle problem

2002-07-16 10:07

I am trying to query an Oracle database using parameters. I have no problems when my data types are VARCHAR but whent they are CHAR, I get no results back.

 

Is there a problem when passing a String into a query that is trying to retrieve a CHAR(10)?

 

Here is my code (ProcGroup is CHAR(10) in db:

 

<parameter name="ProcGroup" class="java.lang.String"/>

<queryString><![CDATA[select CARRIER_ID, GROUP_NUM, SAS, REC_TYPE, PROCESSOR_GROUP, TURNAROUND_DAYS, sum(TOTAL_CLAIM_CNT) as TOTAL_CLAIM_CNT

from DENTAL_SUM_GSAS_TURNAROUND

where CARRIER_ID = $P{Carrier} and GROUP_NUM = '5' and

PROCESSOR_GROUP = $P{ProcGroup} and

report_date between '20000101' and '20000301' and

date_type = 'P'

group by CARRIER_ID, GROUP_NUM, SAS, PROCESSOR_GROUP, TURNAROUND_DAYS, REC_TYPE

]]></queryString>

 

 

By: Teodor Danciu - teodord

RE: CHAR versus VARCHAR in Oracle problem

2002-07-17 00:34

 

Hi,

 

Pay attention to the blank spaces that are

always present in the CHAR fields.

 

For example:

 

If you store the value "John" in a VARCHAR field,

when you retrieve it from the database you will

always get "John" back (length=4).

 

But if you store it in a CHAR(10) field,

when you retrieve it you will obtain

"John_ _ _ _ _ _" (length=10) because the

database fills with blank spaces at right

the CHAR fields.

 

The solution is to TRIM the blank spaces from

the table column and from the parameter in your

WHERE clause when you compare them.

 

I hope this helps.

Teodor

 

 

 

By: Keith Berman - bobbassen

RE: CHAR versus VARCHAR in Oracle problem

2002-07-17 07:11

Thanks a ton, that took care of it.

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...