topic Re: Select statement to search - case sensitive in Application Development and Automation Discussions

Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 10:24:35 GMT

Hi all,

I have to do a search on the field BSTKD_E for table VBKD. However that field is case sensitive. We do not know what the users will input to do a search, therefore I need to search regardless of whether it is in uppercase, lowercase or mixed. I understand that it can be done by EXEC SQL. But besides this, any other method?

We are using MSSQL now but will be migrating to DB2 soon. I heard that the EXEC SQL is server dependent. Is it so?

If yes, can somebody help me to code the following to be native SQL for MSSQL and DB2? (Actually hope not to use this method)

SELECT f_{vbeln f}posnr INTO TABLE lt_vbeln

FROM vbkd AS f JOIN vbup AS p ON f_{vbeln = p}vbeln AND

f_{posnr = p}posnr JOIN vbak AS k ON f_{vbeln = k}vbeln

WHERE f_{bstkd_e in lr_po_num AND p}absta NE 'C' AND k~auart = 'ZOR'.

Thanks in advance.

Re: Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 10:31:51 GMT

convert the input that user enters into uppercase always. Irrespective of whether the user entrs in upper case or lower case, convert it into uppercase always and then try to search the field.

Re: Select statement to search - case sensitive

Sandeep_Panghal — Thu, 23 Jul 2009 10:32:27 GMT

As far as i understand the case sensitive search can be handled with native SQL only.

I heard that the EXEC SQL is server dependent. Is it so? ==> yes

Regarding the syntax , it varies for diff DB platforms .

Re: Select statement to search - case sensitive

viquar_iqbal — Thu, 23 Jul 2009 10:32:28 GMT

you can convert the BSTKD_E for table VBKD value into uppercase /lowercase no matter what user enters and then use it in select query.

TRANSLATE text TO UPPER CASE.

Thanks

Viquar iqbal

Re: Select statement to search - case sensitive

kesavadas_thekkillath — Thu, 23 Jul 2009 10:35:57 GMT

Dear priti suryawanshi

what if the value in db table-field contains mixed cases ????

This Qn is discussed several times in this forum..

its better get the values into a itab and then use some functions on that field to sort it out !!!

Re: Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 11:40:18 GMT

Hi Priti,

The user will enter in the sales order and I don't think I can control on how they enter it and there are already records with mixed uppercase and lowercase. Therefore would prefer to to control it in the program itself.

Re: Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 11:42:02 GMT

Hi Sandeep,

Do you know the syntax for DB2 and MSSQL?

Re: Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 11:43:59 GMT

Hi Viquar,

I understand that this can work. But I will be pulling alot of data from the DB and this will slow down the performance of the program. Therefore wondering if there are better methods.

Re: Select statement to search - case sensitive

Former Member — Thu, 23 Jul 2009 11:44:38 GMT

Hi Keshu,

I understand that this can work. But this will be pulling alot of data from the DB and this will slow down the performance of the program. Therefore wondering if there are better methods.

Re: Select statement to search - case sensitive

Sandeep_Panghal — Thu, 23 Jul 2009 11:45:23 GMT

Unfortunately no , but you may search in other forms .

Re: Select statement to search - case sensitive

Former Member — Wed, 19 Aug 2009 06:26:07 GMT

Cannot find any better solution, therefore decided to translate the data in the table to uppercase using an update program. Also added in user exit to translate to uppercase for all future SO.