Select Query using AND operation

[Former Member]

Hello,

I am developing RFC which will retrieve some records from SAP HR module table.

Input will be one structure (INPUT_TABLE) containing 4 fields. User may enter values for any fields, and my requirement is to perform a AND operation on those entered fields only. Currently i am writing query as follows :

-

---

SELECT DISTINCT a_{pernr a}VORNA a_{NACHN a}cname a~gbdat FROM

PA0002 as a INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE

WHERE a_{pernr = INPUT_TABLE-pernr AND a}VNAMC = INPUT_TABLE-VORNA AND a_{NCHMC = INPUT_TABLE-NACHN AND a}gbdat = INPUT_TABLE-GBDAT .

-

---

If only 2 values are entered by user out of 4 , it will perform AND operation by taking other values as blank or 000000. I want to skip these AND operation on fields which are not entered by user.

Please help for writing query.

Thanks in advance,

Prashant

[Former Member]

Use RANGES in your select statement.

RANGES: r_test for sy-datum,

r_test2 for sy-datum.

clear r_test.

if not datum is initial.

r_test = 'IEQ.

r_test-low = datum.

append r_test.

endif.

clear r_test2.

if not addat is initial.

r_test2 = 'IEQ.

r_test2-low = addat.

append r_test2.

endif.

SELECT * FROM testdb where datum in r_test.

and addat in r_test2.

[Former Member]

Hi,

Create dynamic where condition based upon user input.

Try like this....

IF NOT INPUT_TABLE-pernr IS INITIAL.

CLEAR : lv_pernr_condition.

CONCATENATE 'PERNR' ' = ' '''' INPUT_TABLE-pernr '''' INTO

lv_pernr_condition.

ENDIF.

IF NOT INPUT_TABLE-vnamc IS INITIAL.

CLEAR : lv_vnamc_condition.

CONCATENATE 'VNAMC' ' = ' '''' INPUT_TABLE-vnamc '''' INTO

lv_vnamc_condition.

ENDIF.

IF NOT INPUT_TABLE-vorna IS INITIAL.

CLEAR : lv_vorna_condition.

CONCATENATE 'VORNA' ' = ' '''' INPUT_TABLE-vorna '''' INTO

lv_vorna_condition.

ENDIF.

IF NOT INPUT_TABLE-nchmc IS INITIAL.

CLEAR : lv_nchmc_condition.

CONCATENATE 'NCHMC' ' = ' '''' INPUT_TABLE-nchmc '''' INTO

lv_nchmc_condition.

ENDIF.

IF NOT INPUT_TABLE-gbdat IS INITIAL.

CLEAR : lv_gbdat_condition.

CONCATENATE 'GBDAT' ' = ' '''' INPUT_TABLE-gbdat '''' INTO

lv_gbdat_condition.

ENDIF.

IF NOT lv_pernr_condition IS INITIAL.

CONCATENATE lv_pernr_condition lv_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

IF NOT lv_vnamc_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_vnamc_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_vnamc_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_vorna_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_vorna_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_vorna_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_nchmc_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_nchmc_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_nchmc_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_gbdat_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_gbdat_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_gbdat_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

SELECT DISTINCT pernr VORNA NACHN cname gbdat FROM

PA0002 INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE

WHERE lv_condition . " Dynamic where condition

Hope it will helps

[Former Member]

Hi Gowri Sankar Dinakaran,

Thanks for your immediate reply. I will try to implement the same using your solution and let you know if i face any difficulty.

-

---

Prashant

[Former Member]

Hi Gowri Sankar Dinakaran,

You have given exact solution for my problem. thanks for the same.

But I got one more error in select query which is as follows.

Incorrect expression "lv_condition" in logical condition.

Thanks,

Prashant

[Former Member]

Solved error.

Used following query.

SELECT DISTINCT pernr VORNA NACHN cname gbdat FROM PA0002 INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE

WHERE (lv_condition) . " Dynamic where condition

(PS : - Points are awarded)

Thanks.

Edited by: Prashant Jagdale (Genius) on Sep 16, 2008 11:30 AM

[Former Member]

Hi,

You have entered select query like this right...

SELECT DISTINCT pernr VORNA NACHN cname gbdat FROM

PA0002 INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE

WHERE (lv_condition) .

other wise can you paste your code here

[Former Member]

This message was moderated.

[Former Member]

Got one more issue. I need to add one more field named 'telnr' in the comparison. So INPUT_TABLE contain TELNR field also.

