topic Re: Native SQL Statement help! in Application Development and Automation Discussions

Native SQL Statement help!

Former Member — Wed, 21 Apr 2010 08:50:34 GMT

Hi guys,

I need some help here.

I have a requirement to select from Microsoft SQL database

Question

How do I select with RANGE internal tables? As you can see, I have 4 range tables for this.

BElow are my codes

EXEC SQL.
    CONNECT TO 'CONNECTION_NAME'
  ENDEXEC.

  IF sy-subrc <> 0.
    MESSAGE 'Unable to connect to CONNECTION_NAME' TYPE 'E' DISPLAY LIKE 'I'.
    RETURN.
  ENDIF.

* Define database cursor
  EXEC SQL.
    OPEN dbcur FOR
            SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
                   qty, zopcd, bstnk, wekunnr
              FROM <TABLE_NAME>
             WHERE month IN i_month
               AND yyear IN i_year
               AND kunnr IN i_kunnr
               AND bstnk IN i_bstnk
  ENDEXEC.

* Fill itab
  DO.

    EXEC SQL.
      FETCH NEXT dbcur INTO :l_dest-name1,
                            :l_dest-zvctk,
                            :l_dest-kunnr,
                            :l_dest-yyear,
                            :l_dest-mmonth,
                            :l_dest-matnr,
                            :l_dest-qty,
                            :l_dest-zopcd,
                            :l_dest-bstnk,
                            :l_dest-wekunnr
    ENDEXEC.

    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      APPEND l_dest TO it_dest.
    ENDIF.
  ENDDO.

its giving me a shortdump

>>>>> OPEN dbcur FOR

35 SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,

36 qty, zopcd, bstnk, wekunnr

37 FROM v_promise_sa

38 WHERE month IN i_month[]

39 AND yyear IN i_year[]

40 AND kunnr IN i_kunnr[]

41 AND bstnk IN i_bstnk[]

Error message:

Database error text........: "[Microsoft][SQL Server Native Client 10.0][SQL

Server]Incorrect syntax near 'i_month'."

Database error code........: 102

Triggering SQL statement...: "SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,

qty, zopcd, bstnk, wekunnr FROM <TABLE_NAME> WHERE month IN i_month[] AND

yyear IN i_year[] AND kunnr IN i_kunnr[] AND bstnk IN i_bstnk[]"

Internal call code.........: "[DBDS/NEW DSQL]"

Please check the entries in the system log (Transaction SM21).

Can anyone guide me? If I do not enter the where clause, it is selecting fine.

Appreciate it guys! Thanks in advance!

Re: Native SQL Statement help!

Former Member — Wed, 21 Apr 2010 09:02:10 GMT

Hi,

we are not able to understand the problem which you are facing.

Be clear in short.

Regards,

Anil.

Re: Native SQL Statement help!

Former Member — Wed, 21 Apr 2010 09:10:31 GMT

Hi there

My problem is, I dont know how the syntax should be when it comes to do native select statements with WHERE clause using RANGE internal tables.

Its giving me short dump

The error is

