12-10-2012 8:01 PM
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.
12-10-2012 8:46 PM
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...
12-10-2012 8:34 PM
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
12-10-2012 8:45 PM
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
Isn't there any way I can add the above conditions in my select?
12-10-2012 8:58 PM
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
12-10-2012 9:14 PM
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.
12-10-2012 8:46 PM
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...
12-10-2012 9:03 PM
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.
12-10-2012 9:22 PM
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
12-10-2012 9:34 PM
Many thanks Amy I'll certainly using your recommendation and use subquery as you've showed above.