cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to Fetch any data from Using Native SQL

former_member191434
Participant
0 Kudos

In the db table t_shopping_cart_manf there are something around 248 records whose SCM_MAKE_BUY_TAG = 'M' AND SCM_STO_STATUS <> '06'The Following SQL statement are not able to fetch any data from db table t_shopping_cart_manf into internal table t_scm

Please guide what might be the reason this statement not able to fetch any data --

EXEC SQL.
    CONNECT TO 'SURROUND_DB'
  ENDEXEC.
  TRY.
      EXEC SQL.
        OPEN dbcur FOR
          SELECT SCM_CART_NO, SCM_FOD_NO
                 FROM T_SHOPPING_CART_MANF
                 WHERE SCM_MAKE_BUY_TAG = 'M'
                 AND   SCM_STO_STATUS  <> '06'
      ENDEXEC.
    CATCH cx_sy_native_sql_error INTO t_exc_ref.
      w_error_text_oc = t_exc_ref->get_text( ).
      MESSAGE w_error_text_oc TYPE 'E'.
  ENDTRY.

TRY.
      DO.

        EXEC SQL.
          FETCH NEXT dbcur INTO :t_wa_scm-SCM_CART_NO, :t_wa_scm-SCM_FOD_NO
        ENDEXEC.

        IF sy-subrc <> 0.
          EXIT.
        ELSE.
          CLEAR : t_wb_scm.
          t_wb_scm-scm_cart_no = t_wa_scm-scm_cart_no.
          t_wb_scm-scm_fod_no  = t_wa_scm-scm_fod_no.
          APPEND t_wb_scm TO t_scm.
          CLEAR : t_wb_scm, t_wa_scm.
        ENDIF.
      ENDDO.
    CATCH cx_sy_native_sql_error INTO t_exc_ref.
      w_error_text_fc = t_exc_ref->get_text( ).
      MESSAGE w_error_text_fc TYPE 'E'.
  ENDTRY.
TRY.
      EXEC SQL.
        CLOSE dbcur
      ENDEXEC.

    CATCH cx_sy_native_sql_error INTO t_exc_ref.
      w_error_text_cc = t_exc_ref->get_text( ).
      MESSAGE w_error_text_cc TYPE 'E'.
  ENDTRY.
  EXEC SQL.
    DISCONNECT 'SURROUND_DB'
  ENDEXEC.

Thanks & regards

Saifur Rahaman

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member191434
Participant
0 Kudos

findout the soln

volker_borowski2
Active Contributor
0 Kudos

AND SCM_STO_STATUS '06'

looks lik eyou are missing an EQUAL Sign in this line ?

AND SCM_STO_STATUS = '06'

Volker

former_member191434
Participant
0 Kudos

No no volker I have used the comparison operator not equal to i.e NE

SELECT SCM_CART_NO, SCM_FOD_NO

FROM T_SHOPPING_CART_MANF

WHERE SCM_MAKE_BUY_TAG = 'M'

AND SCM_STO_STATUS NE '06'

Edited by: Saifur Rahaman on Oct 14, 2009 8:49 AM

markus_doehr2
Active Contributor
0 Kudos

What happens if you exceute that SQL directly on the database; do you get any records?

Markus

former_member191434
Participant
0 Kudos

Sir , i didnt get your point.. I am using this in ABAP...

Now i am stucking whether i have to take any authorization in the userid what i am using to perform this or is there any syntax or logical error in coding ???

Edited by: Saifur Rahaman on Oct 13, 2009 4:39 PM

markus_doehr2
Active Contributor
0 Kudos

My question is:

if you execute the same statement you use in the ABAP using SQLPLUS, do you get any data?

Markus

former_member191434
Participant
0 Kudos

Sir I dont have SQLPLUS installed in my desktop. So not able to R & D using it. Most probably it must fetch the data ....

Exactly I dont know sir... you please guide what will happen in sql plus and why it is not happening in ABAP

markus_doehr2
Active Contributor
0 Kudos

> Exactly I dont know sir... you please guide what will happen in sql plus and why it is not happening in ABAP

That's what I'm trying to figure out.

Maybe there's a conversion issue (character vs. number) - or you need leading zeros. Without knowing how the data is really stored on the database (not converted via SE16) it's just guessing what can be wrong.

Markus

former_member191434
Participant
0 Kudos

Sir ,

The thing what i know is

scm_cart_no of table t_shopping_cart_manf is a number field of size 10 and

scm_fod_no of table t_shopping_cart_manf is a varchar2 having size 12

I have declared above 2 fields in ABAP is as below

TYPES : BEGIN OF ty_scm,
         scm_cart_no(10) TYPE n,  "" Shopping Cart Number
         scm_fod_no(12)  TYPE c,  "" Production Order Number
       END OF ty_scm.
DATA : t_scm TYPE STANDARD TABLE OF ty_scm INITIAL SIZE 0.
DATA : t_wa_scm TYPE ty_scm.
DATA : t_wb_scm TYPE ty_scm.

Then also it is not able to get any data... and exception is also not raising if it is not able to fetch any data.

Sir in debugging one thing i have found when following part of code is getting executed it is giving return value = 4

EXEC SQL.
          FETCH NEXT dbcur INTO :t_wa_scm-SCM_CART_NO, :t_wa_scm-SCM_FOD_NO
        ENDEXEC.

Edited by: Saifur Rahaman on Oct 13, 2009 6:51 PM

former_member191434
Participant
0 Kudos

Markus sir

I have worked out on the

EXEC SQL.
    CONNECT TO 'SURROUND_DB'
  ENDEXEC.
OPEN dbcur FOR  SELECT SCM_CART_NO, SCM_FOD_NO
                 FROM T_SHOPPING_CART_MANF
                 WHERE SCM_MAKE_BUY_TAG = 'M'
                 AND SCM_STO_STATUS NE '06'

and

TRY.
      DO.

        EXEC SQL.
          FETCH NEXT dbcur INTO :t_wa_scm-SCM_CART_NO, :t_wa_scm-SCM_FOD_NO
        ENDEXEC.

        IF sy-subrc <> 0.
          EXIT.
        ELSE.

The thing what i have found out for db connection 'SURROUND_DB' when i am using the ""OPEN dbcur FOR

SELECT"" with out where condition ""AND SCM_STO_STATUS NE '06'"" i am getting some values in the internal table t_scm but when i am using this ""AND SCM_STO_STATUS NE '06'"" in where condition it is throwing return value 4..

my question why it is happening like this ..

Former Member
0 Kudos

Hi,

NE is not supported in native SQL on Oracle.

use instead NE the relational operator !=

OPEN dbcur FOR  SELECT SCM_CART_NO, SCM_FOD_NO
               FROM T_SHOPPING_CART_MANF
                  WHERE SCM_MAKE_BUY_TAG = 'M'
                  AND SCM_STO_STATUS !=  '06'

always have your manual at your side:

http://www.oracle.com/pls/db102/homepage

bye

yk