Database error text........: "Microsofthttp://SQL Server Native Client 10.0[SQL
Server]Incorrect syntax near 'i_month'."
Database error code........: 102
Triggering SQL statement...: "SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
qty, zopcd, bstnk, wekunnr FROM <TABLE_NAME> WHERE month IN i_month[] AND
yyear IN i_year] AND kunnr IN i_kunnr[ AND bstnk IN i_bstnk[]"
Internal call code.........: "DBDS/NEW DSQL"
Please check the entries in the system log (Transaction SM21).

My question is, what am I doing wrong? Can someone guide me on the correct syntax for native select statement with RANGE values?

Re: Native SQL Statement help!

Former Member — Wed, 21 Apr 2010 09:16:37 GMT

Hi,

Check This ,


EXEC SQL.
    OPEN dbcur FOR
            SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
                   qty, zopcd, bstnk, wekunnr
              FROM <TABLE_NAME>
             WHERE month IN i_month "check this I think field is MMONTH
               AND yyear IN i_year
               AND kunnr IN i_kunnr
               AND bstnk IN i_bstnk
  ENDEXEC.

Regards,

Raghava Channoru

Re: Native SQL Statement help!

SuhaSaha — Wed, 21 Apr 2010 09:19:38 GMT

Hello,

I don't think IN is allowed in Native SQL.

Please note:

"Native SQL statements do not fall within the scope of ABAP and do not follow ABAP syntax"

Source: [http://help.sap.com/abapdocu_70/en/ABENNATIVESQL.htm]

An easy workaround will be select the data from the SQL server w/o the range table into the internal table & then delete the entries from the internal table which do not fall in the range.

DELETE it_dest
WHERE month NOT IN i_month
AND yyear NOT IN i_year
AND kunnr NOT IN i_kunnr
AND bstnk NOT IN i_bstnk

Just out of curiosity i checked what could be the native sql operator similar to IN. I got these links, may be they interest you:

[http://www.sql-tutorial.net/SQL-IN.asp]

[http://www.sql-tutorial.net/SQL-BETWEEN.asp]

BR,

Suhas

Edited by: Suhas Saha on Apr 21, 2010 2:55 PM

Re: Native SQL Statement help!

Former Member — Wed, 21 Apr 2010 09:33:42 GMT

Hi there,

Yea, I realized that earlier. Anyway its not that which is causing the error. Some progress now.

EXEC SQL.
    OPEN dbcur FOR
            SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
                   qty, zopcd, bstnk, wekunnr
              FROM v_promise_sa
             WHERE mmonth = :i_month
               AND yyear  = :i_year
               AND ( kunnr BETWEEN :i_kunnr-low AND :i_kunnr-high )
               AND ( bstnk BETWEEN :i_bstnk-low AND :i_bstnk-high )
  ENDEXEC.

No shortdump but the BETWEEN syntax is making it not select data. need to figure it out.

Re: Native SQL Statement help!

adrian_dorn — Wed, 21 Apr 2010 15:54:33 GMT

The fields :i_kunnr-low , :i_kunnr-high,etc. don't contain any data unless you are inside a LOOP. So basically you need something like this:


LOOP AT i_kunnr.
 LOOP at i_bstnk.
  EXEC SQL.
             SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
                   qty, zopcd, bstnk, wekunnr
              FROM v_promise_sa
             WHERE mmonth = :i_month
               AND yyear  = :i_year
               AND ( kunnr BETWEEN :i_kunnr-low AND :i_kunnr-high )
              AND ( bstnk BETWEEN :i_bstnk-low AND :i_bstnk-high )               
   ENDEXEC.
  ENDLOOP.
 ENDLOOP.

BTW: Does the table v_promise_sa exist in the SAP DDIC? If yes I would recommend to use Open-SQL with range tables instead of Native-SQL with nested LOOPs.

Re: Native SQL Statement help!

Former Member — Thu, 22 Apr 2010 16:24:07 GMT

Well, none of this will work if the range tables has anything but EQ, I and ranges of values.

It won't work with individual values, excluding or patterns.

Rob

Re: Native SQL Statement help!

adrian_dorn — Thu, 22 Apr 2010 16:57:08 GMT


> Well, none of this will work if the range tables has anything but EQ, I and ranges of values.
> 
> It won't work with individual values, excluding or patterns.
>

True.

By the code sample in SlowABAPer's last posting I assumed that the range tables have only BT records. If this assumption is not correct and the range tables have a mixture of EQ, CP, BT, etc, the situation gets more complicated. In this case I can only think of the following solution: Create an extra internal table ITAB with 4 fields

month

year

kunnr

bstnk

and fill this table with all possible records resulting from the values of the range tables. Afterwards loop over the resulting ITAB and inside the loop do a EXEC SQL with a WHERE which has just EQ operators.

Remark @ Slow ABAPer : Not really a nice coding, but you won't find a better way of doing it with NATIVE-SQL. Therefore as I mentioned in my previous posting: If the table exists in the SAP DDIC, you should use Open-SQL instead of Native-SQL.

Re: Native SQL Statement help!

Former Member — Thu, 22 Apr 2010 17:23:40 GMT


> By the code sample in SlowABAPer's last posting I assumed that the range tables have only BT records.

By the name Slow ABAPER has chosen, I wouldn't assume anything

The suggestion to use open SQL if possible would be ideal though.

Rob

Re: Native SQL Statement help!

Former Member — Mon, 26 Apr 2010 01:14:22 GMT

lol, Come guys, don't judge me by just my forum nick man. It was just something random during registration a few years back...was indeed new in ABAP language 😛

But anyway, the table v_promimse_sa is not in the DDIC, its some microsoft MYSQL database or something.

anyway what I did now:

EXEC SQL.
    CONNECT TO 'MSSQL_PROMISE'
  ENDEXEC.

  IF sy-subrc <> 0.
    MESSAGE 'Unable to connect to MSSQL_PROMISE' TYPE 'E' DISPLAY LIKE 'I'.
    RETURN.
  ENDIF.

* Define database cursor
  EXEC SQL.
    OPEN dbcur FOR
            SELECT name1, zvctk, kunnr, yyear, mmonth, matnr,
                   qty, zopcd, bstnk, wekunnr
              FROM v_promise_sa
             WHERE mmonth = :i_month
               AND yyear  = :i_year
*               AND ( kunnr BETWEEN :i_kunnr-low AND :i_kunnr-high )
*               AND ( bstnk BETWEEN :i_bstnk-low AND :i_bstnk-high )
  ENDEXEC.

* Fill itab
  DO.

    EXEC SQL.
      FETCH NEXT dbcur INTO :l_dest-name1,
                            :l_dest-zvctk,
                            :l_dest-kunnr,
                            :l_dest-yyear,
                            :l_dest-mmonth,
                            :l_dest-matnr,
                            :l_dest-qty,
                            :l_dest-zopcd,
                            :l_dest-bstnk,
                            :l_dest-wekunnr
    ENDEXEC.

    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      APPEND l_dest TO it_dest.
    ENDIF.
  ENDDO.

*    EXEC SQL.
*      CLOSE dbcur
*    ENDEXEC.

  DELETE it_dest WHERE kunnr NOT IN i_kunnr
                   AND bstnk NOT IN i_bstnk.

Well, after checking with the functional person, there are only 3 records so I guess the selection + filtering is working.

Re: Native SQL Statement help!

Former Member — Mon, 26 Apr 2010 02:25:24 GMT

So, if your question is answered, please mark it as such.

Rob

Re: Native SQL Statement help!

Former Member — Mon, 26 Apr 2010 03:07:39 GMT

Thanks guys I think it is solved.