Select statement to search - case sensitive

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[kesavadas_thekkillath]

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 !!!

[Former Member]

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.

[Former Member]

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.

[Sandeep_Panghal]

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 .

[Former Member]

Hi Sandeep,

Do you know the syntax for DB2 and MSSQL?

[Sandeep_Panghal]

Unfortunately no , but you may search in other forms .

[viquar_iqbal]

Hi

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

[Former Member]

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.

[Former Member]

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.