but this field is from table PA0006.

Do you have any help on queries containing join???

-

---

Prashant

Edited by: Prashant Jagdale (Genius) on Sep 16, 2008 12:01 PM

[Former Member]

Hi,

Try like this....

IF NOT INPUT_TABLE-pernr IS INITIAL.

CLEAR : lv_pernr_condition.

CONCATENATE 'a~PERNR' ' = ' '''' INPUT_TABLE-pernr '''' INTO

lv_pernr_condition.

ENDIF.

IF NOT INPUT_TABLE-vnamc IS INITIAL.

CLEAR : lv_vnamc_condition.

CONCATENATE 'a~VNAMC' ' = ' '''' INPUT_TABLE-vnamc '''' INTO

lv_vnamc_condition.

ENDIF.

IF NOT INPUT_TABLE-vorna IS INITIAL.

CLEAR : lv_vorna_condition.

CONCATENATE 'a~VORNA' ' = ' '''' INPUT_TABLE-vorna '''' INTO

lv_vorna_condition.

ENDIF.

IF NOT INPUT_TABLE-nchmc IS INITIAL.

CLEAR : lv_nchmc_condition.

CONCATENATE 'a~NCHMC' ' = ' '''' INPUT_TABLE-nchmc '''' INTO

lv_nchmc_condition.

ENDIF.

IF NOT INPUT_TABLE-gbdat IS INITIAL.

CLEAR : lv_gbdat_condition.

CONCATENATE 'a~GBDAT' ' = ' '''' INPUT_TABLE-gbdat '''' INTO

lv_gbdat_condition.

ENDIF.

IF NOT INPUT_TABLE-telnr IS INITIAL.

CLEAR : lv_telnr_condition.

CONCATENATE 'b~telnr' ' = ' '''' INPUT_TABLE-telnr '''' INTO

lv_telnr_condition.

ENDIF.

IF NOT lv_pernr_condition IS INITIAL.

CONCATENATE lv_pernr_condition lv_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

IF NOT lv_vnamc_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_vnamc_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_vnamc_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_vorna_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_vorna_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_vorna_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_nchmc_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_nchmc_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_nchmc_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_gbdat_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_gbdat_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_gbdat_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

IF NOT lv_telnr_condition IS INITIAL.

IF lv_condition IS INITIAL.

CONCATENATE lv_telnr_condition lv_condition

INTO lv_condition SEPARATED BY space.

ELSE.

CONCATENATE lv_condition 'AND' lv_telnr_condition

INTO lv_condition SEPARATED BY space.

ENDIF.

ENDIF.

SELECT DISTINCT a_{pernr a}VORNA a_{NACHN a}cname a_{gbdat b}telnr

INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE from pa002 as a

join pa006 as b on a_{pernr = b}pernr

WHERE lv_condition . " Dynamic where condition

hope it is helps

[Former Member]

Again i have to add one more field "DATJO"from another table "PA0502". So i have build the dynamic condition string for the same and written query as follows.

SELECT DISTINCT a_{pernr a}VORNA a_{NACHN a}cname a_{gbdat b}telnr c~DatJo

INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE from pa0002 as a

JOIN pa0006 as b on a_{pernr = b}pernr JOIN PA0502 as c on b_{pernr = c}pernr

WHERE (lv_condition).

But i m geeting zero records.

Is there any wrong in query??

Thanks,

Prashant

[Former Member]

Hi,

Try this code.....

SELECT DISTINCT a~pernr a~VORNA a~NACHN a~cname a~gbdat b~telnr c~DatJo
INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE from pa0002 as a
JOIN pa0006 as b on a~pernr = b~pernr JOIN PA0502 as c on a~pernr = c~pernr
WHERE (lv_condition). 

*OR*

SELECT DISTINCT a~pernr a~VORNA a~NACHN a~cname a~gbdat b~telnr c~DatJo
INTO CORRESPONDING FIELDS OF TABLE Z_PD_TABLE from ( pa0002 as a
INNER JOIN pa0006 as b on a~pernr = b~pernr 
INNER JOIN PA0502 as c on a~pernr = c~pernr )
WHERE (lv_condition). 

Hope it will helps

[Former Member]

Thanks for your reply.

As per your solution my query is correct.

But just now i have checked the values of the table PA0502. It contains zero records.

So join of this table with any tables is zero records. Right??

Correct me if i am wrong??

........

Prashant