topic Re: Select error in Application Development and Automation Discussions

Select error

Former Member — Fri, 22 Jul 2005 14:45:51 GMT

Hello all,

How can a simple statement like this one:

SELECT SINGLE * FROM tbl_smt INTO ls_smt

WHERE field = p_field.

always return sy-subrc = 4 ?

p_field is declared like tbl_smt-field

and is in fact a CHAR of length 30.

tbl_smt contains a few lines with data.

p_field is not a key field.

Re: Select error

RichHeilman — Fri, 22 Jul 2005 14:54:30 GMT

How is the value of field stored in the database?

Is it stored with leading zeros and you are hitting it with a value like 1234? For example in the material master table MARA. The matnr field is 18 characters, if the material number is a numeric value then in the DB it looks like this '000000000000001234'. In you select statement, you must make sure that you are hitting it with the same format.

Regards,

Rich Heilman

Re: Select error

Former Member — Fri, 22 Jul 2005 14:56:09 GMT

No, the field is a char field, storing simple city names.

Regards,

Bogdan

Re: Select error

RichHeilman — Fri, 22 Jul 2005 14:57:15 GMT

Is the field case-sensitive?

Regards,

Rich Heilman

Re: Select error

Former Member — Fri, 22 Jul 2005 14:58:30 GMT

Is that possible?

Regards,

Bogdan

Re: Select error

Vinod_Chandran — Fri, 22 Jul 2005 15:00:50 GMT

Also check whether the field's tbl_smt-field domain has a conversion routine. Goto the domain and copy the routine name for example ALPHA.

Now go to SE37 and check for ALPHA. You will get two function modules like CONVERSION_EXIT_ALPHA_INPUT and

CONVERSION_EXIT_ALPHA_OUTPUT. The first one is used to convert to internal and other one to external. You have to use the INPUT function module.

Thanks

Vinod

Re: Select error

RichHeilman — Fri, 22 Jul 2005 15:00:51 GMT

Sure.....

Regards,

Rich Heilman

Re: Select error

Vinod_Chandran — Fri, 22 Jul 2005 15:01:57 GMT

Yes. It is possible.

Check the Domain and you can find a checkbox.

Re: Select error

Former Member — Fri, 22 Jul 2005 15:02:34 GMT

No, the domain does not have a conversion routine.

Re: Select error

RichHeilman — Fri, 22 Jul 2005 15:02:38 GMT

Look at the domain TEXT40. Under the output characteristics, you see a checkbox, that says lowercase. This means that the system will not translate it to uppercase when saved to the database.

Regards,

Rich Heilman

Re: Select error

RichHeilman — Fri, 22 Jul 2005 15:04:42 GMT

If your field has a domain like this, then you must hit the db with exactly the case used in the database.

Regards,

Rich Heilman

Re: Select error

Former Member — Fri, 22 Jul 2005 15:06:11 GMT

The "lowercase" checkbox is not checked.

Re: Select error

Former Member — Fri, 22 Jul 2005 15:07:30 GMT

The "Lowercase" checkbox isn't checked, eather.

Re: Select error

Former Member — Fri, 22 Jul 2005 15:09:54 GMT

Could it be an issue of encoding?

Re: Select error

RichHeilman — Fri, 22 Jul 2005 15:13:49 GMT

Please post the values of the field as they are exactly from the database. Also post your select statement as well as the parameter statement.

Regards,

Rich Heilman

Re: Select error

Former Member — Fri, 22 Jul 2005 15:15:04 GMT

SE16 doesn't return any values eather, when entering in the select-option corresponding to field a value that exists in the table.

Regards,

Bogdan

Re: Select error

Former Member — Fri, 22 Jul 2005 15:25:16 GMT

Well, I guess I'm too tired now.

The problem is solved.

The values in the database are indeed case sensitive.

I needed to TRANSLATE a bit the field before hitting the database with it, ofcourse.

Thank you, guys.

Bogdan

Re: Select error

Former Member — Fri, 22 Jul 2005 15:30:10 GMT

As you say, it is possible that this is an encoding problem.

Write a simple program to select some records from the table. Now put the your search value (p_field) similar to one of the value in the table.

Say the table contains following entries:

City

WALTHAM

BOSTON

CAMBRIDGE

WORCESTOR

QUINCY

You can set this as screen parameter

p_field = 'WALTHAM'.

SELECT * FROM table INTO it_data.

BREAK-POINT.

When this takes you to debugging mode, examine contents of it_data.

Enter it_data[1] & p_field in the fields to examine.

Click on the magnifying glass on the right hand side to see the Hexadecimal values.

This will allow you to compare what you are entering and what is there in the table, and tell you what you are doing wrong. You can examine other entries one by one if you don't get hit on first one.

~Sivaraj.

Re: Select error

Former Member — Fri, 22 Jul 2005 15:31:59 GMT

;-). Ignore my reply.

Re: Select error

RichHeilman — Fri, 22 Jul 2005 15:32:43 GMT

Please award points accordingly and mark this post as solved. Thanks.

Regards,

Rich Heilman