Use of Native SQL

[Former Member]

Can i use all sql statementsa which are used in oracle ? Basically I am more interested in oracle data conversion functions like to_date(),To_Char() with appropriate formats.

I know that to use oracle's native commands i have to use Native SQL and for that i have to write my query between EXEC SQL and ENDEXEC. But i am not able to use query that uses date conversion fuctions like to_date() and to_char().

Also i want to know the list of SQL statements which can be used with Native SQL in Oracle.

[Former Member]

Can i use all sql statementsa which are used in oracle ? Basically I am more interested in oracle data conversion functions like to_date(),To_Char() with appropriate formats.

I know that to use oracle's native commands i have to use Native SQL and for that i have to write my query between EXEC SQL and ENDEXEC. But i am not able to use query that uses date conversion fuctions like to_date() and to_char().

Also i want to know the list of SQL statements which can be used with Native SQL in Oracle.

[Former Member]

Hi Deval,

Trry this,

EXEC SQL PERFORMING read_dental.

select emp_ssn_num, emp_fname, emp_lname,

to_char(start_date, 'YYYYMMDD') as start_date,

rec_type,

to_char(eff_date, 'YYYYMMDD') as eff_date,

vendor_plan_id

into :dental

from opr$8oc.BVER_CURR_DENTAL_MASTER

ENDEXEC.

Reward if useful!

[Former Member]

It doesn't work. I issued the following statement ,

EXEC SQL PERFORMING LOOP_OUTPUT.

SELECT MANDT,VBELN,TO_CHAR(FKDAT,'YYYYMMDD') AS FKDAT,VTWEG

INTO :wa

FROM VBRK

WHERE MANDT = 300

ORDER BY VBELN

ENDEXEC.

but i receive the error as bellow

Error 1481 occured in the current database connection "DEFAULT".

[Former Member]

HI.

please refer this one.

To avoid the standard F4 help to be show, insert the event PROCESS ON-VALUE-REQUEST request in the program and add a field statement for the field that should trigger the F4 help. In the module called from PROCESS ON-VALUE-REQUEST request, call function module F4IF_FIELD_VALUE_REQUEST.

Example Code :

process before output.

.....

process after input.

.....

PROCESS ON VALUE-REQUEST.

FIELD sflight-carrid MODULE f4_help_for_carrid.

MODULE f4_help_for_carrid INPUT.

• NOTE:

• Tabname/fieldname is the name of the table and field

• for which F4 should be shown.

*

• Dynprog/Dynpnr/Dynprofield are the names of the Progran/Dynpro/Field

• in which the f4 value should be returned.

*

• Value: The value of the Dynpro field when calling the F4 help.

• You can limit the values shown, by inseting a value in this parameter

• e.g 'A*' to show only values beginning with A

CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST'

EXPORTING

tabname = 'SFLIGHT'

fieldname = 'CARRID'

• SEARCHHELP = ' '

• SHLPPARAM = ' '

dynpprog = 'ZDANY_F4_OWN_CALL'

dynpnr = '0100'

dynprofield = 'SFLIGHT-CARRID'

• STEPL = 0

value = 'A*'

• MULTIPLE_CHOICE = ' '

• DISPLAY = ' '

• SUPPRESS_RECORDLIST = ' '

• CALLBACK_PROGRAM = ' '

• CALLBACK_FORM = ' '

• TABLES

• RETURN_TAB =

• EXCEPTIONS

• FIELD_NOT_FOUND = 1

• NO_HELP_FOR_FIELD = 2

• INCONSISTENT_HELP = 3

• NO_VALUES_FOUND = 4

• OTHERS = 5

.

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDMODULE. " F4_help_for_carrid

To control F4 help in a selection screen use the AT SELECTION-SCREEN ON VALUE-REQUEST FOR event.Note that for ranges both the low and high value of the field must have there own ON VALUE-REQUEST

Example:

AT SELECTION-SCREEN ON VALUE-REQUEST FOR s_prctr-low.

PERFORM f4_help_carrid.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR s_prctr-high.

PERFORM f4_help_carrid

Rewards all helpfull answers.

Regards.

Jay

[Former Member]

I want ot change the format of Date like to retrive date in 'dd-mon-yy' or to retrive only 'Mon' from the supplied date.

[alex_m]

Please avoid NAIVE SQL. Instead u have lot of option in SQL to perform.

[Former Member]

Ok, Please tell me how to change the date format using Open SQL.I want to change the date format of all selected rows in single shot. means i don't want to perform loop on internal table to change the format.

[Former Member]

Hi.

please avoid Native SQL. try SQL as follows.

data:begin of itab occurs 0,

l_mandt type vbrk-mandt,

l_vbeln type vbrk-vbeln,

l_date type sy-datum.

end of itab

select MANDT VBELN <which filed for date> from vbrk into itab.

loop at itab.

write :itab-l_date.

endloop.

try this it will help for you.

rewards all helpfull answers.

regards.

Jay