CL_SQL_STATEMENT versus direct SELECT

[former_member192854]

Hi Guys,

at this moment I want to create a generic function that is able to fetch data directly from the database via a query.

Since I want to have some freedom in adding nested selects etcetera I wanted to use the CL_SQL_STATEMENT class.

Unfortunatly this class doesn't return the same result as a direct select. It seems that the numerical fields get shifted (after 3000 characters). See screenshot/code sample and database definition below. Any suggestions?

Please consider the following (very large) database definition for my table (ZTB_IU_NBI_RAW):

FIELD	TYPE	LENGTH	DECIMALS
MANDT	CLNT	3	0
MESSAGEID	CHAR	15	0
MESSAGETYPE	CHAR	50	0
MESSAGESEQNR	CHAR	10	0
CREATEUSER	CHAR	12	0
CREATEDATE	DATS	8	0
CREATETIME	TIMS	6	0
EANCODE	CHAR	18	0
BPNUONID	CHAR	10	0
BPNAME	CHAR	40	0
BILLACCOUNTNAME	CHAR	40	0
BILLACCOUNTNAME2	CHAR	40	0
BPPA	CHAR	40	0
ENERGYTYPECODE	CHAR	2	0
BILLPA	CHAR	40	0
BPSTREETADDR	CHAR	60	0
BPHOUSENUMBER	CHAR	10	0
BPHOUSESUFFIX	CHAR	10	0
BPPOSTCODE	CHAR	10	0
BPCITY	CHAR	40	0
BPCOUNTRY	CHAR	3	0
BILLSTREETADDR	CHAR	60	0
BILLHOUSENUMBER	CHAR	10	0
BILLHOUSESUFFIX	CHAR	10	0
BILLPOSTCODE	CHAR	10	0
BILLCITY	CHAR	40	0
BILLCOUNTRY	CHAR	3	0
BILLEMAILADDR	CHAR	132	0
BILLPID	CHAR	50	0
BPKIND	CHAR	4	0
BPLEGALFORM	CHAR	2	0
INCOMINGBANKKEY	CHAR	15	0
INCOMINGBANKNR	CHAR	34	0
OUTGOINGBANKKEY	CHAR	15	0
OUTGOINGBANKNR	CHAR	34	0
VCRACCOUNTNUMBER	CHAR	12	0
CRDESCRIPTION	CHAR	35	0
SUPPRESSBBFLAG	CHAR	1	0
SIEBELBILLTYPE	CHAR	30	0
TERMSOFPAYMENT	CHAR	2	0
ACCOUNTDETID	CHAR	2	0
DUNNINGPROCEDURE	CHAR	2	0
PAYMENTMETHOD	CHAR	30	0
CONTRACTNAME	CHAR	35	0
COMPANYRECEIVING	CHAR	4	0
COSTCENTERRCV	CHAR	10	0
SIEBELDEVCAT	CHAR	30	0
SINGLETARIFF	CHAR	3	0
BBBEGININGEND	CHAR	30	0
PROFILECATEGORY	CHAR	8	0
GOSEAN	CHAR	18	0
GRIDOPERATOR	CHAR	18	0
PRODUCTNAME	CHAR	30	0
CONTRACTYRVOLG	CHAR	17	0
CONTRACTYRVOLE	CHAR	17	0
GASSALESOFFSET	CHAR	17	0
GASFIXEDFEE	CHAR	17	0
COMPANYPCT	CHAR	17	0
LOWVATRATE	CHAR	1	0
NROFHOUSES	CHAR	17	0
GASFIXED	CHAR	10	0
GASINDEXPRICE	CHAR	17	0
GASSALESPRICE	CHAR	17	0
BRANCHCODE	CHAR	30	0
CONTRACTID	CHAR	30	0
PURCHASECOMBID	CHAR	30	0
CONVENANTAGRMT	CHAR	1	0
NONBUSINESSUSAGE	CHAR	1	0
EKWCHARGE	CHAR	10	0
SPLITPURCHASE	CHAR	1	0
CAPACITY	CHAR	17	0
EOFFPEAKPRICE	CHAR	10	0
EPEAKPRICE	CHAR	10	0
EFIXEDFEE	CHAR	10	0
HERPRIJSFLAG	CHAR	1	0
EINTEGRALPRICE	CHAR	10	0
DEVSTREETADDR	CHAR	60	0
DEVHOUSENR	CHAR	10	0
DEVHOUSENRSFFX	CHAR	10	0
DEVICECITY	CHAR	40	0
DEVICEPOSTCODE	CHAR	10	0
PPSTARTDATE	CHAR	10	0
PPENDDATE	CHAR	10	0
ASSETSTARTDATE	CHAR	10	0
ASSETENDDATE	CHAR	10	0
ASSETMOVEINDATE	CHAR	10	0
ASSETMOVEOUTDATE	CHAR	10	0
REVISIONREASON	CHAR	50	0
VIGROUPING	CHAR	3	0
CONTRACTSHORT	CHAR	3	0
GVKVINDICATOR	CHAR	2	0
DUNBRADSTREETNR	CHAR	60	0
KVKNUMBER	CHAR	60	0
SIEBELUSERID	CHAR	12	0
EBDECSTARTDATE	CHAR	10	0
EBDECENDDATE	CHAR	10	0
EBDECTYPE	CHAR	50	0
HEFFINGSKORTING	CHAR	1	0
VCRDESCRIPTION	CHAR	35	0
PAYEEACCOUNTNAME	CHAR	40	0
PAYEEACCOUNTNAM2	CHAR	40	0
PAYEEPA	CHAR	40	0
PAYEESTREETADDR	CHAR	60	0
PAYEEHOUSENUMBER	CHAR	10	0
PAYEEHOUSESUFFIX	CHAR	10	0
PAYEEPOSTCODE	CHAR	10	0
PAYEECITY	CHAR	40	0
PAYEECOUNTRY	CHAR	3	0
PAYEEEMAILADDR	CHAR	132	0
PAYEEPID	CHAR	50	0
BBBILLMONTH	CHAR	2	0
BBFREQUENCY	CHAR	30	0
REVACTIVDATE	CHAR	10	0
PEAKPERCENTAGE	CHAR	6	0
EDSNBILLINGMONTH	CHAR	2	0
PAYERACCOUNTNAME	CHAR	40	0
PAYERACCOUNTNAM2	CHAR	40	0
PAYERPA	CHAR	40	0
PAYERSTREETADDR	CHAR	60	0
PAYERHOUSENUMBER	CHAR	10	0
PAYERHOUSESUFFIX	CHAR	10	0
PAYERPOSTCODE	CHAR	10	0
PAYERCITY	CHAR	40	0
PAYERCOUNTRY	CHAR	3	0
PAYEREMAILADDR	CHAR	132	0
PAYERPID	CHAR	50	0
COPYINVOICE	CHAR	1	0
FIXEDREGIONCOST	CHAR	10	0
LINEITEMSTARTDAT	CHAR	10	0
COMMINTERNAME	CHAR	30	0
COMMINTERBTW	CHAR	30	0
COMMFEEE	CHAR	10	0
COMMFEEG	CHAR	10	0
COMMFEEASSET	CHAR	10	0
METERNO	CHAR	18	0
CAPACITYPHYSIC	CHAR	5	0
PHYSICALSTATUS	CHAR	3	0
ADMINSTATUS	CHAR	3	0
METERTYPE	CHAR	3	0
METERINGMETHOD	CHAR	3	0
CAPACITYTARIFF	CHAR	13	0
TEMPERATURECORR	CHAR	1	0
DIALID1	CHAR	5	0
TARIFFTYPE1	CHAR	1	0
METERINGDIR1	CHAR	3	0
MEASUREUNIT1	CHAR	3	0
NRDIGITS1	NUMC	1	0
MULTPLICFACTOR1	CHAR	9	0
DIALID2	CHAR	5	0
TARIFFTYPE2	CHAR	1	0
METERINGDIR2	CHAR	3	0
MEASUREUNIT2	CHAR	3	0
NRDIGITS2	NUMC	1	0
MULTPLICFACTOR2	CHAR	9	0
DIALID3	CHAR	5	0
TARIFFTYPE3	CHAR	1	0
METERINGDIR3	CHAR	3	0
MEASUREUNIT3	CHAR	3	0
NRDIGITS3	NUMC	1	0
MULTPLICFACTOR3	CHAR	9	0
DIALID4	CHAR	5	0
TARIFFTYPE4	CHAR	1	0
METERINGDIR4	CHAR	3	0
MEASUREUNIT4	CHAR	3	0
NRDIGITS4	NUMC	1	0
MULTPLICFACTOR4	CHAR	9	0
NODIALS	CHAR	1	0
COLLECTIONMETHOD	CHAR	4	0
COLLECTIONZPID	CHAR	10	0
COLLZPFIRSTNAME	CHAR	40	0
COLLZPPREFIX	CHAR	20	0
COLLZPLASTNAME	CHAR	40	0
COLLZPEMAIL	CHAR	132	0
COLLZPSTREET	CHAR	60	0
COLLZPHOUSENR	CHAR	10	0
COLLZPHOUSESUF	CHAR	10	0
COLLZPPOSTCODE	CHAR	10	0
COLLZPCITY	CHAR	40	0
COLLZPCOUNTRY	CHAR	3	0
REASONID	CHAR	3	0
TRANSACTIONID	CHAR	25	0
ANLART	CHAR	4	0
VKO	CHAR	3	0
MNDID	CHAR	35	0
B2B	CHAR	1	0
VAL_FROM_DATE	DATS	8	0
FORCEENDBILL	CHAR	1	0
VAL_START	CHAR	10	0
TYPE_OF_SUPPLY	CHAR	1	0

