Subscribe Bookmark RSS Feed

Problem when looping through data tabel

hans

Community Trekker

Joined:

Jul 1, 2015

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";

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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]

11 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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);

);

Craige_Hales

Staff

Joined:

Mar 21, 2013

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

Craige
hans

Community Trekker

Joined:

Jul 1, 2015

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/*###*/

pmroz

Super User

Joined:

Jun 23, 2011

You're missing the first e in sql_statement.

Change this:

     show(sql_statment);

to this:

     show(sql_statement);

hans

Community Trekker

Joined:

Jul 1, 2015

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?

Craige_Hales

Staff

Joined:

Mar 21, 2013

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.

Craige
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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]

hans

Community Trekker

Joined:

Jul 1, 2015

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

hans

Community Trekker

Joined:

Jul 1, 2015

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?