topic Re: Basic select does not work in Application Development and Automation Discussions

Basic select does not work

former_member295881 — Mon, 10 Dec 2012 20:01:10 GMT

Hello Expert,

I'm trying to write the following select to fetch list of records based on the various filters. There are two fields in Standard SAP VBAK table (VDATU and BSTDK) where I'm keep getting error. On the first AND ( vdatu EQ bstdk ) I'm keep getting error that (Field "BSTDK" is unknown). However, when I look at VBAP both the fields are part of this table. Can anybody please point out my mistake and how to rectify it.

SELECT vbeln erdat vdatu bstdk
     FROM vbak
       INTO TABLE git_vbak
         WHERE erdat IN so_date
           "AND ( vdatu EQ bstdk )
           AND ( vbtyp EQ lc_vbtyp )
           AND ( auart EQ lc_zshp OR auart EQ lc_zlcy )
           AND ( augru EQ lc_sh OR augru EQ lc_lc )
           AND ( bsark EQ lc_zonl OR bsark EQ lc_zpws OR bsark EQ lc_zoff OR bsark EQ lc_zedi OR bsark EQ lc_zotb ).
           sort git_vbak by vbeln.

Many thanks in advance.

Re: Basic select does not work

amy_king — Mon, 10 Dec 2012 20:34:54 GMT

Hi Zero,

You are trying to compare two fields within the database record itself...

AND ( vdatu EQ bstdk )

rather than comparing a database field with a local value. The compiler is expecting bstdk to exist within your local program, just as the range so_date exists locally and the constants lc_vbtyp, lc_zshp, lc_zlcy, etc exist locally.

By the way, you can substitute the parenthetical OR expressions...

AND ( augru EQ lc_sh OR  augru EQ lc_lc )

with a range...

AND augru IN (lc_sh, lc_lc)

Also, instead of sorting your table after the select, you can sort the table as part of the select for better performance...

SELECT ...
               FROM ...
               WHERE ...
               ORDER BY vbeln

Cheers,

Amy

Re: Basic select does not work

former_member295881 — Mon, 10 Dec 2012 20:45:33 GMT

Hi Amy, Many thanks for your reply. As I explained I'm selecting record based on filter and the only reason to write this program is to find records where

AND ( vdatu EQ bstdk )
AND ( vdatu NE bstdk )

Isn't there any way I can add the above conditions in my select?

Re: Basic select does not work

Jelena_Perfiljeva — Mon, 10 Dec 2012 20:46:29 GMT

If you're trying to get a record where VBAK-BSTDK = VBAK-VDATU then it can't be done like this. You either need a subquery (see ABAP Help) or select into an internal table (like you're already doing) and then run through that internal table comparing the fields.

The error message is because after 'VDATU EQ...' some local variable is expected and obviously there is no variable with the name BSTDK.

Just a note - instead of the multiple OR conditions you might want to have those values in a range (or even as a selection criteria). Also you don't need parenthesis if there is just one statement. The whole thing is barely readable...

Re: Basic select does not work

amy_king — Mon, 10 Dec 2012 20:58:21 GMT

Hi Zero,

I recommend joining the table to itself and specifying as a join condition that field bstdk equals field vdatu...

SELECT t1~vbeln t1~erdat t1~vdatu t1~bstdk
                FROM vbak AS t1
                JOIN vbak AS t2 ON    t2~vbeln = t1~vbeln
                                              AND t2~bstdk = t1~vdatu
                INTO TABLE git_vbak
                WHERE t1~erdat IN   so_date
                AND       t1~vbtyp EQ lc_vbtyp
                AND       t1~auart IN   (lc_zshp, lc_zlcy)
                AND       t1~augru IN  (lc_sh, lc_lc)
                AND       t1~ bsark IN  (lc_zonl, lc_zpws, lc_zoff, lc_zedi, lc_zotb)
                ORDER BY t1~vbeln.

Cheers,

Amy

Re: Basic select does not work

former_member295881 — Mon, 10 Dec 2012 21:03:22 GMT

Thanks Jelena for your reply. I understand it now so I've to pull all records from VBAK and populate two tables one where VDATU = BSTDK and other where VDATU <> BSTDK. My concern is performance though. Because there are morethen 76 million records I'll be pulling and applying my logic. If I loop though that many records will that be okay? Please advice.

Re: Basic select does not work

former_member295881 — Mon, 10 Dec 2012 21:14:47 GMT

Many thanks Amy for suck a big help. I wrote the following INNER JOIN

I'm having it transported to Staging but still my fear is volume. There are more then 76 million records so let see how the above JOIN works.

Re: Basic select does not work

amy_king — Mon, 10 Dec 2012 21:22:04 GMT

Hi Zero,

To find the records where vdatu and bstdk have different values, I recommend using a subquery. Here the subquery identifies the records where vdatu and bstdk match, and the main query reads vbeln records not in that set.

SELECT vbeln erdat vdatu bstdk
                FROM vbak
                INTO TABLE git_vbak
                WHERE erdat IN so_date
                AND       vbtyp EQ lc_vbtyp
                AND       auart IN  (lc_zshp, lc_zlcy)
                AND       augru IN (lc_sh, lc_lc)
                AND       bsark IN (lc_zonl, lc_zpws, lc_zoff, lc_zedi, lc_zotb)
                AND       vbeln NOT IN ( SELECT t1~vbeln
                                                                          FROM vbak AS t1
                                                                          JOIN vbak AS t2 ON    t2~vbeln = t1~vbeln
                                                                                                        AND t2~bstdk = t1~vdatu )
                ORDER BY vbeln.

Cheers,

Amy

Re: Basic select does not work

former_member295881 — Mon, 10 Dec 2012 21:34:08 GMT

Many thanks Amy I'll certainly using your recommendation and use subquery as you've showed above.