topic Select with SQL - retrieve no results in Application Development and Automation Discussions

Select with SQL - retrieve no results

Former Member — Thu, 18 Dec 2014 18:55:42 GMT

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

Re: Select with SQL - retrieve no results

Former Member — Thu, 18 Dec 2014 19:05:20 GMT

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

Rob

Re: Select with SQL - retrieve no results

Former Member — Thu, 18 Dec 2014 19:07:21 GMT

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

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

Re: Select with SQL - retrieve no results

Former Member — Fri, 19 Dec 2014 08:58:35 GMT

Hi,

Thank you for your answers.

Rob:

I am gona try it

Abdul

Yes i am supplyng 12 and 2014.

Best regards

Re: Select with SQL - retrieve no results

jayanthi_jayaraman — Fri, 19 Dec 2014 09:18:40 GMT

Hi,

As suggested, try with single quotes.

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

Re: Select with SQL - retrieve no results

Former Member — Fri, 19 Dec 2014 09:45:26 GMT

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

Re: Select with SQL - retrieve no results

Former Member — Fri, 19 Dec 2014 09:53:54 GMT

Re: Select with SQL - retrieve no results

Former Member — Fri, 19 Dec 2014 09:54:24 GMT

Above thre is an image of the error we get

Re: Select with SQL - retrieve no results

jayanthi_jayaraman — Fri, 19 Dec 2014 10:24:20 GMT

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.

Re: Select with SQL - retrieve no results

ec1 — Tue, 23 Dec 2014 01:54:46 GMT

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