12-18-2014 6:55 PM
Hi Abapers,
We are using the following code to retrieve data from an oracle database.
We get results but if we add the lines
'AND TO_CHAR(DOCDATE, ''MM'') = ?'
'AND TO_CHAR (DOCDATE, ''YYYY'') = ?'
we get no results.
so what are doing wrong in these two lines?
Thank you and best regards
*-----------------------------------------------------------------------
* Form le_doc_header
* Le doc_header
*-----------------------------------------------------------------------
FORM le_doc_header USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA: l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_sqlerr_ref TYPE REF TO cx_sql_exception,
l_res_ref TYPE REF TO cl_sql_result_set,
l_wa_company TYPE descarga-company,
l_wa_Branch TYPE descarga-Branch,
l_wa_NombreDescarga TYPE descarga-NombreDescarga,
l_wa_monat TYPE c LENGTH 2,
l_wa_gjahr TYPE c LENGTH 4,
* l_wa_monat TYPE monat,
* l_wa_gjahr TYPE gjahr,
l_wa_itab TYPE STANDARD TABLE OF doc_header,
l_row_cnt TYPE i.
TRY.
CONCATENATE bd_name '.CAF_VSW_ERP_DOC_HEADER' INTO c_tabname.
CONCATENATE 'select * from' c_tabname
'where COMPANY = ? AND BRANCH = ?'
'AND NombreDescarga = ?'
'AND TO_CHAR(DOCDATE, ''MM'') = ?'
'AND TO_CHAR (DOCDATE, ''YYYY'') = ?'
INTO l_stmt SEPARATED BY space.
l_wa_company = empre.
l_wa_branch = pardepar.
l_wa_NombreDescarga = NombreDescarga.
l_wa_monat = monat.
l_wa_gjahr = gjahr.
l_stmt_ref = p_con_ref->create_statement( ).
GET REFERENCE OF l_wa_company INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_wa_Branch INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_wa_NombreDescarga INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_wa_monat INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_wa_gjahr INTO l_dref.
l_stmt_ref->set_param( l_dref ).
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
GET REFERENCE OF l_wa_itab into l_dref.
l_res_ref->set_param_table( l_dref ).
MOVE l_wa_itab[] TO doc_header[].
CATCH cx_sql_exception INTO l_sqlerr_ref.
l_res_ref->close( ).
ENDTRY.
ENDFORM. " Le doc_header
12-19-2014 9:45 AM
Hi,
Thank you.
We try wiht single quotes but we get an error.
The statement we get, as we show you with the double quotes are.
select * from RVDSD.CAF_VSW_ERP_DOC_HEADER where COMPANY = ? AND BRANCH = ? AND NombreDescarga = ? AND TO_CHAR(DOCDATE, 'MM') = ? AND TO_CHAR (DOCDATE, 'YYYY') = ?
As you can see we get a single quote when we query the database
Thank you and best regards
12-18-2014 7:05 PM
What happens if you use single quotes instead of double quotes?
Rob
12-18-2014 7:07 PM
are you supplying month number (eg: 12) and year(2014) for these fields?
AND TO_CHAR(DOCDATE, ''MM'') = ?'
'AND TO_CHAR (DOCDATE, ''YYYY'') = ?'
12-19-2014 8:58 AM
Hi,
Thank you for your answers.
Rob:
I am gona try it
Abdul
Yes i am supplyng 12 and 2014.
Best regards
12-19-2014 9:18 AM
Hi,
As suggested, try with single quotes.
'AND TO_CHAR(DOCDATE, 'MM') = ?'
'AND TO_CHAR (DOCDATE, 'YYYY') = ?'
12-19-2014 10:24 AM
Hi,
what about the below statement?
CONCATENATE bd_name '.CAF_VSW_ERP_DOC_HEADER' INTO c_tabname.
CONCATENATE 'select * from' c_tabname
'where COMPANY = ? AND BRANCH = ?'
'AND NombreDescarga = ?'
'AND TO_CHAR(' DOCDATE ', ''MM'') = ?'
'AND TO_CHAR (' DOCDATE', ''YYYY'') = ?'
Since the usual syntax for oracle seems to be with to_char(docdate, 'MM') and to_char(docdate, 'YYYY'), we need to check whether docdate can be passed as variable which holds value and rest others in quotes like you had done for c_tabname.
Double quotes with DOCDATE should be used as variable holding values and quotes should end and start after this.
Just give a try. It may help.
12-19-2014 9:45 AM
Hi,
Thank you.
We try wiht single quotes but we get an error.
The statement we get, as we show you with the double quotes are.
select * from RVDSD.CAF_VSW_ERP_DOC_HEADER where COMPANY = ? AND BRANCH = ? AND NombreDescarga = ? AND TO_CHAR(DOCDATE, 'MM') = ? AND TO_CHAR (DOCDATE, 'YYYY') = ?
As you can see we get a single quote when we query the database
Thank you and best regards
12-19-2014 9:53 AM
12-19-2014 9:54 AM
12-23-2014 1:54 AM
Hi Joao,
I think the problem is because DOCDATE is not stored as date in Oracle database. Please note that SAP stores date as character and the format is YYYYMMDD.
So the solution is to change DOCDATE to date and then you can get the month or year portion of it.
TO_CHAR(TO_DATE(begda, 'YYYYMMDD'), 'YYYY')
To give you an example, please find it following.
INFOTYPES: 0105.
DATA: p_con_ref TYPE REF TO cl_sql_connection.
DATA: l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_sqlerr_ref TYPE REF TO cx_sql_exception,
l_res_ref TYPE REF TO cl_sql_result_set,
lv_param1 TYPE c LENGTH 30,
lv_param2 TYPE c LENGTH 10,
lt_result TYPE STANDARD TABLE OF pa0105,
lwa_result LIKE LINE OF lt_result.
TRY.
CREATE OBJECT p_con_ref.
CONCATENATE 'select * from pa0105 '
' where usrid_long = ? '
' AND usrty = ''0010'' '
' AND TO_CHAR(TO_DATE(begda, ''YYYYMMDD''), ''YYYY'') = ? ' "Please note you need to put two single apostrophe instead of double apostrophe
INTO l_stmt SEPARATED BY space.
l_stmt_ref = p_con_ref->create_statement( ).
lv_param1 = 'YOUREMAIL@YOURCOMPANY.COM.AU'.
GET REFERENCE OF lv_param1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
lv_param2 = '2009'.
GET REFERENCE OF lv_param2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
GET REFERENCE OF lt_result into l_dref.
l_res_ref->set_param_table( l_dref ).
IF l_res_ref->next_package( ) > 0 .
LOOP AT lt_result INTO lwa_result.
WRITE:/ lwa_result-usrid_long, lwa_result-begda.
ENDLOOP.
ENDIF.
CATCH cx_sql_exception INTO l_sqlerr_ref.
ENDTRY.
Hopefully this will resolve your issue.
Additionally, you can use SQL command editor in DB02 transaction to test if your native sql can be executed properly or not.
Regards,
Stevanic