High and Low values in UNICODE database

sheela_aks · New User Joined: 22 Dec 2006 Posts: 7 Location: Singapore

Hi,
I have an issue with the selecting of records from a UNICODE DB2 database when using the high and low values. Can anyone let me know how I can read all the records in the database using high and low values. The query I used is

select * from VC_CURRENCY
where CURRENCY_CODE >= :WS-CCY-LOW and
CURRENCY_CODE <= :WS-CCY-HIGH ;

I am getting a -302 error when this query is executed. I have some double byte characters in this table and need to fetch them.

priyesh.agrawal · Posted: Sat Dec 23, 2006 12:08 pm

How "CURRENCY_CODE" is defined in the table VC_CURRENCY

Also what is the definition for WS-CCY-LOW & WS-CCY-HIGH.

sheela_aks · New User Joined: 22 Dec 2006 Posts: 7 Location: Singapore

The CURRENCY_CODE is defined as CHAR(3) and the WS-LOW-VALUE and WS-HIGH-VALUE is defined as LOW VALUE and HIGH VALUE respectively, in the working storage division of the COBOL program.

Please let me know if there is any restrictions for Numeric fields as well.

priyesh.agrawal · Posted: Mon Dec 25, 2006 9:29 pm

sheela,

SQL -302 :-
THE VALUE OF INPUT VARIABLE NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE

So what I meant to ask was... What is the PIC clause defined for WS-LOW-VALUE and WS-HIGH-VALUE.

sheela_aks · New User Joined: 22 Dec 2006 Posts: 7 Location: Singapore

The size of the working storage fields are defined as CHAR(3), same as it is defined in the database.

priyesh.agrawal · Posted: Tue Dec 26, 2006 10:50 pm

Then atleast dont have any problem with length of the vars...

BTW, what does this query accomplish... could you explain little bit...

dick scherrer · Posted: Wed Dec 27, 2006 12:53 am

Hello,

Would "select * from VC_CURRENCY" do what you need? If not, why not?

sheela_aks · New User Joined: 22 Dec 2006 Posts: 7 Location: Singapore

Hi,
Actually the given SQL is only a part of the complete where clause. The same condition is being checked for another 5 fields in the database. The idea is to use only one SQL query to fetch records even if only one of the fields have data as input. For eg. I have these 5 fields in the search condition on the screen (currency code, currency name, currency numeric code, currency char code and status). If I would like to fetch all the records which are of status 'ACT' then the other four fields will not have any input.

One way of achieiving this is to check if each of these fields are entered and based on the different permutations and combinations the where clause will have to be written.

To avoid this, we have written a generic query with low and high values so that this can be achieved with just one SQL query statment.

I hope I am being clear now. This single query works fine for the EBCIDIC database but does not work for the UNICODE database.