I want to replace a missing value by a default value to my field.
I have trouble with it.
I tried all Webfocus documentation solutions and more.
What is wrong with this code?

SET ALL = PASS
JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO ALL TRAINING.TRAINING.PIN IN TRAINING AS JOIN1
END
DEFINE FILE EMPDATA
CODE/A10=IF (COURSECODE IS MISSING OR COURSECODE EQ '') THEN 'AAA' ELSE COURSECODE;
EXP/D12.2=IF (EXPENSES IS MISSING OR EXPENSES EQ 0) THEN 0 ELSE EXPENSES;
EXPSAL/D12.2=EXP + SALARY;
END

TABLE FILE EMPDATA
PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES AND CODE AND EXP AND EXPSAL
BY PIN
WHERE EXPENSES GT 3000
END

OUTPUT:

PIN       LASTNAME FIRSTNAME COURSECODE	EXPENSES CODE   EXP      EXPSAL
000000020 BELLA    MICHAEL   .          .        .      .        .
000000040 ADAMS    RUTH      EDP750     3,400.00 EDP750 3,400.00 65,900.00
000000050 ADDAMS   PETER     UMI720     3,300.00 UMI720 3,300.00 57,400.00
000000060 PATEL    DORINA    .          .        .      .        .
000000070 SANCHEZ  EVELYN    .          .        .      .        .
000000080 SO       PAMELA    EDP690     3,200.00 EDP690 3,200.00 46,600.00
.         SO       PAMELA    BIT420     3,350.00 BIT420 3,350.00 46,750.00

EXPECTED OUTPUT:

PIN       LASTNAME FIRSTNAME COURSECODE	EXPENSES CODE   EXP      EXPSAL
000000020 BELLA    MICHAEL   .          .        AAA    0        62,500.00
000000040 ADAMS    RUTH      EDP750     3,400.00 EDP750 3,400.00 65,900.00
000000050 ADDAMS   PETER     UMI720     3,300.00 UMI720 3,300.00 57,400.00
000000060 PATEL    DORINA    .          .        AAA    0        55,500.00
000000070 SANCHEZ  EVELYN    .          .        AAA    0        83,000.00
000000080 SO       PAMELA    EDP690     3,200.00 EDP690 3,200.00 46,600.00
.         SO       PAMELA    BIT420     3,350.00 BIT420 3,350.00 46,750.00

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Luco, and welcome to Focal Point

The problem that you have is most of your defines are occurring on your child data source (TRAINING) plus the filter that you have coded is on EXPENSES which also resides on your child data source.

You could approach it from a different angle and use MATCH or you could hold the data from your original and then issue your defines on the held data file. Either way is relevant and should give you success.

Try these code snippets -

MATCH FILE EMPDATA
  SUM LASTNAME
      FIRSTNAME
      SALARY
   BY PIN
RUN
FILE TRAINING
  SUM COURSECODE
      EXPENSES
   BY PIN
AFTER MATCH HOLD AS TEMPHLD1 OLD
END

DEFINE FILE TEMPHLD1
  CODE/A10      = IF (COURSECODE IS MISSING OR COURSECODE EQ '') THEN 'AAA' ELSE COURSECODE;
  EXP/D12.2     = IF (EXPENSES IS MISSING OR EXPENSES EQ 0) THEN 0 ELSE EXPENSES;
  EXPSAL/D12.2  = EXP + SALARY;
END

TABLE FILE TEMPHLD1
PRINT LASTNAME
      FIRSTNAME
      COURSECODE
      EXPENSES
      CODE
      EXP
      EXPSAL
   BY PIN
WHERE (EXPENSES GT 3000 OR EXPENSES IS MISSING)
END

and

SET ALL = PASS
JOIN LEFT_OUTER PIN IN EMPDATA TO ALL PIN IN TRAINING AS JOIN1

TABLE FILE EMPDATA
PRINT LASTNAME
      FIRSTNAME
      SALARY
      COURSECODE
      EXPENSES
   BY PIN
ON TABLE HOLD AS TEMPHLD0
END

DEFINE FILE TEMPHLD0
  CODE/A10      = IF (COURSECODE IS MISSING OR COURSECODE EQ '') THEN 'AAA' ELSE COURSECODE;
  EXP/D12.2     = IF (EXPENSES IS MISSING OR EXPENSES EQ 0) THEN 0 ELSE EXPENSES;
  EXPSAL/D12.2  = EXP + SALARY;
END

TABLE FILE TEMPHLD0
PRINT LASTNAME
      FIRSTNAME
      COURSECODE
      EXPENSES
      CODE
      EXP
      EXPSAL
   BY PIN
WHERE (EXPENSES GT 3000 OR EXPENSES IS MISSING)
END

Good luck

T

... or you could just move your defines into the table request as computes -

SET ALL = PASS
JOIN LEFT_OUTER PIN IN EMPDATA TO ALL PIN IN TRAINING AS JOIN1

TABLE FILE EMPDATA
PRINT LASTNAME
      FIRSTNAME
      COURSECODE
      EXPENSES
      COMPUTE CODE/A10      = IF (COURSECODE IS MISSING OR COURSECODE EQ '') THEN 'AAA' ELSE COURSECODE;
      COMPUTE EXP/D12.2     = IF (EXPENSES IS MISSING OR EXPENSES EQ 0) THEN 0 ELSE EXPENSES;
      COMPUTE EXPSAL/D12.2  = EXP + SALARY;
   BY PIN
WHERE (EXPENSES GT 3000 OR EXPENSES IS MISSING)
END

Three solutions for the price of one

T

As is the same price, I used the second solution...
And it works!

Thanks Tony.