Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select with SQL - retrieve no results

former_member654002
Participant
0 Kudos

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

1 ACCEPTED SOLUTION

former_member654002
Participant
0 Kudos

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

9 REPLIES 9

Former Member
0 Kudos

What happens if you use single quotes instead of double quotes?

Rob

Former Member
0 Kudos

are you supplying month number (eg: 12) and year(2014) for these fields?


AND TO_CHAR(DOCDATE, ''MM'') = ?'
'AND TO_CHAR (DOCDATE, ''YYYY'') = ?'

former_member654002
Participant
0 Kudos

Hi,

Thank you for your answers.

Rob:

I am gona try it

Abdul

Yes i am supplyng 12 and 2014.

Best regards

jayanthi_jayaraman
Active Contributor
0 Kudos

Hi,

As suggested, try with single quotes.

'AND TO_CHAR(DOCDATE, 'MM') = ?'
'AND TO_CHAR (DOCDATE, 'YYYY') = ?'

0 Kudos

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.

former_member654002
Participant
0 Kudos

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

former_member654002
Participant
0 Kudos

former_member654002
Participant
0 Kudos

Above thre is an image of the error we get

0 Kudos

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