280578_en-US

liliconcom

Select Where clause referencing column from existing table instead of set

Hello,

In the example below, I want NewTable2 to include all rows from DatabaseTbl that match Column X in Table1. Any suggestions are welcome.

 

Thanks!

dt1 = Open Database(
            "DSN=...",
            "SELECT * FROM ....",
            "New Table 1"
);

dt2 = Open Database(
            "DSN=..",
             "SELECT * FROM DatabaseTbl WHERE DatabaseTbl.X in **ColumnX from table "New Table 1"**",
"New Table 2"
);

 

 

Data TableRe: Select Where clause referencing column from existing table instead of set

Hello,

I've upgraded to JMP 13 and I do see the query builder. But from what I understand, I can't use it when one of the tables involved is an external table from a database. Is there a way to use a table from a database rather than one that is saved on my PC?

Re: Select Where clause referencing column from existing table instead of set

Maybe this is a solution?

If I'm not able (for what reason ever) to put a data request into one query, I perform first one query, and with that result I perform the second one.

Please see below a script snippet, where the content of a first table column is taken into the sql query string for the second query.

 

 

dt_First = New SQL Query( connection( First_cnx_str ), queryname( "dt_First" ), customSQL( First_sql ) ) << Run Foreground();
// save file to current directory
Wait( 1 );
dt_First_name = dt_First << get name();
dt_First << save( curr_path || dt_First_name );

// get values as condition for Second query
sample_lst = dt_First:Sample_NO << get values( );

// Prepare condition string
cond_string = "and t1.sample_no in ( " || Char( sample_lst[1] );
For( i = 2, i <= N Items( sample_lst ), i++,
 cond_string = cond_string || ", " || Char( sample_lst[i] )
);
cond_string = cond_string || " ) ";

// Place condition string into pg_sql
Substitute Into( Second_sql, "__COND_STRING__", cond_string );

dt_Second = New SQL Query( connection( Second_cnx_str ), queryname( "dt_Second" ), customSQL( Second_sql ) ) << Run Foreground();

Re: Select Where clause referencing column from existing table instead of set

Query Builder was introduced in a version after JMP 10.

 

However, depending on your license you may have access to the most recent release. 

 

Send me a Private Message with details of your company and location and I'll be happy to assist further.

Re: Select Where clause referencing column from existing table instead of set

Thanks Jeff!

I'm using JMP 10 and I don't see a Query Builder option. Is that something that is only available in the later versions or am I missing something?

Re: Select Where clause referencing column from existing table instead of set

This is pretty easily done using the Query Builder using the Filter Type -> Match Column Values.

 

 

You'll find instructions in: Import Matching Data from an Existing Data Table.