topic making select statement according to 2 fields in Application Development and Automation Discussions

making select statement according to 2 fields

former_member745554 — Mon, 10 May 2021 13:25:24 GMT

Hi
In a table tab_1 there are fields werks , date , fklmg , kzwi1 , udf1 , udf2

and udf1 , udf2 are extra field that contain data and sometime the field udf1 could be empty but udf2 is not .

I need to build a report from table tab_1 based on the udf1 (as primary key) where udf1 is not initial , but if udf1 is initial get the value from udf2 .

table like
date werks fklmg kzwi1 udf1 udf2
date1 werks 70 105 pr1
date2 werks 65 206 pr6
date3 werks 66 103 pr3

date4 werks 35 623 pr2

report :
udf2 total of kzwi1

Re: making select statement according to 2 fields

Sandra_Rossi — Mon, 10 May 2021 13:50:19 GMT

Please try to format your question like that:

date  werks fklmg kzwi1 udf1 udf2
----- ----- ----- ----- ---- ----
date1 werks 70    105   pr1
date2 werks 65    206   pr6
date3 werks 66    103   pr3
date4 werks 35    623   pr2

Sorry I don't get what is your expected result.

Re: making select statement according to 2 fields

matt — Mon, 10 May 2021 14:19:39 GMT

SELECT werks , date , fklmg , kzwi1 , udf1
  INTO TABLE @my_table
  FROM tab_1
  WHERE udf1 NE @space

  UNION

SELECT werks , date , fklmg , kzwi1 , udf2 AS udf1
  INTO TABLE @my_table
  FROM tab_1
  WHERE udf1 EQ @space
    AND udf2 NE @space.

or (old syntax)

SELECT werks date fklmg kzwi1 udf1
  INTO TABLE my_table
  FROM tab_1
  WHERE udf1 NE space.

SELECT werks date fklmg kzwi1 udf2 AS udf1
  APPENDING TABLE my_table
  FROM tab_1
  WHERE udf1 EQ space
    AND udf2 NE space.

Re: making select statement according to 2 fields

Patrick_vN — Mon, 10 May 2021 14:51:25 GMT

Depending on the version of your NW stack, you could try the code below:

SELECT werks, 
       date, 
       fklmg, 
       kzwi1, 
       COALESCE( udf1, udf2 ) AS udf
  INTO TABLE @my_table
  FROM tab_1.