How to use a where clause in a querry with data from a table currently selected?

[warcsi]

Hello

I have this select statement here.

The problem is I need to use the field ekkn~ablad where the psp elements are stored for orders in this system

And it is only the first couple characters that are saved in there and I was wondering how I can do it while also appending a wildcard to the end.

Want to thank you for any answers or pointers in advance

Sincerely András

SELECT c~pernr,ekkn~ablad, c~workdate,ekkn~ebeln,pa~kostl, pa~persk,c~rproj, c~catshours, c~ltxa1, ekko~kdatb, ekko~kdate FROM catsdb AS c<br>INNER JOIN pa0001 AS pa ON pa~pernr = c~pernr <br>INNER JOIN ekkn ON pa~kostl = ekkn~kostl<br>INNER JOIN ekko ON ekkn~ebeln = ekko~ebeln<br> WHERE c~pernr IN @s_pernr<br>    AND c~workdate IN @s_wdate                             <br>    AND c~rpoj like [ekkn~ablad + wildcard] <br>   AND pa~persk = 'AA'<br>    AND ( c~lstar = 'PROD' OR c~lstar = 'REISE' OR c~lstar = 'REISA' )<br>*    AND c~status = 30<br>  INTO TABLE @DATA(gt_testtable2).<br><br><br>

[xiswanto]

Hi warcsi

I'm not sure if the query you wanted is possible as I've never success in doing query case such as what you wanted to accomplish.

If I am to do it,
I would split the query into 2 part, first part: selecting the catsdb and pa0001 into one itab first,
then second part: selecting the ekkn and ekko based on field value of first itab into another itab,
then I would loop first itab, reading second itab, matching the catsdb-rproj and ekkn~ablad as per requirement ( such as rproj CP ekkn~ablad ), then deleting the record of 2nd itab where the sy-subrc of read table is false.

Another point, you still need to do conversion value for the psp field (RPROJ), so joining query might not work, unless the ekkn~ablad field saved with the input value of psp element, not the output value.

In summary, in my way, I would need to do the long way ( multiple select query, loop, read, check logic, append the work area into the final table which containing data from both itab ) instead of the short way ( joining all table with the single select )

[warcsi]

Hello thank you for your comment

I kind of got an idea on how to solve it, and that was to join the prps onto catsdb on the element of the PSP

as prps has both the converted and unconverted data.

Thank you again for your time

and have a great day!

[Sandra_Rossi]

Formatting problem in your question (one line with lots of <br>).

Well formatted:

SELECT c~pernr,ekkn~ablad, c~workdate,ekkn~ebeln,pa~kostl, pa~persk,c~rproj, c~catshours, 
       c~ltxa1, ekko~kdatb, ekko~kdate 
FROM catsdb AS c
INNER JOIN pa0001 AS pa ON pa~pernr = c~pernr 
INNER JOIN ekkn ON pa~kostl = ekkn~kostl
INNER JOIN ekko ON ekkn~ebeln = ekko~ebeln
 WHERE c~pernr IN @s_pernr
    AND c~workdate IN @s_wdate                             
    AND c~rpoj like [ekkn~ablad + wildcard] 
   AND pa~persk = 'AA'
    AND ( c~lstar = 'PROD' OR c~lstar = 'REISE' OR c~lstar = 'REISA' )
*    AND c~status = 30
  INTO TABLE @DATA(gt_testtable2). 

[Sandra_Rossi]

You can use this since ABAP 7.55 to mimic c~rproj LIKE [ ekkn~ablad + wildcard ] (NB: ignoring ABLAD trailing blanks):

SELECT c~pernr,ekkn~ablad, c~workdate,ekkn~ebeln,pa~kostl, pa~persk,c~rproj, c~catshours,
       c~ltxa1, ekko~kdatb, ekko~kdate
  FROM catsdb AS c
  INNER JOIN pa0001 AS pa ON pa~pernr = c~pernr
  INNER JOIN ekkn ON pa~kostl = ekkn~kostl
  INNER JOIN ekko ON ekkn~ebeln = ekko~ebeln
  WHERE c~pernr IN @s_pernr
    AND c~workdate IN @s_wdate
    AND left( c~rproj, length( ekkn~ablad ) ) = ekkn~ablad " AND c~rproj like [ekkn~ablad + wildcard]
    AND pa~persk = 'AA'
    AND ( c~lstar = 'PROD' OR c~lstar = 'REISE' OR c~lstar = 'REISA' )
*    AND c~status = 30
  INTO TABLE @DATA(gt_testtable2).