Converting DDL MasterFile table to SQL Server table, How do I write in missing data?

Question

I am working on converting DDL master files to SQL Server tables, and every one of them have MISSING=ON in the file, which I am interpreting as "if missing then make aware" sort of thing. How do I convert that to an SQL table that I am creating when it is only present in certain rows? An example is below:

FILENAME=EX_AGENCY_ACTIVE, SUFFIX=SQLORA  , $
  SEGMENT=EX_AGENCY_ACTIVE, SEGTYPE=S0, $
    FIELDNAME=AGENCY_ACRONYM, ALIAS=AGENCY_ACRONYM, USAGE=A12, ACTUAL=A12,
      **MISSING=ON**, $
    FIELDNAME=AGENCY_ACTIVE_DATE, ALIAS=AGENCY_ACTIVE_DATE, USAGE=A40, ACTUAL=A40,
      **MISSING=ON**, $
    FIELDNAME=AGENCY_ACTIVE_FLAG, ALIAS=AGENCY_ACTIVE_FLAG, USAGE=A1, ACTUAL=A1, $
    FIELDNAME=AGENCY_CODE, ALIAS=AGENCY_CODE, USAGE=A24, ACTUAL=A24,
      **MISSING=ON**, $
    FIELDNAME=AGENCY_EFFECTIVE_DATE, ALIAS=AGENCY_EFFECTIVE_DATE, USAGE=YYMD, ACTUAL=DATE,
     **MISSING=ON**, $
    FIELDNAME=AGENCY_NAME, ALIAS=AGENCY_NAME, USAGE=A40, ACTUAL=A40,
      **MISSING=ON**, $
    FIELDNAME=AGENCY_WK, ALIAS=AGENCY_WK, USAGE=D20.6, ACTUAL=D8, $

Currently I am thinking just adding in a NOT NULL to all columns, but I expect a lot of these tables will have missing data from not everything being "required". Would I do a circumstance like this?

SET Missing = CASE 
                  WHEN FieldName, Usage, Actual IS NULL 
                      THEN 0 
                      ELSE 1 
              END

Answer 1

Here is what would best fit your situation.

The MISSING=ON directive in the DDL master file is indeed indicating the the field can be missing or null.

in SQL Server you can achieve this by setting a column to allow null values. Here is the sample create table.

CREATE TABLE EX_AGENCY_ACTIVE (
    AGENCY_ACRONYM varchar(12) NULL,
    AGENCY_ACTIVE_DATE varchar(40) NULL,
    AGENCY_ACTIVE_FLAG char(1) NOT NULL,
    AGENCY_CODE varchar(24) NULL,
    AGENCY_EFFECTIVE_DATE date NULL,
    AGENCY_NAME varchar(40) NULL,
    AGENCY_WK decimal(20, 6) NULL
);

by setting the column to NULL, you are allowing the column to accept null values, which is equivalent to the MISSING=ON directive.

In your case the proposed solution using CASE expression to set the missing column is not necessary in this case.