topic Re: Multiple column subquery in Application Development and Automation Discussions

Multiple column subquery

alejandro_bindi — Fri, 18 May 2007 18:36:26 GMT

Hi,

Is it possible to do something like this in Open SQL (Subquery which returns more than one field)?:

SQL> SELECT order_id, product_id, quantity

FROM item

<b>WHERE (product_id, quantity) IN (

SELECT product_id, quantity FROM item WHERE order_it = 200)</b>

AND order_id = 200;

I want to do something similar for belnr AND gjahr.

Thanks

Re: Multiple column subquery

ferry_lianto — Fri, 18 May 2007 18:41:27 GMT

Hi,

Yes, you can do it.

Please check this sample code.


SELECT * FROM cdhdr
  INTO TABLE t_cdhdr
  FOR ALL ENTRIES IN t_cdpos
  WHERE objectclas ='MATERIAL'
    AND objectid = t_cdpos-objectid
    AND changenr IN
    ( SELECT MAX( changenr )
        FROM cdhdr
        WHERE objectclas ='MATERIAL'
          AND objectid = t_cdpos-objectid ).

Regards,

Ferry Lianto

Re: Multiple column subquery

Former Member — Fri, 18 May 2007 18:46:30 GMT

Example :

select matnr shkzg meins sum( fklmg )

into table i_billed

from ( vbrk as k INNER JOIN vbrp as p on k_{vbeln = p}vbeln )

where ( vbtyp = 'M' or "invoice

vbtyp = 'N' ) and "invoice cancellation

k~fkdat in so_vdatu and

matnr in so_matnr and

p~werks = p_werks

group by matnr shkzg meins.

Re: Multiple column subquery

alejandro_bindi — Fri, 18 May 2007 23:17:36 GMT

Thanks for answers, but:

- Ferry, your subquery returns a single field (changenr), i want to return multiple fields

- Seshu, your example doesn't involve a subquery.

Here's the actual query (this works but I think would improve with subquery):


SELECT DISTINCT ih~belnr ih~gjahr ih~waers
                il~buzei il~kschl il~wrbtr
FROM ekbe AS h
  INNER JOIN rbkp AS ih
    ON h~belnr = ih~belnr AND
       h~gjahr = ih~gjahr
  INNER JOIN rseg AS il
    ON ih~belnr = il~belnr AND
       ih~gjahr = il~gjahr
INTO TABLE lpi_invoices
WHERE h~ebeln = lp_po-ebeln AND
      h~bewtp = 'Q' AND
      ih~vgart = 'RD' AND
      ih~stblg = space.

Here's what i want to achieve, please look at the WHERE condition (this code gives error):


SELECT ih~belnr ih~gjahr ih~waers
       il~buzei il~kschl il~wrbtr
FROM rbkp AS ih
  INNER JOIN rseg AS il
    ON ih~belnr = il~belnr AND
       ih~gjahr = il~gjahr
INTO TABLE lpi_invoices
WHERE (ih~belnr, ih~gjahr) IN
      ( SELECT DISTINCT belnr gjahr
        FROM ekbe
        WHERE ebeln = lp_po-ebeln AND
              bewtp = 'Q' ) AND
      ih~vgart = 'RD' AND
      ih~stblg = space.

Please don't post against DISTINCT and in favor of SORT and DELETE ADJACENT DUPLICATES, I already know in some cases that's the recomendation but that's offtopic.

Thank you, please post if you know how to do this kind of subquery.

Re: Multiple column subquery

Former Member — Fri, 18 May 2007 23:23:36 GMT

Hi,

Did you try using INNER JOIN..Not sure if it can solve your purpose..

Thanks,

Naren

Re: Multiple column subquery

Former Member — Fri, 18 May 2007 23:24:38 GMT

Hi,

Did you try using INNER JOIN..Not sure if it can solve your purpose..



SELECT ih~belnr ih~gjahr ih~waers
       il~buzei il~kschl il~wrbtr
FROM rbkp AS ih
  INNER JOIN rseg AS il
    ON ih~belnr = il~belnr AND
       ih~gjahr = il~gjahr
  INNER JOIN ekbe as c       " Inserted code
    ON ih~belnr = c~belnr    " Inserted code
    AND ih~gjahr = c~gjahr   " Inserted code
  INTO TABLE lpi_invoices
  WHERE  c~ebeln    = lp_po-ebeln AND
         c~bewtp    = 'Q'  AND
         ih~vgart   = 'RD' AND
         ih~stblg   = space.

Thanks,

Naren

Re: Multiple column subquery

alejandro_bindi — Fri, 18 May 2007 23:48:33 GMT

Narendran, please look again at my post. What you did is the same as i have now (first query in my second post) but in different order and without DISTINCT. As you wrote it that results in duplicates since in EKBE the relation with invoices is at position level.

My purpose is already solved as it is but I want to optimize the query as much as possible since the data volume will be high in some cases, and the involved tables are huge. I think the subquery option will be better.

Thanks again

Re: Multiple column subquery

Former Member — Sat, 19 May 2007 03:12:22 GMT

Hi,

Ok..Include the BUZEI also...


SELECT ih~belnr ih~gjahr ih~waers
       il~buzei il~kschl il~wrbtr
FROM rbkp AS ih
  INNER JOIN rseg AS il
    ON ih~belnr = il~belnr AND
       ih~gjahr = il~gjahr
  INNER JOIN ekbe as c       " Inserted code
    ON ih~belnr = c~belnr    " Inserted code
    AND ih~gjahr = c~gjahr   " Inserted code
    AND il~buzei = c~buzei   " Inserted new code..
  INTO TABLE lpi_invoices
  WHERE  c~ebeln    = lp_po-ebeln AND
         c~bewtp    = 'Q'  AND
         ih~vgart   = 'RD' AND
         ih~stblg   = space.

Thanks,

Naren

Re: Multiple column subquery

alejandro_bindi — Sat, 19 May 2007 16:45:31 GMT

May work...but what I want to know is how to write the multiple column subquery (if that's even possible).

I've posted the actual query i want to modify so you can understand what i'm trying to do...but i don't need help or alternatives in that particular case, I just wanna learn how to do this for any query and haven't found anything about this in SAP help.

Thanks again Naren

Edit: You post made me think again and ask the user again: i do need to take into account the Buzei field, although EKBE-BUZEI is not compatible with RSEG-BUZEI so the join won't work. So i assigned more points to you.

However, i still wanna know how to do the subquery thing, so i'll leave the topic open.

Re: Multiple column subquery

sinan_keklik — Tue, 22 May 2007 08:54:58 GMT

I think that multiply column subqueries are not possible.

Try FOR ALL ENTRIES.

You first save your subquery with multiple columns in an internal table and then make FOR ALL ENTRIES

SELECT product_id, quantity FROM item INTO TABLE itab1

WHERE order_it = 200.

SELECT value FROM table INTO itab2

FOR ALL ENTRIES IN itba1

WHERE product_id = itab1-product_id

AND quantity = itab1-quantity.

This is really fast.

Sinan

Re: Multiple column subquery

alejandro_bindi — Tue, 22 May 2007 13:36:16 GMT

Sinan, i know FOR ALL ENTRIES statement.

I just want to be certain if the subquery thing can be done or not, and if yes, how.

Thanks

EDIT: Solved myself: It CANNOT be done. Reading again Subquery help came across this text which i may have skipped previously:

<b>Note

If you use a subquery with a relational operator instead of EXISTS, you may only specify one column in the SELECT clause. This can be either a field from the database table or an aggregate expression. Subqueries of this kind are referred to as scalar subqueries. </b>

Thanks to all.