topic Re: Open SQL - fetch selective records whose field value is 5 characters in Application Development and Automation Discussions

Open SQL - fetch selective records whose field value is 5 characters

Former Member — Sat, 08 Nov 2008 09:41:36 GMT

Hi,

I am using open SQL.

I have a table which has a field with max length of 10 characters. Now I want to fetch only those records whose value length is 5 characters.

Can I achieve this without using wildcard '_' ?

Is there a way by which I can find the length of the field value?

Thanks.

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Mon, 10 Nov 2008 04:18:24 GMT

Hello,

Explain your problem little bit clearly.

If possible give one example.

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Tue, 11 Nov 2008 09:31:46 GMT

Hello Santosh,

If a field lendth say MATNR is 18 characters and in MARA table I have material nos. as

10010001,

20010002,

30010003,

400100045.

Now in my SQL query, I would like to fetch only the first three material nos., ie, all material nos whose length is 8 digits.

Is there a way to derive it in SQL where condition (not using '_' wildcard).

Thanks.

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Tue, 11 Nov 2008 09:58:22 GMT

Good

I dont think you can give the field range while selecting a particular field data from the table, so you will get complete filed value with the availble length. If you truncate some the digits outside the select statement.

Thanks

mrutyun^

Re: Open SQL - fetch selective records whose field value is 5 characters

ThomasZloch — Tue, 11 Nov 2008 10:04:50 GMT

WHERE MATNR LIKE '________' would work, why don't you want to use wildcards?

Thomas

Edit: actually it would not work if MATNR contains only digits, since these are stored with leading zeroes in the DB. Next one please.

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Tue, 11 Nov 2008 10:20:25 GMT

Hi Thomas,

I agree that this will not work for MATNR. My requirement is not for MATNR.

The reason for me not to use '_' wildcard is that I want to do this for 1100, 2000, 3000 .... 7000 series of a particular field. Using '_' would mean that i will have to use '_' for all the series.

In native SQL I can use 'where LEN(VKBUR) = 3'.

Is there a similar way in open SQL?

Thanks.

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Tue, 11 Nov 2008 10:23:04 GMT

Hi,

I think we can do like this.

Initially fetch all the data in to the internal table.

then we can delete the records whose length greater than 5 and less than 5.

Regards.

Re: Open SQL - fetch selective records whose field value is 5 characters

ThomasZloch — Tue, 11 Nov 2008 10:36:03 GMT

> In native SQL I can use 'where LEN(VKBUR) = 3'.

> Is there a similar way in open SQL?

I don't think so.

> I agree that this will not work for MATNR. My requirement is not for MATNR.

Right, you mentioned field length 10...my mistake.

> The reason for me not to use '_' wildcard is that I want to do this for 1100, 2000, 3000 .... 7000 series of a particular field. Using '_' would mean that i will have to use '_' for all the series.

Not completely getting this, so I'll withdraw now

Thomas

Re: Open SQL - fetch selective records whose field value is 5 characters

Clemenss — Tue, 11 Nov 2008 11:32:45 GMT

Hey,

why not use open sql and put +++++ in your select-options?

This will return all values consisting of 5 non-blank characters - which may have spaces enclosed.

IE SE16 for T000 with ++++++ in field MTEXT will select SAP AG but probably not any other client because usually the text is longer.

Regards,

Clemens.

Re: Open SQL - fetch selective records whose field value is 5 characters

ThomasZloch — Tue, 11 Nov 2008 11:33:59 GMT

This would be the same as LIKE '_____', but he doesn't like it...

Re: Open SQL - fetch selective records whose field value is 5 characters

Clemenss — Tue, 11 Nov 2008 11:39:07 GMT

Hi kumar,

possibly you can put it into a select-option range LOW = 1100 ...to HIGH = 9000.

Regards,

Clemens

Re: Open SQL - fetch selective records whose field value is 5 characters

Former Member — Mon, 22 Dec 2008 06:15:38 GMT

All answers to this question are known. My question is not understood.

Thanks for your time and effort.