making select statement according to 2 fields

[former_member745554]

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

[Sandra_Rossi]

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.

[matt]

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.

[Patrick_vN]

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.