topic Re: Key problem in innerJoin in Application Development and Automation Discussions

Key problem in innerJoin

Former Member — Fri, 21 May 2010 15:21:59 GMT

Hi all,

Iam trying to get some fields from the tables cdpos and stpo using the following query

For example

select cdpos~valueold cdpos~tabname 
         stpo~stlr stpo~position stpo~component into CORRESPONDING FIELDS OF TABLE gt_ditab from
	 cdpos
	INNER JOIN stpo on cdpos~tabkey = stpo~(mandt+bomtype+bom+nodetype+counter)
	where cdpos~tabname = ' 'stpo'.

In the innerJoin " INNER JOIN stpo on cdpos_{tabkey = stpos}( mandtbomtypebomnodetypecounter)" - the cdpos~tabkey is a concateneted value from the fields(mandtbomtypebomnodetypecounter) in stpos. how to make a join condition here.

Regards

Koh

Re: Key problem in innerJoin

Former Member — Fri, 21 May 2010 15:29:49 GMT

You can't concatenate like that during a join iteslf. Also, I would seriously suggest you reconsider the query in general.

CDPOS by nature can get very large, since the change items in there are cross application. Your query, even if you could make the join, will definitely will have major issues in production since you'll be doing a full table scan.

I would first try to figure out what BOM header you're working with, and then look up CDHDR to get the change document header, then use the key to read from CDPOS.

Re: Key problem in innerJoin

Former Member — Fri, 21 May 2010 15:43:05 GMT

Hi daniel,

The above was just a test query to show my problem.

In my real query Iam using

select cdhdr~username cdhdr~udate cdpos~changeid cdpos~valuenew
         cdpos~valueold cdpos~tabname cdpos~feldname stpo~stlr
         stpo~stlr stpo~position stpo~component 
	 into corresponding fields of table gt_ditab
         from cdhdr
         inner join cdpos on cdhdr~objectclas = cdpos~objectclas
          and cdhdr~changenr = cdpos~changenr
        *+inner join stpo on cdpos~tabkey = stpo~(*+
            +*mandt+bomtype+bom+nodetype+counter)*+

	where where cdhdr~objectclas = 'stue'
        and cdhdr~udate in so_date
	AND cdpos~tabname = 'stpo'.

I know that it cant be concateneted as follows:

INNER JOIN stpo on cdpos_{tabkey = stpo}(mandtbomtypebomnodetypecounter).

How to rewrite the above join condition.That was my question....

Edited by: Kohinoor72 on May 21, 2010 5:49 PM

Edited by: Kohinoor72 on May 21, 2010 5:50 PM

Re: Key problem in innerJoin

Former Member — Fri, 21 May 2010 15:51:15 GMT

In that case, you won't be able to do the join.

You'll first need to retrieve STPO records that you want to use as the key for your search into an itab, then concatenate it into a compatible column of TABKEY (type CDTABKEY).

Afterwards, you can try to "use for all entries". However, i'd still provide the objectclass and objectid so you can avoid a full table scan.

Re: Key problem in innerJoin

Former Member — Fri, 21 May 2010 16:11:09 GMT

Daniel,

You'll first need to retrieve STPO records that you want to use as the key for your search into an itab.

How to get the STPO records I need. The cdpos~tabkey was my key to get the needed records from stpos otherwise I have to get all the stpo records into an itab.

i'd still provide the objectclass and objectid so you can avoid a full table scan.

where to provide the above condition.

Iam already providing it on

inner join cdpos on cdhdr_{objectclas = cdpos}objectclas

and cdhdr_{changenr = cdpos}changenr

Edited by: Kohinoor72 on May 21, 2010 7:04 PM

Re: Key problem in innerJoin

Former Member — Fri, 21 May 2010 17:49:44 GMT

If you know what BOM's you're dealing with, the objectid in CDHDR should be the concatenation of mandt, stlty, and stlnr.

Re: Key problem in innerJoin

Former Member — Sat, 22 May 2010 11:09:35 GMT

Hi,

so I can do something as follows right.

Instead of doing a check like this "cdpos_{tabkey = stpo}(mandtbomtypebomnodetypecounter).

I can also do it as follows right

cdpos_{tabkey = cdhdr}(mandant+objectid) and

CDHDR_{ObjectId = stpo}(mandt, stlty, and stlnr.)

select * from cdhdr into TABLE  gt_cdhdr where cdhdr~objectclas =
'stue' and cdhdr~udate in so_date.

 loop at gt_cdhdr.

      h_tabkey-low(3)    = gt_cdhdr-mandant.
      h_tabkey-low+3(18) = gt_cdhdr-objectid.
      append h_tabkey.
      h_mandant = gt_cdhdr-objectid+2(4).
      h_stlType = gt_cdhdr-objectid+6(4).
      h_stlNummer = gt_cdhdr-objectid+10(4).
      append  h_mandant.
      append  h_stlType.
      append  h_stlNummer.
   endloop.

Then make a select on cdpos with h_tabkey and on stpo with h_mandant,h_stlType and h_stlNummer.

Thanks

Edited by: Kohinoor72 on May 22, 2010 1:21 PM