13070_en-US

liliconcom

Problem when looping through data tabel<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I have a data table based on a sql query. I want to loop through the data table and create and execute new sql statements based on the values in the original data table.
For some reason it seems that the sql statement is created twice first with variables names and afterwards with the actual variabel values. Below I have the code and the result from the log when the code is executed. Any tips/explanations regarding the problem is highly appreciated.

br

Hans

CODE from here:

dtMonitor = DatabaseOpslag("SELECT distinct trial_no, trial_unit_reference FROM table");

  For(i=1,i<=nrows(dtMonitor),i++,

  show(:trial_no);

  show(:trial_unit_reference);

  Show("SELECT Statement where IMPACT_ALL_ISSUES.ISSUE_CATEGORY_CODE like 'PD%' and IMPACT_ALL_ISSUES.trial_no = "||Char(EVAL(:trial_no))||" and IMPACT_ALL_ISSUES.trial_unit_reference like "||Char(EVAL(:trial_unit_reference))||"");

  );

/*:

LOG from here:

:TRIAL_NO = 3748;

:TRIAL_UNIT_REFERENCE = "822";

"SELECT PRIMARY_INVESTIGATOR, COUNTRY_NAME, IMPACT_ALL_ISSUES.TRIAL_UNIT_REFERENCE, PATIENT_ENTRY_NO, ISSUE_CATEGORY_DESC, ISSUE_TITLE, ISSUE_DESC, ISSUE_ACTION, GCP_ISSUE, ISSUE_CREATED_BY_FORENAME, ISSUE_CREATED_BY_SURNAME, CREATED_DATE, CLOSED_DATE, ISSUE_RESOLVED FROM IMPACT_ALL_ISSUES INNER JOIN  IMPACT_UNIT_LEVEL ON  (IMPACT_ALL_ISSUES.UNIT_KEY = IMPACT_UNIT_LEVEL.UNIT_KEY) AND (IMPACT_ALL_ISSUES.TRIAL_NO = IMPACT_UNIT_LEVEL.TRIAL_NO) where IMPACT_ALL_ISSUES.ISSUE_CATEGORY_CODE like 'PD%' and IMPACT_ALL_ISSUES.trial_no = " || Char(Eval(:TRIAL_NO)) || " and IMPACT_ALL_ISSUES.trial_unit_reference like " || Char(Eval(:TRIAL_UNIT_REFERENCE)) || "" = "SELECT PRIMARY_INVESTIGATOR, COUNTRY_NAME, IMPACT_ALL_ISSUES.TRIAL_UNIT_REFERENCE, PATIENT_ENTRY_NO, ISSUE_CATEGORY_DESC, ISSUE_TITLE, ISSUE_DESC, ISSUE_ACTION, GCP_ISSUE, ISSUE_CREATED_BY_FORENAME, ISSUE_CREATED_BY_SURNAME, CREATED_DATE, CLOSED_DATE, ISSUE_RESOLVED FROM IMPACT_ALL_ISSUES INNER JOIN  IMPACT_UNIT_LEVEL ON  (IMPACT_ALL_ISSUES.UNIT_KEY = IMPACT_UNIT_LEVEL.UNIT_KEY) AND (IMPACT_ALL_ISSUES.TRIAL_NO = IMPACT_UNIT_LEVEL.TRIAL_NO) where IMPACT_ALL_ISSUES.ISSUE_CATEGORY_CODE like 'PD%' and IMPACT_ALL_ISSUES.trial_no = 3748 and IMPACT_ALL_ISSUES.trial_unit_reference like 822";

Re: Problem when looping through data tabel

JMP's current data table will change when a new table is created and when you click on a different table.  It is a good idea to use the explicit table reference rather than depending on the current data table.

Craige

Re: Problem when looping through data tabel

thanks for the effort in helping me out. useing the table as prefix made the difference.

Re: Problem when looping through data tabel

That did the trick - thank you

so just for my understanding/learning: jmp gets confused about which data table I am refering to when I try to reference the variables? but with the table as prefix that is solved?

Re: Problem when looping through data tabel

I just posted an error trapping utility that I use for Oracle ODBC SQL, to the file exchange.  Click here to see it.  It's specific to Oracle, but could be adapted for other databases.

Re: Problem when looping through data tabel

C Hales is correct.  However your table variable is dtmonitor, not dt.  You want the following in your sql statement in the loop:

     dtmonitor:trial_no[i]

and

     dtmonitor:TRIAL_UNIT_REFERENCE[i]

Re: Problem when looping through data tabel

the error messages in JMP need a little explaining the first time you see one:

Name Unresolved: trial_no in access or evaluation of 'trial_no' , :trial_no/*###*/

