Reading Table data - case in-sensetivity.

[Former Member]

Hi ABAPers,

I have an issue in reading data from table.

Issue is : Need to read all recrods(upper, lower, alter) from 2 tables(join) depending on input value.

I tried with native sql

EXEC sql performing email_change.

SELECT usr21-bname usr21-persnumber adr6-SMTP_SRCH adr6-smtp_addr INTO wa_user_email

from ( usr21 left OUTER JOIN adr6 on usr21-persnumber = adr6-persnumber )

where UPPER(adr6-SMTP_addr) eq v_addr.

if sy-subrc <> 0.

MESSAGE i000.

ENDIF.

endexec.

I am getting short dump for this code.

Please help

Thank you.

Ameen

[Former Member]

Hi Ameen,

You can not write regular ABAP statements like sy-subrc check, IF condition and all in between EXEC SQL and ENDEXEC. Moreover you can use only host variables in native SQL. In your case it is :wa_user_mail (colon before the data object).

You can refer the documentation of Native SQL in ABAP Keyword documentation (TCode ABAPDOCU) for more details. I think your idea to use native SQL with UPPER function is a good idea in this case.

Regards

Suresh

[SureshRa]

Hi Ameen,

You can not write regular ABAP statements like sy-subrc check, IF condition and all in between EXEC SQL and ENDEXEC. Moreover you can use only host variables in native SQL. In your case it is :wa_user_mail (colon before the data object).

You can refer the documentation of Native SQL in ABAP Keyword documentation (TCode ABAPDOCU) for more details. I think your idea to use native SQL with UPPER function is a good idea in this case.

Regards

Suresh

[rakeshjain]

Hi Ameen,

You can modify your code like this -

DATA: exc_ref TYPE REF TO cx_sy_native_sql_error,

error_code TYPE n,

error_text TYPE string.

TRY.

EXEC SQL.

SELECT usr21.bname,

usr21.persnumber,

adr6.smtp_srch,

adr6.smtp_addr

INTO :wa_user_email

FROM ( usr21 left OUTER JOIN adr6 on usr21.persnumber = adr6.persnumber )

WHERE UPPER(adr6.SMTP_addr) = :v_addr

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exc_ref.

error_text = exc_ref->get_text( ).

error_code = exc_ref->sqlcode.

CONCATENATE error_text ' SQL CODE:' error_code ' MSG:' exc_ref->sqlmsg INTO error_text.

MESSAGE error_text TYPE 'I'.

ENDTRY.

This will solve your problem.

Regards,

Rakesh