topic Re: Use of Native SQL in Application Development and Automation Discussions

Use of Native SQL

Former Member — Mon, 25 Jun 2007 11:41:22 GMT

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.

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 11:43:49 GMT

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!

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 11:47:28 GMT

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

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 11:58:40 GMT

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.

Re: Use of Native SQL

alex_m — Mon, 25 Jun 2007 12:03:24 GMT

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

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 12:06:35 GMT

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".

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 12:11:34 GMT

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.

Re: Use of Native SQL

Former Member — Mon, 25 Jun 2007 12:38:26 GMT

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