topic Re: SELECT QUESTION. in Application Development and Automation Discussions

SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:10:57 GMT

HI!

OK, I need help here. In the specification I need to take out data for contracts and sales orders from the table VBAK but for contracts I need to use the VALID FROM and VALID TO parameters but for the sales orders I need for the date only the field of the document date to make the search. I don't want to use two SELECTS for the same table. Exist a way to make this with one SELECT only?

Thanks!

The validation that I need to use for the search are:

AUART - Document Type

GUEBG - Valid From

GUEEN - Valid To

AUDAT - Document Date

For contracts:

Document Type: 'ZCBH and ZCBTR'

The Valid From : = 11/30/2006

The Valid To: >= 11/30/2006

For sales orders:

Document Type: 'ZHCD and ZBRC'.

Document Date: only the month of december.

For the moment I have this: but I want one select not two.


*FOR CONTRACT
SELECT VBLEN KUNNR 
  FROM VBAK INTO TABLE T_VBAK1
  WHERE AUART = ZCBH
    AND AUART = ZCBR
    AND GUEBG = 20061130
    OR  GUEEN > 20061130.

*FOR SALES ORDERS.    
SELECT VBLEN KUNNR
  FROM VBAK INTO TABLE T_VBAK2
  WHERE AUART = ZHCS
    AND AUART = ZBRC
    AND AUDAT+4(2) = '12'.

Re: SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:14:51 GMT

Do you need them into two separate internal tables?

Rob

Re: SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:19:37 GMT

You cannot use AND for multiple AUART fields as it will fail. You must OR for that.

Besides you can try this

SELECT VBLEN KUNNR

FROM VBAK INTO TABLE T_VBAK1

WHERE ( AUART = ZCBH

OR AUART = ZCBR

OR AUART = ZHCS

OR AUART = ZBRC )

AND GUEBG = 20061130

AND GUEEN > 20061130.

AND AUDAT+4(2) = '12'.

Regards

Kathirvel

Re: SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:20:11 GMT

Hi Carlos,

Try with this code.

You need to mix both WHERE conditions as a single condition under one SELECT statement.

SELECT VBLEN KUNNR

FROM VBAK INTO TABLE T_VBAK1

WHERE ( AUART = ZCBH

OR AUART = ZCBR )

AND ( GUEBG = '20061130'

OR GUEEN > '20061130' )

AND ( AUDAT >= '20061201'

OR AUDAT <= '20061231' ).

<b>Note: Plz reward all helpful answers and close the thread if the problem is solved.</b>

Thanks,

Vinay

Re: SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:28:02 GMT

Hi Rob!

I want to have both in one internal table because the user don't need to have the contract and sales order separated. So, I like to create one SELECT only to do that because after this select I need to use other selects for other tables and if I separate this select I need to duplicate the other selects, one for the contract and other for the sales order.

Re: SELECT QUESTION.

Former Member — Wed, 13 Dec 2006 20:31:34 GMT

Try:


SELECT vbeln kunnr auart
  FROM vbak INTO TABLE itab
  WHERE ( (   auart = 'ZCBH'                 "Contracts
       OR     auart = 'ZCBR' )
      AND     guebg >= '20061130' )
       OR ( ( auart = 'ZHCS'                 "Orders
       OR     auart = 'ZBRC' )
       OR     audat LIKE '____12%' ).

Changed the expression in LIKE.

Rob

Message was edited by:

Rob Burbank