Please consider this code:

REPORT ztest_cl_sql_statement.

DATA scr_messageid TYPE zde_iu_nbi_messid.
SELECT-OPTIONS so_mess FOR scr_messageid.

INITIALIZATION.

  so_mess[] = VALUE #( SIGN = 'I' OPTION = 'EQ' ( LOW = '1-IAENKH' )
  ( LOW = '1-IDS6SW' )
  ( LOW = '1-IDSL1O' )
  ( LOW = '1-II9KK6' )
  ( LOW = '1-IIKMUM' )
  ( LOW = '1-IIKMUS' )
  ( LOW = '1-IKM9L9' ) ).


START-OF-SELECTION.
  DATA lv_query TYPE STRING.
  DATA lt_test TYPE STANDARD TABLE OF ztb_iu_nbi_raw.
  DATA lt_testd TYPE STANDARD TABLE OF ztb_iu_nbi_raw.

  lv_query = |SELECT * FROM ZTB_IU_NBI_RAW WHERE mandt = '261'|.

  DATA lv_sub TYPE STRING.
  LOOP AT so_mess ASSIGNING FIELD-SYMBOL(<fs_m>).
   IF sy-tabix EQ 1.
  lv_sub = |AND messageid IN ('{ <fs_m>-low }' |.
   ELSEIF sy-tabix EQ LINES( so_mess ).
  lv_sub = |{ lv_sub }, '{ <fs_m>-low }')|.
   ELSE.
  lv_sub = |{ lv_sub }, '{ <fs_m>-low }'|.
   ENDIF.
  ENDLOOP.
  IF sy-subrc EQ 0. lv_query = |{ lv_query } { lv_sub }|. ENDIF.

* New SQL approach
  TRY.
   DATA(lo_query) = NEW cl_sql_statement( ).
   DATA(lo_result) = lo_query->execute_query( lv_query ).
  lo_result->set_param_table( REF #( lt_test ) ).
  lo_result->next_package( ).
  lo_result->CLOSE( ).
   CATCH cx_sql_exception INTO DATA(lx_sql).
   DATA(ev_message) = lx_sql->sql_message.
  ENDTRY.

* Versus old SQL approach
  SELECT * FROM ztb_iu_nbi_raw INTO TABLE lt_testd WHERE messageid IN so_mess.

* This statement fails - numeric fields in lt_nbid are shifted
  ASSERT lt_test = lt_testd.

[rosenberg_eitan]

Hi,

See if this post is usefully for you.

program Y_R_EITAN_TEST_28_01 is building internal table to match the selected fields .

Regards.

[former_member192854]

I'm performing a SELECT *. I did the declaration of the data type both via direct data statements (DATA lt_table TYPE STANDARD TABLE OF ddic.

So the shifting issue will not be sovled via a custom build database table data declaration (nor using RTTI, nor the much re-used cl_alv_table_create).

[rosenberg_eitan]

Hi,

Just to eliminate human error .

Please try my program.

We have oracle.

Regards.