package net.sf.log4jdbc;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class SqlUtil {
/**
* @param sql the sql containing parameters to replace in '?'
* @param parameterStr is a string with all the parameters for the sql separated with ','
*/
public static void replaceParameter(String sql, String parameterStr) {
String[] parameters = parameterStr.split(",");
Pattern p = Pattern.compile("= \\?");
Matcher m = p.matcher(sql);
String actual = null;
int index = 0;
int start = 0;
StringBuffer sb = new StringBuffer();
while (m.find()) {
//System.out.println(sql.substring(m.start(),m.end()));
sb.append(sql.substring(start, m.start() - 1));
sb.append(" = ");
sb.append("'");
sb.append(parameters[index]);
sb.append("'");
sb.append("\n");
index++;
start = m.end();
//actual = sql.substring(0, m.end()- 1) + parameters[index++] + "\n";
}
sb.append(sql.substring(start));
//System.out.println(index);
System.out.println(sb.toString());
}
public static void main(String[] args) {
String sql = "SELECT T0.c0, T0.c1, T0.c2, T0.c3, T0.c4, T0.c5, T0.c6, T0.c7, T0.c8, T0.c9, T0.c10, T0.c11, T0.c12, T0.c13, T0.c14, T0.c15, T0.c16, T0.c17, T0.c18, T0.c19, T0.c20, T0.c21, T0.c22, T0.c23, T0.c24, T0.c25, T0.c26, T0.c27, T0.c28, T0.c29, T0.c30, T0.c31, T0.c32, T0.c33, T0.c34, T0.c35, T0.c36, T0.c37, T0.c38, T0.c39, T0.c40, T0.c41, T0.c42, T0.c43, T0.c44, T0.c45, T0.c46, T0.c47, T0.c48, T0.c49, T0.c50, T0.c51, T0.c52, T0.c53, T0.c54, T0.c55, T0.c56, T0.c57, T0.c58, T0.c59, T0.c60, T0.c61, T0.c62, T0.c63, T0.c64, T0.c65, T0.c66, T0.c67, T0.c68 FROM (SELECT T1.PATIENT_RECORD_ID c0, T2.EVENT_ID c1, TO_CHAR(T2.EVENT_DATE_TIME, 'MM/DD/YYYY') c2, REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') c3, CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '430') THEN 'Hemorrhage' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '431') THEN 'Hemorrhage' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '432.9') THEN 'Hemorrhage' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.0') THEN 'TIA' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.1') THEN 'TIA' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.2') THEN 'TIA' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.3') THEN 'TIA' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.8') THEN 'TIA' ELSE CASE WHEN (REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') = '435.9') THEN 'TIA' ELSE 'Ischemic' END END END END END END END END END c4, T3.c4 c5, TO_CHAR(T4.DATE_OF_BIRTH, 'YYYY-MM-DD') c6, T4.GENDER c7, T4.USER_LEVEL c8, T6.STR c9, T7.RESPONSE c10, T8.RESPONSE c11, T9.RESPONSE c12, T10.RESPONSE c13, T11.RESPONSE c14, T12.RESPONSE c15, T14.c3 c16, T16.c3 c17, T17.RESPONSE c18, T18.c3 c19, TO_CHAR(T19.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c20, TO_CHAR(T20.DETAIL_DATE, 'MM/DD/YYYY') c21, TO_CHAR(T21.DETAIL_DATE, 'MM/DD/YYYY') c22, TO_CHAR(T22.DETAIL_DATE, 'MM/DD/YYYY') c23, TO_CHAR(T23.DETAIL_DATE, 'MM/DD/YYYY') c24, TO_CHAR(T24.DETAIL_DATE, 'MM/DD/YYYY') c25, TO_CHAR(T25.DETAIL_DATE, 'MM/DD/YYYY') c26, TO_CHAR(T26.DETAIL_DATE, 'MM/DD/YYYY') c27, TO_CHAR(T27.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c28, TO_CHAR(T28.DETAIL_DATE, 'MM/DD/YYYY') c29, TO_CHAR(T29.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c30, TO_CHAR(T30.DETAIL_DATE, 'MM/DD/YYYY') c31, T31.RESPONSE c32, T32.RESPONSE c33, T33.RESPONSE c34, T34.RESPONSE c35, T35.RESPONSE c36, T36.RESPONSE c37, T38.c3 c38, T39.RESPONSE c39, T41.c3 c40, T42.RESPONSE c41, T43.RESPONSE c42, T44.RESPONSE c43, T45.RESPONSE c44, T46.RESPONSE c45, T47.RESPONSE c46, T48.RESPONSE c47, T49.RESPONSE c48, T50.RESPONSE c49, T51.RESPONSE c50, T52.RESPONSE c51, T53.RESPONSE c52, T55.c3 c53, T56.DETAIL_NUMBER c54, T57.DETAIL_NUMBER c55, T58.DETAIL_NUMBER c56, T59.DETAIL_NUMBER c57, TO_CHAR(T60.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c58, TO_CHAR(T61.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c59, TO_CHAR(T62.DETAIL_DATE, 'YYYY-MM-DD HH24:MI:SS') c60, T63.RESPONSE c61, T64.c3 c62, T65.RESPONSE c63, T66.RESPONSE c64, T67.RESPONSE c65, T68.RESPONSE c66, T69.RESPONSE c67, T70.RESPONSE c68 FROM UMD_PATIENT T1 JOIN UMD_EVENT T2 ON T2.USER_ID = T1.USER_ID AND T2.PHYS_ID = T1.OWNER_ID AND ('2020373703' = T2.USER_ID OR T2.PHYS_ID IN ('3856') AND '2' = '2') JOIN (SELECT S1T1.LIST_ITEM_ID c2, S1T1.FIELD_VALUE c4, S1T2.PHYS_ID c0, S1T2.LIST_NAME c1 FROM FB_LIST_ITEMS S1T1 JOIN FB_LISTS S1T2 ON S1T2.LIST_ID = S1T1.LIST_ID WHERE S1T2.PHYS_ID = ? AND S1T2.LIST_NAME = ?) T3 ON TO_CHAR(T3.c2) = T2.LOCATION JOIN EMY_USERS T4 ON T4.USER_ID = T2.USER_ID AND (T4.PHYS_ID IN ('3856') AND '2' = '2' OR T4.USER_ID = '2020373703') LEFT JOIN UMD_PATIENT_DIAGNOSIS T5 ON T5.EVENT_ID = T2.EVENT_ID AND T5.PATIENT_DIAGNOSIS_TYPE = ? AND T2.EVENT_DATE_TIME >= T5.VALID_START_DATE AND T2.EVENT_DATE_TIME < T5.VALID_END_DATE LEFT JOIN ONTOLOGY_TABLE_FLAT T6 ON T6.CODE = T5.DIAGNOSIS_ID AND T6.PATHNODE = 'RMD680440' JOIN UMD_EVENT_DETAIL T7 ON T7.EVENT_ID = T2.EVENT_ID AND T7.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T8 ON T8.EVENT_ID = T2.EVENT_ID AND T8.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T9 ON T9.EVENT_ID = T2.EVENT_ID AND T9.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T10 ON T10.EVENT_ID = T2.EVENT_ID AND T10.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T11 ON T11.EVENT_ID = T2.EVENT_ID AND T11.DETAIL_CLASS_ID = ? LEFT JOIN UMD_PATIENT_DETAIL T12 ON T12.PATIENT_ID = T1.PATIENT_RECORD_ID AND T12.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T12.VALID_START_DATE AND T2.EVENT_DATE_TIME < T12.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T13 ON T13.PATIENT_ID = T1.PATIENT_RECORD_ID AND T13.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T13.VALID_START_DATE AND T2.EVENT_DATE_TIME < T13.VALID_END_DATE LEFT JOIN (SELECT S2T1.CUI1 c0, S2T1.REL c1, S2T2.CODE c2, S2T2.STR c3 FROM MRREL S2T1 JOIN MRCONSO S2T2 ON S2T2.CUI = S2T1.CUI2 WHERE S2T1.CUI1 = ? AND S2T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T14 ON T14.c2 = TO_CHAR(T13.DETAIL_NUMBER) LEFT JOIN UMD_EVENT_DETAIL T15 ON T15.EVENT_ID = T2.EVENT_ID AND T15.DETAIL_CLASS_ID = ? LEFT JOIN (SELECT S3T1.CUI1 c0, S3T1.REL c1, S3T2.CODE c2, S3T2.STR c3 FROM MRREL S3T1 JOIN MRCONSO S3T2 ON S3T2.CUI = S3T1.CUI2 WHERE S3T1.CUI1 = ? AND S3T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T16 ON T16.c2 = T15.RESPONSE LEFT JOIN UMD_PATIENT_DETAIL T17 ON T17.PATIENT_ID = T1.PATIENT_RECORD_ID AND T17.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T17.VALID_START_DATE AND T2.EVENT_DATE_TIME < T17.VALID_END_DATE LEFT JOIN (SELECT S4T1.CUI1 c0, S4T1.REL c1, S4T2.CODE c2, S4T2.STR c3 FROM MRREL S4T1 JOIN MRCONSO S4T2 ON S4T2.CUI = S4T1.CUI2 WHERE S4T1.CUI1 = ? AND S4T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T18 ON T18.c2 = T17.RESPONSE LEFT JOIN UMD_EVENT_DETAIL T19 ON T19.EVENT_ID = T2.EVENT_ID AND T19.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T20 ON T20.EVENT_ID = T2.EVENT_ID AND T20.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T21 ON T21.EVENT_ID = T2.EVENT_ID AND T21.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T22 ON T22.EVENT_ID = T2.EVENT_ID AND T22.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T23 ON T23.EVENT_ID = T2.EVENT_ID AND T23.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T24 ON T24.EVENT_ID = T2.EVENT_ID AND T24.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T25 ON T25.EVENT_ID = T2.EVENT_ID AND T25.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T26 ON T26.EVENT_ID = T2.EVENT_ID AND T26.DETAIL_CLASS_ID = ? LEFT JOIN UMD_PATIENT_DETAIL T27 ON T27.PATIENT_ID = T1.PATIENT_RECORD_ID AND T27.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T27.VALID_START_DATE AND T2.EVENT_DATE_TIME < T27.VALID_END_DATE LEFT JOIN UMD_EVENT_DETAIL T28 ON T28.EVENT_ID = T2.EVENT_ID AND T28.DETAIL_CLASS_ID = ? LEFT JOIN UMD_PATIENT_DETAIL T29 ON T29.PATIENT_ID = T1.PATIENT_RECORD_ID AND T29.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T29.VALID_START_DATE AND T2.EVENT_DATE_TIME < T29.VALID_END_DATE LEFT JOIN UMD_EVENT_DETAIL T30 ON T30.EVENT_ID = T2.EVENT_ID AND T30.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T31 ON T31.EVENT_ID = T2.EVENT_ID AND T31.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T32 ON T32.EVENT_ID = T2.EVENT_ID AND T32.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T33 ON T33.EVENT_ID = T2.EVENT_ID AND T33.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T34 ON T34.EVENT_ID = T2.EVENT_ID AND T34.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T35 ON T35.EVENT_ID = T2.EVENT_ID AND T35.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T36 ON T36.EVENT_ID = T2.EVENT_ID AND T36.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T37 ON T37.EVENT_ID = T2.EVENT_ID AND T37.DETAIL_CLASS_ID = ? LEFT JOIN (SELECT S5T1.CUI1 c0, S5T1.REL c1, S5T2.CODE c2, S5T2.STR c3 FROM MRREL S5T1 JOIN MRCONSO S5T2 ON S5T2.CUI = S5T1.CUI2 WHERE S5T1.CUI1 = ? AND S5T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T38 ON T38.c2 = T37.RESPONSE LEFT JOIN UMD_EVENT_DETAIL T39 ON T39.EVENT_ID = T2.EVENT_ID AND T39.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T40 ON T40.EVENT_ID = T2.EVENT_ID AND T40.DETAIL_CLASS_ID = ? LEFT JOIN (SELECT S6T1.CUI1 c0, S6T1.REL c1, S6T2.CODE c2, S6T2.STR c3 FROM MRREL S6T1 JOIN MRCONSO S6T2 ON S6T2.CUI = S6T1.CUI2 WHERE S6T1.CUI1 = ? AND S6T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T41 ON T41.c2 = T40.RESPONSE LEFT JOIN UMD_PATIENT_DETAIL T42 ON T42.PATIENT_ID = T1.PATIENT_RECORD_ID AND T42.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T42.VALID_START_DATE AND T2.EVENT_DATE_TIME < T42.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T43 ON T43.PATIENT_ID = T1.PATIENT_RECORD_ID AND T43.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T43.VALID_START_DATE AND T2.EVENT_DATE_TIME < T43.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T44 ON T44.PATIENT_ID = T1.PATIENT_RECORD_ID AND T44.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T44.VALID_START_DATE AND T2.EVENT_DATE_TIME < T44.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T45 ON T45.PATIENT_ID = T1.PATIENT_RECORD_ID AND T45.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T45.VALID_START_DATE AND T2.EVENT_DATE_TIME < T45.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T46 ON T46.PATIENT_ID = T1.PATIENT_RECORD_ID AND T46.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T46.VALID_START_DATE AND T2.EVENT_DATE_TIME < T46.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T47 ON T47.PATIENT_ID = T1.PATIENT_RECORD_ID AND T47.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T47.VALID_START_DATE AND T2.EVENT_DATE_TIME < T47.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T48 ON T48.PATIENT_ID = T1.PATIENT_RECORD_ID AND T48.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T48.VALID_START_DATE AND T2.EVENT_DATE_TIME < T48.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T49 ON T49.PATIENT_ID = T1.PATIENT_RECORD_ID AND T49.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T49.VALID_START_DATE AND T2.EVENT_DATE_TIME < T49.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T50 ON T50.PATIENT_ID = T1.PATIENT_RECORD_ID AND T50.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T50.VALID_START_DATE AND T2.EVENT_DATE_TIME < T50.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T51 ON T51.PATIENT_ID = T1.PATIENT_RECORD_ID AND T51.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T51.VALID_START_DATE AND T2.EVENT_DATE_TIME < T51.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T52 ON T52.PATIENT_ID = T1.PATIENT_RECORD_ID AND T52.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T52.VALID_START_DATE AND T2.EVENT_DATE_TIME < T52.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T53 ON T53.PATIENT_ID = T1.PATIENT_RECORD_ID AND T53.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T53.VALID_START_DATE AND T2.EVENT_DATE_TIME < T53.VALID_END_DATE LEFT JOIN UMD_EVENT_DETAIL T54 ON T54.EVENT_ID = T2.EVENT_ID AND T54.DETAIL_CLASS_ID = ? LEFT JOIN (SELECT S7T1.CUI1 c0, S7T1.REL c1, S7T2.CODE c2, S7T2.STR c3 FROM MRREL S7T1 JOIN MRCONSO S7T2 ON S7T2.CUI = S7T1.CUI2 WHERE S7T1.CUI1 = ? AND S7T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T55 ON T55.c2 = T54.RESPONSE LEFT JOIN UMD_EVENT_DETAIL T56 ON T56.EVENT_ID = T2.EVENT_ID AND T56.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T57 ON T57.EVENT_ID = T2.EVENT_ID AND T57.DETAIL_CLASS_ID = ? LEFT JOIN UMD_PATIENT_DETAIL T58 ON T58.PATIENT_ID = T1.PATIENT_RECORD_ID AND T58.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T58.VALID_START_DATE AND T2.EVENT_DATE_TIME < T58.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T59 ON T59.PATIENT_ID = T1.PATIENT_RECORD_ID AND T59.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T59.VALID_START_DATE AND T2.EVENT_DATE_TIME < T59.VALID_END_DATE LEFT JOIN UMD_EVENT_DETAIL T60 ON T60.EVENT_ID = T2.EVENT_ID AND T60.DETAIL_CLASS_ID = ? LEFT JOIN UMD_EVENT_DETAIL T61 ON T61.EVENT_ID = T2.EVENT_ID AND T61.DETAIL_CLASS_ID = ? LEFT JOIN UMD_PATIENT_DETAIL T62 ON T62.PATIENT_ID = T1.PATIENT_RECORD_ID AND T62.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T62.VALID_START_DATE AND T2.EVENT_DATE_TIME < T62.VALID_END_DATE LEFT JOIN UMD_EVENT_DETAIL T63 ON T63.EVENT_ID = T2.EVENT_ID AND T63.DETAIL_CLASS_ID = ? LEFT JOIN (SELECT S8T1.CUI1 c0, S8T1.REL c1, S8T2.CODE c2, S8T2.STR c3 FROM MRREL S8T1 JOIN MRCONSO S8T2 ON S8T2.CUI = S8T1.CUI2 WHERE S8T1.CUI1 = ? AND S8T1.REL IN ('CHD', 'VAL') AND ROWNUM > 0) T64 ON T64.c2 = T63.RESPONSE LEFT JOIN UMD_PATIENT_DETAIL T65 ON T65.PATIENT_ID = T1.PATIENT_RECORD_ID AND T65.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T65.VALID_START_DATE AND T2.EVENT_DATE_TIME < T65.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T66 ON T66.PATIENT_ID = T1.PATIENT_RECORD_ID AND T66.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T66.VALID_START_DATE AND T2.EVENT_DATE_TIME < T66.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T67 ON T67.PATIENT_ID = T1.PATIENT_RECORD_ID AND T67.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T67.VALID_START_DATE AND T2.EVENT_DATE_TIME < T67.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T68 ON T68.PATIENT_ID = T1.PATIENT_RECORD_ID AND T68.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T68.VALID_START_DATE AND T2.EVENT_DATE_TIME < T68.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T69 ON T69.PATIENT_ID = T1.PATIENT_RECORD_ID AND T69.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T69.VALID_START_DATE AND T2.EVENT_DATE_TIME < T69.VALID_END_DATE LEFT JOIN UMD_PATIENT_DETAIL T70 ON T70.PATIENT_ID = T1.PATIENT_RECORD_ID AND T70.DETAIL_CLASS_ID = ? AND T2.EVENT_DATE_TIME >= T70.VALID_START_DATE AND T2.EVENT_DATE_TIME < T70.VALID_END_DATE JOIN EMY_USERS T71 ON T71.USER_ID = T1.USER_ID LEFT JOIN UMD_PATIENT T72 ON T72.USER_ID = T4.USER_ID AND T2.EVENT_DATE_TIME >= T72.VALID_START_DATE AND T2.EVENT_DATE_TIME < T72.VALID_END_DATE AND (T72.OWNER_ID IN ('3856') AND '2' = '2' OR T72.USER_ID = '2020373703') WHERE REGEXP_REPLACE(T6.STR, '^\\[(.*?)\\].*$', '\\1') IN ('430', '431', '432.9', '433.01', '433.11', '433.21', '433.31', '433.81', '433.91', '434.00', '434.01', '434.11', '434.91', '436', '435.0', '435.1', '435.2', '435.3', '435.8', '435.9') AND T7.RESPONSE = 'Complete' AND T3.c4 IS NOT NULL AND T4.USER_LEVEL = 1 AND (T1.OWNER_ID IN ('3856') AND '2' = '2' OR T1.USER_ID = 2020373703) AND (T1.LOCATION IN ('Prov. Portland M. C.','Prov. St. Vincent M. C.','Prov. Milwaukie H.','Prov. Newberg M. C.','Prov. Medford M. C.','Sky Lakes Medical Center','Silverton Hospital','Ocean Beach Hospital','KimMustBeUniqueTest','Prov. Hood River Hospital','Curry General Hospital','Prov. Willamette Falls M. C.','Good Shepherd M. C.','Prov. St. Mary''s M. C.','Prov. Seaside Hospital','Wallowa Memorial Hospital','St. Anthony Hospital','Testing for bugs','Test Hospital Location','Test hospital','Tillamook County General H.') OR T1.LOCATION IS NULL OR 2020373703 = T1.USER_ID) AND (T71.PHYS_ID IN ('3856') AND '2' = '2' OR T71.USER_ID = 2020373703) AND (T72.LOCATION IN ('Prov. Portland M. C.','Prov. St. Vincent M. C.','Prov. Milwaukie H.','Prov. Newberg M. C.','Prov. Medford M. C.','Sky Lakes Medical Center','Silverton Hospital','Ocean Beach Hospital','KimMustBeUniqueTest','Prov. Hood River Hospital','Curry General Hospital','Prov. Willamette Falls M. C.','Good Shepherd M. C.','Prov. St. Mary''s M. C.','Prov. Seaside Hospital','Wallowa Memorial Hospital','St. Anthony Hospital','Testing for bugs','Test Hospital Location','Test hospital','Tillamook County General H.') OR T72.LOCATION IS NULL OR 2020373703 = T72.USER_ID) AND T2.EVENT_DATE_TIME >= T1.VALID_START_DATE AND T2.EVENT_DATE_TIME < T1.VALID_END_DATE ORDER BY c1) T0 WHERE ROWNUM <= 100000";
String parameterStr = "3856,Locations,ICD-9-CM Principal Diagnosis Code,1900243037,1900315528,1900315527,1900234276,1900234235,1900234244,1900234246,RCA304939859,1900298044,RCA304955221,1900298724,RCA304957405,1900234296,1900234297,1900311515,1900311516,1900311518,1900311519,1900311524,1900234345,1900298719,1900234349,1900298725,1900234475,1900234447,1900234448,1900234451,1900234449,1900234450,1900234381,1900234382,RCA304940421,1900234298,1900234380,RCA304940415,1900234241,1900234248,1900234251,1900234247,1900234249,1900234252,1900234250,1900234322,1900234326,1900234324,1900234325,1900234323,1900234366,RCA304940349,1900234369,1,1900298721,1900298722,1900242540,1900242544,1900298720,1900234348,RCA304940255,1900313129,1900313130,1900298727,1900298728,1900298723,1900313382";
replaceParameter(sql, parameterStr);
}
protected static final String dateFormat = "MM/dd/yyyy HH:mm:ss.SSS";
public static String escapeString(String in) {
StringBuilder out = new StringBuilder();
for (int i = 0, j = in.length(); i < j; i++) {
char c = in.charAt(i);
if (c == '\'') {
out.append(c);
}
out.append(c);
}
return out.toString();
}
public static String formatParameterObject(Object object) {
if (object == null) {
return "NULL";
} else {
if (object instanceof String) {
return "'" + escapeString((String) object) + "'";
} else if (object instanceof Date) {
return "'" + new SimpleDateFormat(dateFormat).format(object) + "'";
} else if (object instanceof Boolean) {
return ((Boolean) object).booleanValue() ? "true" : "false";
} else {
return object.toString();
}
}
}
public static String dumpSql(String sql, String[] vars) {
List argTrace = Arrays.asList(vars);
StringBuffer dumpSql = new StringBuffer();
int lastPos = 0;
int Qpos = sql.indexOf('?', lastPos); // find position of first question mark
int argIdx = 0;
String arg;
while (Qpos != -1) {
// get stored argument
synchronized (argTrace) {
try {
arg = (String) argTrace.get(argIdx);
arg = formatParameterObject(arg);
} catch (IndexOutOfBoundsException e) {
arg = "?";
}
}
if (arg == null) {
arg = "?";
}
argIdx++;
dumpSql.append(sql.substring(lastPos, Qpos)); // dump segment of sql up to question mark.
lastPos = Qpos + 1;
Qpos = sql.indexOf('?', lastPos);
dumpSql.append(arg);
}
if (lastPos < sql.length()) {
dumpSql.append(sql.substring(lastPos, sql.length())); // dump last segment
}
dumpSql.append("\n\n");
return dumpSql.toString();
}
}