nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Problem when looping through data tabel

hans

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.

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

Accepted answers

pmroz

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]

All comments

pmroz

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

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

hans

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

You're missing the first e in sql_statement.

Change this:

show(sql_statment);

to this:

show(sql_statement);

hans

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?

LogShow.jsl

LogExecute.jsl

pmroz

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.