50159_en-US

liliconcom

Joining Tables Inside the Query Function

Hi,

I am fairly knew to JSL scripting, although I have some custom SQL queries I wrote in query builder. I am trying to put these queries into JSL script without having to rewrite them using the JOIN function  

 

Is it possible for me to join the tables in the Query() function?

 

For example   dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

                       dr = Open( "$RANDOM_DATA/Small Class.jmp" );

 

Below is what I'm trying/ guessing the code should look like

RESULT = QUERY (dt, dr, "SELECT * 

                  FROM 'Big Class'  BC

                  JOIN 'Small Class' SC on SC.name = BC.name;");

 

The only examples I found were of the Query function being used with 1 table.

 

Thanks,

 

David

 

Re: Joining Tables Inside the Query Function

Thank you!

Re: Joining Tables Inside the Query FunctionThank you!Re: Joining Tables Inside the Query Function

And as with the other Table Generation platforms in JMP, If you interactively run the interactive platform, and then look into the "Source" entry in the Table Panel of the new data table, you will see the JSL script that it takes to generate the data table.  Here is the script from the Source entry, for the Join example that @cwillden provided

New SQL Query(
 Connection( "JMP:RESTRICT=Y;UUID=4E866C55-4F0A-45D4-EBE9AE92DC4D8FB7" ),
 JMP Tables(
  ["t1" => "C:\Program Files\SAS\JMPPRO\13\Samples\Data\SATByYear.jmp",
  "t2" => "C:\Program Files\SAS\JMPPRO\13\Samples\Data\US Demographics.jmp"]
 ),
 QueryName( "SQLQuery1" ),
 CustomSQL(
  "SELECT t1.State, t1.\!"SAT Math\!", t2.\!"College Degrees\!",
            t2.\!"Eighth Grade Math\!"
       FROM t1
       LEFT OUTER JOIN t2
           ON t1.State = t2.State
       WHERE t1.'SAT Math' > 550;
      "
 )
) << Run

Re: Joining Tables Inside the Query Function

The Query() function in the Scripting index actually has a nice example of what you want to:

// Using aliases, performing a join
dtSAT = Open(
 "$SAMPLE_DATA/SATByYear.jmp",
 Invisible
);
dtUS = Open(
 "$SAMPLE_DATA/US Demographics.jmp",
 Invisible
);
Query(
 Table( dtSAT, "t1" ),
 Table( dtUS, "t2" ), 

 "\[SELECT t1.State, t1."SAT Math", t2."College Degrees",
            t2."Eighth Grade Math"
       FROM t1
       LEFT OUTER JOIN t2
           ON t1.State = t2.State
       WHERE t1.'SAT Math' > 550;
      ]\"
);