STAR JOIN

STAR JOIN performs the equivalent of an SQL left outer join. The FROM subcommand specifies the case data file (fact table). The JOIN subcommands specify the table lookup files (dimension tables).

STAR JOIN
  /SELECT {* | alias0.field [AS newname], alias1.field [AS newname],...}
  /FROM {'data file' [PASSWORD='password'] | dataset | *} AS alias0
  /JOIN {'data file' [PASSWORD='password'] | dataset | *} AS alias1
    ON alias0.key1=alias1.key1 
    [AND alias0.key2=alias1.key2] [AND...]
    [IN=field]
  [/JOIN...] [/JOIN...]
 /OUTFILE FILE={'data file' | dataset | *}
  [/PASSPROTECT]
    PASSWORD={'password'|NONE**}
    [ENCRYPTEDPW={NO**|YES}]

**Default if subcommand or keyword omitted

• All subcommands except PASSPROTECT are required.
• All subcommands must be in the order indicated in the syntax chart.
• Multiple JOIN subcommands are allowed, up to a maximum of the operating system limit for file handles or until you run out of memory.
• All command specifications must be spelled out in full; three letter abbreviation is not allowed.
• The syntax for SELECT, FROM, and JOIN is based on standard SQL syntax, but this is not SQL. You cannot include any SQL not explicitly specified in the syntax chart.

This command reads the active dataset and causes execution of any pending commands. See the topic Command Order for more information.

Release History

Example

STAR JOIN
  /SELECT t1.Name1, t1.Name2, t1.Field1, t1.Field2, t1.Field3, 
   t2.Value1, t3.Value2
  /FROM '/myfiles/fact.sav' AS t1 
  /JOIN '/myfiles/field1_values.sav' AS t2 
   ON t1.Field1 = t2.Field1
  /JOIN 'myfiles/field2_values .sav' AS t3 
   ON t1.Field2 = t3.Field2
  /OUTFILE FILE='/myfiles/merged.sav'.