Query SAP tables

SmashAndGrab
SmashAndGrab used Ask the Experts™
on
hi,

I need to create an SQL query that is performed by my application on SAP tables.  

The tables are:    
LTBP - Transfer order Item
LTBK - Transfer order header


Here's the existing code:

The following code is the driver for bringing back the open TR’s for the specified material. The is the LTBP (item table):

  sWhere = "SELECT TBNUM, TBPOS, MATNR, CHARG, MENGA, TAMEA, ALTME FROM LTBP WHERE " & _
                sMaterialClause & sBatchClause & " AND " & _
                "LGNUM = '" & mobjUser.Warehouse & "' AND ELIKZ <> 'X'"


New pseudo code:

Add a join to the header table to include the movement types:  101 and 103.
The movement type is in the header table.

  sWhere = "SELECT TBNUM, TBPOS, MATNR, CHARG, MENGA, TAMEA, ALTME FROM LTBP WHERE " & _
                sMaterialClause & sBatchClause & " AND " & _
                "LGNUM = '" & mobjUser.Warehouse & "' AND ELIKZ <> 'X'"    
JOIN TO THE HEADER TABLE (LTBK) field BWLVS (movement type is 101 OR 103)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I have been working on this today and have come up with this..

sWhere = "SELECT TBNUM, TBPOS, MATNR, CHARG, MENGA, TAMEA, ALTME FROM LTBP AS p JOIN LTBK AS k ON k.LGNUM = p.LGNUM and k.TBNUM = p.TBNUM WHERE  " & _
                sMaterialClause & sBatchClause & " AND " & _
                "p.LGNUM = '" & mobjUser.Warehouse & "' AND p.ELIKZ <> 'X'"  & _    
                  " AND ( k.BWLVS = '101' or  k.BWLVS = '103')"


however - it does not seem to be working at the moment.

Author

Commented:
I think its on the right lines however.

Author

Commented:
Screenshot of debugging..
I had to use a different query as this join would not work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial