topic Re: select statement problem in Application Development and Automation Discussions

select statement problem

Former Member — Mon, 06 Mar 2006 08:26:40 GMT

Hi experts,

In this following code why iam not able to get the data?

P_KDGRP & P_KUNNR are parameters and P_KDGRP is mandatory.


    SELECT VBAK~VBELN VBAK~KUNNR VBAK~AUDAT VBAK~VKBUR VBAK~AUART
           VBKD~KDGRP
    FROM VBAK INNER JOIN VBKD
    ON VBAK~VBELN = VBKD~VBELN
    INTO CORRESPONDING FIELDS OF TABLE T_HDR
    WHERE  KDGRP EQ P_KDGRP AND
            KUNNR EQ P_KUNNR .

thanks

kaki

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:28:15 GMT

Since P_KDGRP & P_KUNNR are parameters and if the values are not specified. Then the result would be empty.

Modify your query as:

SELECT VBAK~VBELN VBAK~KUNNR VBAK~AUDAT VBAK~VKBUR VBAK~AUART
           VBKD~KDGRP
    FROM VBAK INNER JOIN VBKD
    ON VBAK~VBELN = VBKD~VBELN
    INTO CORRESPONDING FIELDS OF TABLE T_HDR
    WHERE  KDGRP EQ P_KDGRP.
IF p_kunnr IS NOT INITIAL.
DELETE FROM TABLE T_HDR WHERE KUNNR NE P_KUNNR .
ENDIF.

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:28:55 GMT

Hi kaki,

1. thats bcos

P_KUNNR must be blank .

regards,

amit m.

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:29:34 GMT

hi Kaki,

if P_KUNNR is empty .. there would be no output..

try this..

data : cond type string value 'KDGRP EQ P_KDGRP '.
  if p_kunnr is not initial.
     concatenate 'AND KUNNR EQ P_KUNNR ' cond into cond.
  endif.
SELECT VBAK~VBELN VBAK~KUNNR VBAK~AUDAT VBAK~VKBUR VBAK~AUART
           VBKD~KDGRP
    FROM VBAK INNER JOIN VBKD
    ON VBAK~VBELN = VBKD~VBELN
    INTO CORRESPONDING FIELDS OF TABLE T_HDR
    WHERE (cond)  .

regards

satesh

Message was edited by: Satesh R

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:31:30 GMT

Hi again,

1. U have two options.

a) either make that parameter compulsory.

b) define the paramter as SELECT-OPTION

using the manner as mentioned below

and use IN in the sql.

<b>This will give the look same as parameter

without ranges, etc.

AND THERE WILL BE NO PROBLEM IF

BLANK IS THERE IN THE FIELD.

</b>

REPORT abc.

TABLES : vbak.

SELECT-OPTIONS p_kunnr FOR vbak-kunnr <b>no intervals NO-EXTENSION.</b>

regards,

amit m.

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:31:30 GMT

Hi Kaki

The parameter P_KUNNR might be empty.

thanks

priya

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:32:26 GMT

Hi Kaki,

do this check..

if not P_KUNNR is inital.

    SELECT 
         VBAK~VBELN 
         VBAK~KUNNR 
         VBAK~AUDAT 
         VBAK~VKBUR
         VBAK~AUART  
          VBKD~KDGRP 
   FROM VBAK INNER JOIN VBKD 
   ON VBAK~VBELN = VBKD~VBELN 
   INTO CORRESPONDING FIELDS OF TABLE T_HDR  
  WHERE  KDGRP EQ P_KDGRP AND  
          KUNNR EQ P_KUNNR .

endif.

Regards

vijay

Re: select statement problem

hymavathi_oruganti — Mon, 06 Mar 2006 08:35:09 GMT

MAKE P_KUNNR AS SELECT-OPTION OR OBLIGATORY

P_KUNNR MIGHT BE BLANK

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:35:34 GMT

Hi kaki

Since you are using Parameters both the fields should satisfy the condition. If one condition is not satisfied it will get the data.

You can use SELECT-OPTIONS with NO INTERVALS then

SELECT VBAK_{VBELN VBAK}KUNNR VBAK_{AUDAT VBAK}VKBUR VBAK~AUART

VBKD~KDGRP

FROM VBAK INNER JOIN VBKD

ON VBAK_{VBELN = VBKD}VBELN

INTO CORRESPONDING FIELDS OF TABLE T_HDR

WHERE KDGRP in S_KDGRP AND

KUNNR in S_KUNNR .

regards

kishore

Re: select statement problem

Former Member — Mon, 06 Mar 2006 08:46:05 GMT

Hi,

This is because P_KUNNR is not mandatory and if you dont specify, it wnt give any result.

The solution could be one of the following.

1) Make P_KUNNR mandatory.

2) Use select-option for KUNNR instead of parameter with

no-intervals.

3) If you want to use parameter, then fill a range

table for KUNNR and then use IN operator.

Hope this helps..

Regards,

Shashank