that's what went wrong, but where?  the next line tells you how the error will be flagged:

In the following script, error marked by /*###*/

D1 = Dialog( "prompt", Monitor = edittext( "Monitor initials" ) );

... skip a bunch of script and find...

  || Char( :trial_no/*###*/ ) ||

I think the data table JMP is looking in does not have a column "trial_no".  If there is more than one data table, you can use dt:trial_no to be explicit about the table to find the variable in.

Re: Problem when looping through data tabel

silly mistake by me sorry

I have corrected the spelling and when I run the script and only diplays the sql statement it appears that the statements are generated as expected. but when i try to pass the sql statement to a function that makes the db query and put the result into a table the function only executes once and then throws an error. I have made a log where I only displays the sql statement (logshow.jsl) and one where I try to execute (logexecute.jsl).

I am new to jmp script so I am not sure what to do?

Re: Problem when looping through data tabel

You're missing the first e in sql_statement.

Change this:

     show(sql_statment);

to this:

     show(sql_statement);

Re: Problem when looping through data tabel

Thanks for looking into this but I still cannot make the sql statement that I attempt see code from log below:

CODE form here:

dtMonitor = DabaseOpslag("SELECT distinct trial_no, trial_unit_reference FROM IMPACT_MONITOR_WORKLOAD where username like '"||Monitor||"' and site_closed_date is null");

For(i=1,i<=nrows(dtMonitor),i++,

sql_statement = "SELECT PRIMARY_INVESTIGATOR, COUNTRY_NAME, IMPACT_ALL_ISSUES.TRIAL_UNIT_REFERENCE, PATIENT_ENTRY_NO, ISSUE_CATEGORY_DESC, ISSUE_TITLE, ISSUE_DESC, ISSUE_ACTION, GCP_ISSUE, ISSUE_CREATED_BY_FORENAME, ISSUE_CREATED_BY_SURNAME, CREATED_DATE, CLOSED_DATE, ISSUE_RESOLVED FROM IMPACT_ALL_ISSUES INNER JOIN IMPACT_UNIT_LEVEL ON (IMPACT_ALL_ISSUES.UNIT_KEY = IMPACT_UNIT_LEVEL.UNIT_KEY) AND (IMPACT_ALL_ISSUES.TRIAL_NO = IMPACT_UNIT_LEVEL.TRIAL_NO) where IMPACT_ALL_ISSUES.ISSUE_CATEGORY_CODE like 'PD%' and IMPACT_ALL_ISSUES.trial_no = "||Char(:trial_no[i])||" and IMPACT_ALL_ISSUES.trial_unit_reference like "||Char(:trial_unit_reference[i])||"";

Show(sql_statment);

);

 

/*:

LOG response:

Name Unresolved: sql_statment in access or evaluation of 'sql_statment' , sql_statment/*###*/

Re: Problem when looping through data tabel

Yes, show() works best with simple variable names.  Complicated expressions produce confusing answers.

Re: Problem when looping through data tabel

You can remove the EVALs.  Set the SQL statement equal to a variable and then show the variable:

For(i=1,i<=nrows(dtMonitor),i++,

  show(:trial_no[i]);

  show(:trial_unit_reference[i]);

     sql_statement =

"SELECT Statement

  where IMPACT_ALL_ISSUES.ISSUE_CATEGORY_CODE like 'PD%'

    and IMPACT_ALL_ISSUES.trial_no = " || Char(:trial_no[i]) || "

    and IMPACT_ALL_ISSUES.trial_unit_reference like " || Char(:trial_unit_reference[i]);

     show(sql_statement);

);