/// <summary> /// Build the sql for the query /// </summary> /// <param name="qt"></param> /// <returns></returns> public override string BuildSql( ExecuteQueryParms eqp) { string sql = base.BuildSql(eqp); return(sql); }
/// <summary> /// PrepareQuery /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string PrepareQuery( ExecuteQueryParms eqp) { Sql = BuildSql(eqp); return(Sql); }
/// <summary> /// Build the sql for the query /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string BuildSql( ExecuteQueryParms eqp) { Eqp = eqp; // save for possible later reference return("NoSql"); // NonSqlMetaBroker placeholder }
/// <summary> /// Execute the query /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { base.ExecuteQuery(eqp); ActivityClass = new ActivityClass(); return; }
/// <summary> /// Execute query for new set of keys /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { CidToStructureDict = null; KeyListPos = -1; MapPos = -1; return; }
/// <summary> /// Build the sql for a query /// </summary> /// <param name="eqp">ExecuteQueryParms</param> /// <returns>Sql for the query</returns> public override string BuildSql( ExecuteQueryParms eqp) { string sql = ""; MetaColumn mc; List <MetaBrokerType> MbTypes = // metabrokers that can provide unpivoted assay data new List <MetaBrokerType>(); MbTypes.Add(MetaBrokerType.Assay); Eqp = eqp; Qt = eqp.QueryTable; Exprs = FromClause = OrderBy = ""; // outer sql elements KeyMc = Mt.KeyMetaColumn; SelectList = new List <MetaColumn>(); // build list of selected metacolumns foreach (QueryColumn qc in Qt.QueryColumns) { mc = qc.MetaColumn; if (qc.Selected) { SelectList.Add(mc); } } foreach (MetaBrokerType mbt in MbTypes) // get Sql for each broker and union together { IMetaBroker mb = MetaBrokerUtil.Create(mbt); if (mb == null) { throw new Exception("Unrecognized Metabroker: " + mbt); } string sql2 = mb.BuildUnpivotedAssayResultsSql(eqp); //if (actBinAQc != null) // sql2 = Lex.Replace(sql2, "null activity_bin", ActivityBinSqlExpression + " activity_bin"); if (Lex.IsDefined(sql)) { sql += " union all "; } sql += "/*** MetaBrokerType." + mbt.ToString() + " ***/ " + sql2; } sql = " select * from ( " + sql + " ) "; if (Qt.Alias != "") { sql += " " + Qt.Alias; } if (eqp.CallerSuppliedCriteria != "") { sql += " where " + eqp.CallerSuppliedCriteria; } return(sql); }
/// <summary> /// Execute query for new set of keys /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { Eqp = eqp; // save for possible later reference MetaTable mt = eqp?.QueryTable?.MetaTable; GSP(mt).ExecuteQuery(eqp); return; }
/// <summary> /// Prepare for execution /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string PrepareQuery( ExecuteQueryParms eqp) { Eqp = eqp; // save for possible later reference Qt = eqp.QueryTable; MetaTable mt = eqp?.QueryTable?.MetaTable; return(GSP(mt).PrepareQuery(eqp)); }
/// <summary> /// Build sql and prepare query /// </summary> /// <param name="eqp">ExecuteQueryParms</param> /// <returns>Sql for the query</returns> public override string PrepareQuery( ExecuteQueryParms eqp) { Eqp = eqp; Qt = eqp.QueryTable; Sql = BuildSql(eqp); OrderBy = ""; return(Sql); }
/// <summary> /// Execute query /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { Eqp = eqp; Qt = eqp.QueryTable; MetaTable mt = Qt.MetaTable; if (string.IsNullOrEmpty(Sql)) { return; // nothing to do here } base.ExecuteQuery(eqp); }
/// <summary> /// Build the sql for the query /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string BuildSql( ExecuteQueryParms eqp) { string sql = ""; Eqp = eqp; if (Eqp == null) { DebugMx.DataException("Eqp parameter is null"); } Qt = eqp.QueryTable; if (Qt == null) { DebugMx.DataException("Eqp.QueryTable is null"); } MetaTable mt = Qt.MetaTable; if (mt == null) { DebugMx.DataException("Metatable not defined for CalcField Query Table"); } Query q = eqp.Qe.Query; QueryTableData[] qtd = eqp.Qe.Qtd; // query table data QueryColumn qc; MetaColumn mc; KeyMc = mt.KeyMetaColumn; if (KeyMc == null) { throw new Exception("Key (compound number) column not found for MetaTable " + mt.Name); } KeyQci = Qt.GetQueryColumnIndexByName(KeyMc.Name); KeyQc = Qt.QueryColumns[KeyQci]; KeyQc.Selected = true; // be sure key is selected Qt.MetaTable.KeyMetaColumn.ColumnMap = ""; // reset key column mapping Query q2 = InitializeSubQuery(Qt); foreach (QueryTable qtx in q2.Tables) { // todo... } return(sql); }
/// <summary> /// Generate SQL /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string BuildSql( ExecuteQueryParms eqp) { Eqp = eqp; Qt = eqp.QueryTable; MetaTable mt = Qt.MetaTable; KeyMc = mt.KeyMetaColumn; string sql = base.BuildSql(eqp); sql = FormatActivityBinExpression(sql); return(sql); }
/// <summary> /// Prepare for execution /// </summary> /// <param name="eqp"></param> /// <returns></returns> public override string PrepareQuery( ExecuteQueryParms eqp) { Eqp = eqp; Qt = eqp.QueryTable; //PrepareForDecompose(Qt, eqp.Qe.Query); // setup again for this broker instance SelectList = new List<MetaColumn>(); foreach (QueryColumn qc in Qt.QueryColumns) { if (qc.Selected || qc.IsKey) SelectList.Add(qc.MetaColumn); } return ""; // No SQL returned }
/// <summary> /// Execute Related Structure Search /// </summary> /// <param name="eqp"></param> void ExecuteRelatedStructureSearch( ExecuteQueryParms eqp) { if (RSS == null) { RSS = new RelatedStructureSearch(); Query q = StructCriteriaQc.QueryTable.Query; QueryTable qt = StructCriteriaQc.QueryTable; MetaTable mt = qt.MetaTable; QueryColumn qc = StructCriteriaQc; MetaColumn mc = qc.MetaColumn; RSS.QueryMtName = mt.Name; RSS.QueryChimeString = Pssc.Molecule.GetChimeString(); // if no cid in options use passed structure RSS.IncludeQueryStructure = true; RSS.SearchCorp = (Lex.Contains(mt.Name, "Corp")); RSS.SearchChembl = (Lex.Contains(mt.Name, "ChEMBL")); RSS.SearchFSS = SST.IsFull(Pssc.SearchTypeUnion); RSS.SearchMmp = SST.IsMmp(Pssc.SearchTypeUnion); RSS.SearchSmallWorld = SST.IsSw(Pssc.SearchTypeUnion); RSS.SearchSim = SST.IsSim(Pssc.SearchTypeUnion); RSS.SearchSSS = SST.IsSSS(Pssc.SearchTypeUnion); if (RSS.SearchMmp && Lex.IsUndefined(RSS.QueryCid)) // try to get a cid from the structure if we are searching MMP and don't already have one { int corpId = Pssc.Molecule.GetCompoundId(); if (corpId > 0) { RSS.QueryCid = corpId.ToString(); } } RSS.KeysToExclude = q.KeysToExclude; RSS.ExecuteSearch(); } BuildRelatedStructureSearchQueryEngineRows(eqp); return; }
/// <summary> /// Return true if associated query is a valid non-Sql search /// </summary> /// <param name="eqp"></param> /// <returns></returns> public static bool IsNonSqlStructureSearchQueryTable( ExecuteQueryParms eqp) { if (Lex.IsDefined(eqp.CallerSuppliedCriteria)) { return(false); // return false if retrieval step of query } QueryColumn strQc = eqp.QueryTable.FirstStructureQueryColumn; if (strQc == null || strQc.Criteria == "") { return(false); } bool result = CanExecuteInternalStructureSearch(strQc); return(result); }
/// <summary> /// Build the set of results rows based on the current key set /// </summary> /// <param name="eqp"></param> void BuildRelatedStructureSearchQueryEngineRows( ExecuteQueryParms eqp) { HashSet <string> keySet = new HashSet <string>(); if (eqp.SearchKeySubset != null) // subsetting { foreach (string cid0 in eqp.SearchKeySubset) { keySet.Add(cid0); } } List <StructSearchMatch> recs = RSS.AllMatches; // start with full match list Results = new List <object[]>(); // destination results list (may or may not be subsetted) for (int ri = 0; ri < recs.Count; ri++) { StructSearchMatch m = recs[ri]; if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid)) { if (!keySet.Contains(m.SrcCid)) { continue; } } if (RestrictedDatabaseView.KeyIsRetricted(m.SrcCid)) { continue; } object[] voa = new object[2]; voa[0] = m.SrcCid; voa[1] = m; Results.Add(voa); } CursorPos = -1; return; }
/// <summary> /// Build a single-parameter in-list by converting a key list string into a table using string operations and the Dual table /// (From Tom Kyte: Varying in lists..., 2-June-2006) /// (Note that this only works for strings up to 4000 chars and not currently used.) /// </summary> /// <param name="eqp"></param> /// <param name="keyName"></param> /// <param name="keyList"></param> /// <param name="firstKeyIdx"></param> /// <param name="keyCount"></param> /// <returns></returns> public string BuildSingleParameterOracleKeyListPredicate( ExecuteQueryParms eqp, string keyName, List <string> keyList, int firstKeyIdx, int keyCount, out string keyListString) { string sql = keyName + @" in ( select trim( substr (txt, instr (txt, ',', 1, level ) + 1, instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) ) as token from (select ','||:0||',' txt from dual) connect by level <= length(:0)-length(replace(:0,',','')) + 1 )" ; StringBuilder sb = new StringBuilder(); for (int i1 = 0; i1 < keyCount; i1++) { string sKey = keyList[firstKeyIdx + i1]; if (Lex.IsNullOrEmpty(sKey)) { continue; } if (sb.Length > 0) { sb.Append(","); } sb.Append(sKey); } keyListString = sb.ToString(); return(sql); }
/// <summary> /// Build the set of results rows based on the current key set /// </summary> /// <returns></returns> void BuildECFP4QueryEngineRows( ExecuteQueryParms eqp) { HashSet <string> keySet = new HashSet <string>(); if (eqp.SearchKeySubset != null) // subsetting { foreach (string cid0 in eqp.SearchKeySubset) { keySet.Add(cid0); } } List <StructSearchMatch> recs = Ecfp4Dao.MatchList; // start with full match list Results = new List <object[]>(); // destination results list (may or may not be subsetted) for (int ri = 0; ri < recs.Count; ri++) { StructSearchMatch m = recs[ri]; if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid)) { if (!keySet.Contains(m.SrcCid)) { continue; } } object[] voa = new object[VoLength]; voa[0] = m.SrcCid; if (VoLength > 1) { voa[1] = m.MatchScore; } Results.Add(voa); } CursorPos = -1; return; }
/// <summary> /// Execute SmallWorld search /// </summary> /// <param name="eqp"></param> void ExecuteSmallWorldSearch( ExecuteQueryParms eqp) { if (!SmallWorldDao.IsSmallWorldAvailable()) { throw new UserQueryException("SmallWorld searching is not currently available."); } Query q = eqp.QueryTable.Query; QueryTable qt = eqp.QueryTable; SmallWorldPredefinedParameters swp = Pssc.SmallWorldParameters; string smiles = Pssc.Molecule.GetSmilesString(); swp.Smiles = smiles; //swp.Database = "Corp"; LastSwNonSqlBroker = null; // debug - force new broker if (SwDao == null) // if Dao not allocated then allocate & execute the search { if (LastSwNonSqlBroker != null && LastSwNonSqlBroker.Pssc != null && LastSwNonSqlBroker.Pssc.SmallWorldParameters != null && LastSwNonSqlBroker.Pssc.SmallWorldParameters.Serialize() == swp.Serialize()) { SwDao = LastSwNonSqlBroker.SwDao; // get rows from previous search SwDao.BuildSwToQeColumnMap(SelectList); // reset the column map for this broker instance } else { SwDao = new SmallWorldDao(); SwDao.KeysToExclude = q.KeysToExclude; SwDao.ExecuteSearch(swp); SwDao.BuildSwToQeColumnMap(SelectList); // LastSwNonSqlBroker = this; // remember for future use } } BuildSmallWorldQueryEngineRows(eqp); // build the QE row set (filtered) from the SW rowset return; }
/// <summary> /// Build in list predicate using temporary database table /// </summary> /// <param name="eqp"></param> /// <param name="baseSql"></param> /// <param name="keyName"></param> /// <param name="keyList"></param> /// <param name="firstKeyIdx"></param> /// <param name="keyCount"></param> /// <returns></returns> public string BuildTempDbTableKeyListPredicate( ExecuteQueryParms eqp, ref string baseSql, string keyName, List <string> keyList, int firstKeyIdx, int keyCount) { bool intKey = true; if (QueryEngine.AllowNetezzaUse && eqp.AllowNetezzaUse) // && keyCount > MaxNetzzaInListItemCount) { return(DbCommandMx.BuildNetezzaTempDbTableKeyListPredicate(keyName, keyList, firstKeyIdx, keyCount)); } else // if (keyCount > MaxOracleInListItemCount) { return(DbCommandMx.BuildOracleTempDbTableKeyListPredicate(ref baseSql, keyName, intKey, keyList, firstKeyIdx, keyCount)); } }
/// <summary> /// Execute CDK ECFP4 search /// </summary> /// <param name="eqp"></param> void ExecuteECFP4Search( ExecuteQueryParms eqp) { CdkSimSearchMx dao = Ecfp4Dao; if (!CdkSimSearchMx.IsSearchingAvailable()) { throw new UserQueryException("ECFP4 searching is not currently available."); } Query q = eqp.QueryTable.Query; QueryTable qt = eqp.QueryTable; MetaTable mt = qt.MetaTable; if (dao == null) // if Dao not allocated then allocate Dao & execute the search { dao = Ecfp4Dao = new CdkSimSearchMx(); dao.KeysToExclude = q.KeysToExclude; if (eqp.SearchKeySubset != null) { dao.SearchKeySubset = new HashSet <string>(eqp.SearchKeySubset); } string databases = mt.Name + "," + mt.Label; // quick/dirty DB Id List <StructSearchMatch> hits = dao.ExecuteSearch(Pssc.Molecule.GetMolfileString(), databases, FingerprintType.Circular, Pssc.MinimumSimilarity, Pssc.MaxSimHits); VoLength = 1; if (qt.GetSelectedMolsimQueryColumn() != null) { VoLength = 2; } } BuildECFP4QueryEngineRows(eqp); // build the QE row set (filtered) return; }
/// <summary> /// Execute non-Oracle query /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { QueryTable qt = Eqp.QueryTable; MetaTable mt = qt.MetaTable; if (Pssc.SearchType == StructureSearchType.MolSim && Pssc.SimilarityType == SimilaritySearchType.ECFP4) { ExecuteECFP4Search(eqp); } else if (Pssc.SearchType == StructureSearchType.SmallWorld) { ExecuteSmallWorldSearch(eqp); } else if (Pssc.SearchType == StructureSearchType.Related) { ExecuteRelatedStructureSearch(eqp); } else if (mt.MetaBrokerType == MetaBrokerType.Annotation) { ExecuteAnnotationStructureSearch(); } else if (Lex.StartsWith(mt.Name, "USERDATABASE_STRUCTURE_")) { ExecuteUserDatabaseStructureSearch(); } else { ExecuteInternalOracleStructureColumnSearch(); // internal search of structures stored in general oracle table column } return; }
/// <summary> /// Build the set of results rows based on the current key set /// </summary> /// <returns></returns> void BuildSmallWorldQueryEngineRows( ExecuteQueryParms eqp) { HashSet <string> keySet = new HashSet <string>(); if (eqp.SearchKeySubset != null) // subsetting { foreach (string cid0 in eqp.SearchKeySubset) { keySet.Add(cid0); } } List <SmallWorldMatch> recs = SwDao.MatchList; // start with full match list Results = new List <object[]>(); // destination results list (may or may not be subsetted) for (int ri = 0; ri < recs.Count; ri++) { SmallWorldMatch m = recs[ri]; if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.Cid)) { if (!keySet.Contains(m.Cid)) { continue; } } object[] voa = m.BuildVoa(SwDao, ri); Results.Add(voa); } CursorPos = -1; return; }
/// <summary> /// Read in data, pivot & buffer for supplied set of rows. /// This is called for retrieval only, not for search /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { DbCommandMx drd; int rowsFetched = 0, vosCreated = 0; MetaTable mt; MetaColumn mc = null; DateTime dt; PivotMetaBroker mb; List <GenericMetaBroker> mbList; string cid, pivotKey, tableFilter, s, txt, tok; int fci, mci, pvi, pci, si, i1; object[] vo = null; object o; if (!PivotInCode) // let Oracle do the pivoting? { base.ExecuteQuery(eqp); return; } // Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source int t0 = TimeOfDay.Milliseconds(); Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo; mt = eqp.QueryTable.MetaTable; string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey]; if (mpd.FirstTableName != mt.Name) { return; // retrieve data for all tables when we see first table } mpd.ClearBuffers(); // Build sql StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable tableFilter = ""; if (mt.TableFilterColumns.Count == 1) { // build single in list if single filter column foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(","); } sb.Append(mt.TableFilterValues[0]); tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")"; } } else // multiple table filter columns, build and/or expressions { foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(" or "); } tableFilter = "(" + GetTableFilterCriteria(mt) + ")"; sb.Append(tableFilter); } tableFilter = "(" + sb.ToString() + ")"; } string sql = "select * from " + mt.TableMap + " where "; if (tableFilter != "") { sql += tableFilter + " and "; } sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) "; // Read unpivoted data, merge/pivot & buffer pivoted rows List <string> keySubset = eqp.SearchKeySubset; if (keySubset == null) { keySubset = GetPreviewSubset(); // assume previewing of single table if no subset } List <string> parmList = new List <string>(); for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized { string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable); if (key != null) { parmList.Add(key); } } drd = new DbCommandMx(); drd.PrepareListReader(sql, DbType.String); drd.ExecuteListReader(parmList); while (drd.Read()) { rowsFetched++; string tableFilterKey = ""; // get column values to identify table for (fci = 0; fci < mt.TableFilterColumns.Count; fci++) { o = drd.GetObjectByName(mt.TableFilterColumns[fci]); if (o == null) { s = ""; } else { s = o.ToString(); } if (tableFilterKey != "") { tableFilterKey += ", "; } tableFilterKey += s; } mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey]; if (mt == null) { continue; // continue if don't know about this table } if (!mpd.MbInstances.ContainsKey(mt.Name)) { continue; // have row hash for broker? } int mbIdx = 0; mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList); while (true) // copy out for each metabroker { mt = mb.Qt.MetaTable; if (mt == null) { continue; } if (mb.MultipivotRowDict == null) { mb.MultipivotRowDict = new Dictionary <string, object[]>(); } string rowKey = ""; for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (o == null) { s = "<null>"; } else { s = o.ToString(); } rowKey += "<" + s + ">"; } if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row? { vo = (object[])mb.MultipivotRowDict[rowKey]; } else // new row, create vo for it & fill in merged column values { vo = new Object[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values { mc = mb.SelectList[si]; if (mc.PivotValues != null) { continue; // skip pivoted cols for now } for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci])) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (mc.IsKey) // normalize cid adding prefix as needed { o = CompoundId.Normalize(o.ToString(), mt); } vo[si] = o; break; } } } mb.MultipivotRowDict[rowKey] = vo; vosCreated++; } // Pivot out data based on pivot column values if (mb.PivotKeys == null) { // build set of pivot keys for the pivoted columns in the table if not done yet mb.PivotKeys = new string[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) { mc = mb.SelectList[si]; if (mc.PivotValues == null) { continue; // skip non-pivoted cols } pivotKey = ""; for (pvi = 0; pvi < mc.PivotValues.Count; pvi++) { pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">"; } mb.PivotKeys[si] = pivotKey; } } pivotKey = ""; for (pci = 0; pci < mt.PivotColumns.Count; pci++) { // build pivot key for this unpivoted row o = drd.GetObjectByName(mt.PivotColumns[pci]); if (o == null) { s = "<null>"; } else { s = o.ToString().ToLower(); } pivotKey += "<" + s + ">"; } for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values { if (mb.PivotKeys[si] == null || // skip non-pivoted cols pivotKey != mb.PivotKeys[si]) { continue; // and non-matches } mc = mb.SelectList[si]; int ci = drd.Rdr.GetOrdinal(mc.ColumnMap); if (mc.DataType == MetaColumnType.Integer) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetInt(ci); } else // value with possible resultId, linking information { txt = drd.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Number) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDouble(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.QualifiedNo) { // todo } else if (mc.DataType == MetaColumnType.String) { if (!mc.DetailsAvailable) { vo[si] = drd.GetString(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Date) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDateTime(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Structure) { // structures come in as compound identifiers (todo: fix for annotation) tok = Dr.GetValue(si).ToString(); cid = CompoundId.Normalize(tok, Qt.MetaTable); vo[si] = cid; } else if (mc.DataType == MetaColumnType.MolFormula) { vo[si] = drd.GetString(ci); } else if (mc.DataType == MetaColumnType.DictionaryId) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Image) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Hyperlink) { txt = drd.GetString(ci); Hyperlink hlink = new Hyperlink(); vo[si] = hlink; hlink.Text = txt; } } if (mbList == null) { break; // single broker } mbIdx++; // go to next broker if (mbIdx >= mbList.Count) { break; // at end of brokers? } mb = (PivotMetaBroker)mbList[mbIdx]; } // end of broker loop } // end of read loop drd.Dispose(); return; }
/// <summary> /// Build and save SQL for use in Spotfire information link /// </summary> /// <param name="query"></param> /// <returns></returns> public static int SaveSpotfireSql( string sqlStmtName, Query query) { DbConnectionMx conn = null; string qtSql = "", sql, expr; string keys = null; string keyColName = ""; string t1KeyColExpr = "", keyColExpr; string rootDataSource = "DEV857"; string rootSchema = "MBS_OWNER"; //if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries"); Query q = query.Clone(); // Clean up query before generating SQL q.KeyCriteria = q.KeyCriteriaDisplay = ""; List <QueryTable> qtToRemove = new List <QueryTable>(); foreach (QueryTable qt0 in q.Tables) { if (qt0.SelectedCount <= 1 || !qt0.MetaTable.RetrievesDataFromQueryEngine) { qtToRemove.Add(qt0); continue; } foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria { if (Lex.IsDefined(qc0.Criteria)) { qc0.Criteria = qc0.CriteriaDisplay = ""; } } } foreach (QueryTable qt0 in qtToRemove) { q.RemoveQueryTable(qt0); } string selectList = ""; // top-level list of selected columns string fromList = ""; // sql for each QueryTable string joinCriteria = ""; // join criteria between QueryTables int remapCount = 0; q.AssignUndefinedAliases(); q.MarkDuplicateNamesAndLabels(); for (int ti = 0; ti < q.Tables.Count; ti++) { QueryTable qt = q.Tables[ti]; if (ti == 0) { keyColName = qt.MetaTable.KeyMetaColumn.Name; } QueryEngine qe = new QueryEngine(); ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt); eqp.ReturnQNsInFullDetail = false; qtSql = qe.BuildSqlForSingleTable(eqp); qtSql = Lex.Replace(qtSql, "/*+ hint */ ", ""); conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema if (conn == null) { throw new Exception("Connection not found for: " + rootDataSource); } // Recast numeric cols that are integers as integers for Spotfire List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn); string qtSelectList = ""; remapCount = 0; // number of cols remapped int sci = -1; foreach (QueryColumn qc in qt.QueryColumns) { if (!qc.Selected) { continue; } sci++; // synch with cmdList MetaColumn mc = qc.MetaColumn; string mcName = mc.Name; if (q.Tables.Count > 1) // if more than one table qualify by table name { mcName = qt.Alias + "." + mcName; } string colName = qc.UniqueName; //if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug //if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId)) { DbColumnMetadata md = cmdList[sci]; expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr; } else if (mcName != colName) { expr = mcName + " " + colName; remapCount++; } else { expr = mc.Name; } if (qtSelectList != "") { qtSelectList += ", "; } qtSelectList += expr; } if (selectList != "") { selectList += ", "; } selectList += qtSelectList; if (fromList != "") { fromList += ", "; } fromList += "(" + qtSql + ") " + qt.Alias; keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name; if (ti == 0) { t1KeyColExpr = keyColExpr; } else { if (joinCriteria != "") { joinCriteria += " and "; } joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr; } } selectList += " "; // be sure last col name in list is delimited with a space if (q.Tables.Count == 1 && remapCount == 0) { sql = qtSql; // simple single table with no remapping of cols } else // combine list of elements { sql = "select " + selectList + " from " + fromList; if (joinCriteria != "") { sql += " where " + joinCriteria; } sql = "select * from (" + sql + ")"; // encapsulate the SQL } int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName); return(v2); }
/// Build an unparameterized key list SQL predicate /// </summary> /// <param name="Qt"></param> /// <param name="keyName">Key column name qualified by table name/alias</param> /// <summary> /// Build an unparameterized key list SQL predicate /// </summary> /// <param name="eqp"></param> /// <param name="baseSql"></param> /// <param name="keyName">Key column name qualified by table name/alias</param> /// <param name="keyList"></param> /// <param name="firstKeyIdx"></param> /// <param name="keyCount"></param> /// <param name="keyCriteria">Full criteria including col name operator and list</param> /// <param name="keyListString">Just the list of keys</param> public void BuildUnparameterizedKeyListPredicate( ExecuteQueryParms eqp, ref string baseSql, string keyName, List <string> keyList, int firstKeyIdx, int keyCount, out string keyCriteria, out string keyListString) { StringBuilder sb; int i1; QueryTable qt = eqp.QueryTable; MetaTable mt = qt.MetaTable; bool integerKey = mt.IsIntegerKey(); List <StringBuilder> sublists = new List <StringBuilder>(); sb = new StringBuilder(); sublists.Add(sb); int sublistKeyCount = 0; // current keys in predicate keyCriteria = keyListString = null; for (i1 = 0; i1 < keyCount; i1++) { if (sublistKeyCount >= DbCommandMx.MaxOracleInListItemCount) { sb = new StringBuilder(); sublists.Add(sb); sublistKeyCount = 0; } if (sb.Length > 0) { sb.Append(","); } string key = CompoundId.NormalizeForDatabase((string)keyList[firstKeyIdx + i1], qt.MetaTable); if (key == null) { key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value } if (!integerKey || !Lex.IsInteger(key)) // quote it if not integer column or value { key = Lex.AddSingleQuotes(key); // (note: quoted integers can cause mismatches for some database systems, e.g. Denodo) } sb.Append(key); sublistKeyCount++; } sb = new StringBuilder(); if (sublists.Count >= 2) { sb.Append("("); // wrap in parens if multiple sublists } for (int sli = 0; sli < sublists.Count; sli++) { if (sli > 0) { sb.Append(" or "); } sb.Append(keyName + " in (" + sublists[sli] + ")"); } if (sublists.Count >= 2) { sb.Append(")"); } keyCriteria = sb.ToString(); keyListString = sublists[0].ToString(); // return just the first sublist (adjust later for larger lists) return; }
/// <summary> /// Execute query /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { MetaTable mt; MetaColumn mc; Query q; QueryTable qt; QueryColumn qc; ResultsPage rp; ResultsViewProps view; CompoundStructureActivityData cd1, cd2; bool smallerIsbetter; double r1, r2, r3, r4; int di, di2, pdi, pdi2, i3; string tok; qt = eqp.QueryTable; qc = qt.GetQueryColumnByNameWithException(SasMapParms.ParametersMetaColumnName); AssertMx.IsDefined(qc.Criteria, qc.Label + " criteria not defined"); if (Lex.Eq(qc.Criteria, LastCriteriaString)) // if same criteria as last time then use existing data { VoListPos = -1; // init list position return; } VoList = new List<object[]>(); VoListPos = -1; // init list position LastCriteriaString = qc.Criteria; ParsedSingleCriteria psc = ParsedSingleCriteria.Parse(qc); SMP = SasMapParms.Deserialize(psc.Value); mc = SMP.EndpointMc; smallerIsbetter = mc.MultiPoint; List<CompoundStructureActivityData> ds1 = ReadData(SMP); // read in the data to analyze if (ds1 == null || ds1.Count == 0) return; // throw new QueryException("No data retrieved"); List<CompoundStructureActivityData> ds2 = ds1; // just one set for now // Calculate difference or ratio coefficents for each pair List<PairData> pd = new List<PairData>(); int minCoef = -1; // index of minimum coefficent selected so far double molFactor = AssayAttributes.GetMolarConcFactor(SMP.EndpointMc); for (di = 0; di < ds1.Count; di++) { // process all compounds in 1st set // if (ds1[di].Nearest == 0) continue; // any data? if (ds2 == ds1) di2 = di + 1; // only do lower rt diagonal if one dataset else di2 = 0; // must do all compares, check for dups later for ( /* start at di2 */; di2 < ds2.Count; di2++) { // if (ds2[di2].Nearest == 0) continue; // any data? if (ds1[di].Cid == ds2[di2].Cid) continue; // avoid self compare double sim = // similarity CalculateSimilarity(ds1[di], ds2[di2]); //if (sim==1.0 && !stereo) // eliminate stereo pairs if requested // continue; // a more careful check may be needed if (sim < SMP.MinimumSimilarity) continue; // below cutoff value? double denom = 1 - sim; // denominator is 1 - sim if (denom == 0) denom = .00000000001f; // avoid divide by zero double actChange = 0; if (smallerIsbetter && ds1[di].Activity < ds2[di2].Activity) { cd1 = ds1[di]; cd2 = ds2[di2]; } else { cd1 = ds2[di2]; cd2 = ds1[di]; } double a1 = cd1.Activity; double a2 = cd2.Activity; if (a1 == NullValue.NullNumber || a2 == NullValue.NullNumber) actChange = 0; else switch (SMP.ActDiffCalcType) { case ActDiffCalcType.SimpleDifference: // activity difference { actChange = a1 - a2; break; } case ActDiffCalcType.NegativeLog: { actChange = -Math.Log10(a1) - -Math.Log10(a2); break; } case ActDiffCalcType.MolarNegativeLog: { actChange = (-Math.Log10(a1 * molFactor)) - (-Math.Log10(a2 * molFactor)); break; } case ActDiffCalcType.Ratio: // activity ratio { r1 = a1; if (r1 == 0) r1 = .00000000001f; r2 = a2; if (r2 == 0) r2 = .00000000001f; r3 = r1 / r2; r4 = r2 / r1; actChange = r3; if (SMP.UseAbsoluteValue && r4 > r3) // take the max value actChange = r4; break; } case ActDiffCalcType.Advanced: { throw new InvalidOperationException("SarMapCalcType.Advanced"); } default: throw new InvalidOperationException("SarMapCalcType: " + (int)SMP.ActDiffCalcType); } if (SMP.UseAbsoluteValue && SMP.ActDiffCalcType != ActDiffCalcType.Ratio) actChange = Math.Abs(actChange); double coef = actChange / denom; if (pd.Count < SMP.MaxPairCount) // just add this pair to end { pdi = pd.Count; pd.Add(new PairData()); } else { // see if this value is greater than anything in list if (minCoef < 0) { // find element with minimum coef minCoef = 0; for (i3 = 1; i3 < pd.Count; i3++) { if (pd[i3].Coef < pd[minCoef].Coef) minCoef = i3; } } if (coef <= pd[minCoef].Coef) continue; // if this one better? //if (ds1 != ds2) //{ // be sure not a duplicate of what we have in list // for (i3 = 0; i3 < pd.Count; i3++) // { // check for pair in list already // if ((di == pd[i3].CD1 && di2 == pd[i3].CD2) || // (di == pd[i3].CD2 && di2 == pd[i3].CD1)) break; // } // if (i3 < pd.Count) continue; // continue to next pair if found //} pdi = minCoef; // replace this item minCoef = -1; // reset to get new minimum next time } // Save data for the pair pd[pdi].CD1 = cd1; pd[pdi].CD2 = cd2; pd[pdi].Sim = sim; pd[pdi].ActChange = actChange; pd[pdi].Coef = coef; } } // Build the list of pair Vos int voLen = qt.SetSimpleVoPositions(); PairData pdItem; for (pdi = 1; pdi < pd.Count; pdi++) // sort from max to min coef value { pdItem = pd[pdi]; for (pdi2 = pdi - 1; pdi2 >= 0; pdi2--) { if (pdItem.Coef < pd[pdi2].Coef) break; pd[pdi2 + 1] = pd[pdi2]; } pd[pdi2 + 1] = pdItem; } for (pdi = 0; pdi < pd.Count; pdi++) { pdItem = pd[pdi]; cd1 = pdItem.CD1; cd2 = pdItem.CD2; object[] vo = new object[voLen]; VoArray.SetVo(qt, "PAIR_ID", vo, new NumberMx(pdi + 1)); VoArray.SetVo(qt, "COMPOUND1", vo, new StringMx(cd1.Cid)); VoArray.SetVo(qt, "STRUCTURE1", vo, cd1.Structure); VoArray.SetVo(qt, "ACTIVITY1", vo, new NumberMx(cd1.Activity)); VoArray.SetVo(qt, "COMPOUND2", vo, new StringMx(cd2.Cid)); VoArray.SetVo(qt, "STRUCTURE2", vo, cd2.Structure); VoArray.SetVo(qt, "ACTIVITY2", vo, new NumberMx(cd2.Activity)); VoArray.SetVo(qt, "SIMILARITY", vo, new NumberMx(pdItem.Sim)); VoArray.SetVo(qt, "ACTIVITY_DIFF", vo, new NumberMx(pdItem.ActChange)); VoArray.SetVo(qt, "ACT_SIM_COEF", vo, new NumberMx(pdItem.Coef)); VoList.Add(vo); } VoListPos = -1; // init list position return; }
/// <summary> /// Clone ExecuteQueryParms /// </summary> /// <returns></returns> public ExecuteQueryParms Clone() { ExecuteQueryParms eqp = (ExecuteQueryParms)this.MemberwiseClone(); return(eqp); }
/// <summary> /// Build the sql for a query doing preparation for separate fetch of data. /// </summary> /// <param name="parms"></param> public override string PrepareQuery( ExecuteQueryParms eqp) { MultiTablePivotBrokerTypeData mpd; // multipivot data for this broker type List <GenericMetaBroker> mbList; string tableFilterKey; Eqp = eqp; Qt = eqp.QueryTable; MetaTable mt = Qt.MetaTable; if (!eqp.ReturnQNsInFullDetail || // no multipivot if part of calc field eqp.Qe == null) // need to be able to access queryengine info //!QueryEngine.AllowMultiTablePivot) // is multipivot even allowed { return(base.PrepareQuery(eqp)); } PivotedColCount = 0; foreach (QueryColumn qc1 in Qt.QueryColumns) { // if any criteria then pivot individually rather than via multipivot if (qc1.Criteria != "" && !qc1.IsKey) { return(base.PrepareQuery(eqp)); } if (qc1.MetaColumn.PivotValues != null) { PivotedColCount++; } } if (PivotedColCount == 0) { return(base.PrepareQuery(eqp)); // must have at least one column to pivot } // Store pivot info for queryTable PivotInCode = true; BuildSql(eqp); // setup SelectList (don't really need sql) MpGroupKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql if (!QueryEngine.AllowMultiTablePivot) { MpGroupKey += "_" + Qt.MetaTable.Name; } if (eqp.Qe.MetaBrokerStateInfo == null) { eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>(); } Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo; if (!mbsi.ContainsKey(MpGroupKey)) { // create entry for data source mpd = new MultiTablePivotBrokerTypeData(); mbsi[MpGroupKey] = mpd; mpd.FirstTableName = mt.Name; mpd.TableFilterValuesToMetaTableDict = new Dictionary <string, MetaTable>(); mpd.TableCodeCsvList = new StringBuilder(); mpd.TableCodeDict = new Dictionary <string, MpdResultTypeData>(); mpd.MbInstances = new Dictionary <string, object>(); } else { mpd = (MultiTablePivotBrokerTypeData)mbsi[MpGroupKey]; // get existing entry } string pms = Csv.JoinCsvString(mt.TableFilterValues); // values to identify this metatable mpd.AddMetaBroker(Qt.MetaTable.Name, this); // Store TableFilter values associated with metatable if (mt.TableFilterValues.Count == 1) { // handle case with list of values allowed for single filter column (e.g. pivot values) List <string> sl = Csv.SplitCsvString(mt.TableFilterValues[0]); foreach (string s in sl) { mpd.TableFilterValuesToMetaTableDict[s.ToLower()] = mt; } } else { tableFilterKey = ""; foreach (string s in mt.TableFilterValues) { if (tableFilterKey != "") { tableFilterKey += ", "; } tableFilterKey += s; } mpd.TableFilterValuesToMetaTableDict[tableFilterKey.ToLower()] = mt; } #if false // todo: store codes for quick row identification if (!mpd.CodeHash.ContainsKey(Qt.MetaTable.Code)) // include assay code { mpd.CodeHash[pms] = null; // add key to hash list if (mpd.Codes.Length > 0) { mpd.Codes.Append(","); } mpd.Codes.Append(pms); } #endif return(null); // no sql generated here }
/// <summary> /// Generate the sql for retrieval from a querytable /// If this query returns qualified numbers (i.e. is not search part of query or part of calc field) /// then don't retrieve any data other than the compound id & group id. /// </summary> /// <param name="qt"></param> public override string BuildSql( ExecuteQueryParms eqp) { List <MetaColumn> mcl; List <QueryColumn> qcl; QueryColumn qc; MetaTable mt; MetaColumn mc; Hashtable resultKeys; string tableAlias, txt, tok; string pivotExprs, pivotTables = "", pivotCriteria; int ci, qci, i1; Eqp = eqp; Qt = eqp.QueryTable; Exprs = FromClause = OrderBy = ""; // outer sql elements qcl = Qt.QueryColumns; mt = Qt.MetaTable; mcl = mt.MetaColumns; KeyMc = mt.KeyMetaColumn; resultKeys = new Hashtable(); SelectList = new List <MetaColumn>(); // list of selected metacolumns int selectCnt = 0; // counting key int criteriaCnt = 0; // not counting key QueryColumn firstCriteriaPivotedCol = null; // first pivoted column with criteria QueryColumn firstSelectedPivotedCol = null; // first pivoted column that's selected StringBuilder pivotValueList = new StringBuilder(); // build list of selected result codes for (qci = 0; qci < qcl.Count; qci++) { qc = qcl[qci]; mc = qc.MetaColumn; if (mc.PivotValues != null && mc.PivotValues.Count == 1 && (qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted)) { if (pivotValueList.Length > 0) { pivotValueList.Append(","); } pivotValueList.Append(mc.PivotValues[0]); } if (qc.Is_Selected_or_GroupBy_or_Sorted) { selectCnt++; if (mc.PivotValues != null && firstSelectedPivotedCol == null && !eqp.ReturnQNsInFullDetail) // don't count if getting qualified numbers since handled differently { firstSelectedPivotedCol = qc; } } if (qc.Criteria != "") { criteriaCnt++; if (mc.PivotValues != null && firstCriteriaPivotedCol == null) { firstCriteriaPivotedCol = qc; } } } if ((eqp.Qe.MqlLogicType != QueryLogicType.And && criteriaCnt > 1)) { firstSelectedPivotedCol = firstCriteriaPivotedCol = null; // don't use this optimization unless "and" logic } // Add key column to selected list if not already selected qc = Qt.KeyQueryColumn; if (qc == null) { throw new Exception("Key column not found for MetaTable " + mt.Name); } if (!qc.Selected) // put key at start of list if not already selected { SelectList.Add(mt.KeyMetaColumn); selectCnt++; } //////////////////////////////////////////////////////////////////////////////// // Build sql //////////////////////////////////////////////////////////////////////////////// // // An inner query in the from clause pivots out the columns that are // selected or have criteria // //////////////////////////////////////////////////////////////////////////////// pivotExprs = // key field with native col name mapped to mc name "t0." + mt.KeyMetaColumn.ColumnMap + " " + mt.KeyMetaColumn.Name; // key field with native col name mapped to mc name pivotCriteria = ""; int pivotCount = 0; QueryColumn t0Col = null; // column assigned to t0 if (firstCriteriaPivotedCol != null) { t0Col = firstCriteriaPivotedCol; // if there is a pivoted column with criteria assign to t0 } else if (selectCnt == 2 && firstSelectedPivotedCol != null) { t0Col = firstSelectedPivotedCol; // single selected pivoted col assigned to t0 } else { // multiple (or zero) selected pivoted cols, build "select unique" to join all pivoted values to pivotTables = "(select unique "; for (int mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { if (mci > 0) { pivotTables += ", "; } pivotTables += mt.PivotMergeColumns[mci]; } pivotTables += " from " + mt.TableMap + " "; string tableSelectionExpr = GetTableFilterCriteria(mt); if (pivotValueList.Length > 0) // if we have any pivot value then limit to those { if (tableSelectionExpr != "") { tableSelectionExpr += " and "; } tableSelectionExpr += mt.PivotColumns[0] + " in (" + pivotValueList + ")"; } pivotTables += " where " + tableSelectionExpr + ") t0 "; } // Process query column objects for (qci = 0; qci < qcl.Count; qci++) { qc = qcl[qci]; mc = qc.MetaColumn; // Pivot out the result if (!qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted) { continue; } if (mt.KeyMetaColumn == mc) { ; // already have key column } else if (mc.PivotValues != null) // pivot out of results table { if (qc == t0Col) { tableAlias = "t0"; // col to be used as t0 } else { pivotCount++; tableAlias = "t" + (pivotCount).ToString(); } pivotExprs += ", "; // add one or more expressions for this column string colName = tableAlias + "." + mc.ColumnMap; // name for this col qualified by table alias if (Eqp.ReturnQNsInFullDetail && // return in qn format? (mc.DataType == MetaColumnType.QualifiedNo || mc.DetailsAvailable)) { if (qc.Is_Selected_or_GroupBy_or_Sorted) { // build expression to retrieve catenated col value & linking info string colExpr = colName; if (mc.DataType == MetaColumnType.Date) // return normalized, sortable date string (annotation tables) { colExpr = "to_char(" + colName + ",'YYYYMMDD HH24MISS')"; } string qnExpr = AppendQnElement(mt.QnQualifier) + " chr(11) || " + AppendQnElement(mt.QnNumberValue) + " chr(11) || " + AppendQnElement(mt.QnTextValue) + " chr(11) || " + AppendQnElement(mt.QnNValue) + " chr(11) || " + AppendQnElement(mt.QnNValueTested) + " chr(11) || " + AppendQnElement(mt.QnStandardDeviation) + " chr(11) || " + AppendQnElement(mt.QnStandardError) + " chr(11) || " + AppendQnElement(mt.QnLinkValue); qnExpr = qnExpr.Replace("<TA>", tableAlias); pivotExprs += qnExpr + " " + mc.Name + ", "; } pivotExprs += // native column value for comparison colName + " " + mc.Name + "_val "; } else // not a qualified number or not returning qualified numbers { //if (qc.Criteria.ToLower().EndsWith(" is not null")) // if is-not-null criteria then any value will do not just number //pivotExprs += tableAlias + ".assay_rslt_typ_id " + mc.Name + "_val "; // todo: fix to work with complex criteria //else pivotExprs += // just return native value colName + " " + mc.Name + ", "; pivotExprs += // native column value for comparison colName + " " + mc.Name + "_val "; } if (pivotTables != "") { pivotTables += ", "; } pivotTables += GetSourceWithTableFilterCriteria(mt) + " " + tableAlias + " "; txt = ""; for (int pci = 0; pci < mt.PivotColumns.Count; pci++) { // filter unpivoted source for just this column if (txt != "") { txt += " and "; } txt += "<TA>." + mt.PivotColumns[pci] + " (+) = " + mc.PivotValues[pci] + " "; } if (qc != t0Col) // joint to t0 on merge columns unless we are t0 { foreach (string mergeCol in mt.PivotMergeColumns) { txt += "and <TA>." + mergeCol + " (+) = t0." + mergeCol + " "; } } txt = txt.Replace("<TA>", tableAlias); // plug in proper table alias if (qc.Criteria != "" || selectCnt <= 2) { txt = txt.Replace("(+)", ""); // remove outer join if criteria on this column } if (pivotCriteria != "") { pivotCriteria += " and "; } pivotCriteria += txt + " "; } else // nulls for other non-result fields { pivotExprs += ", null " + mc.Name + " "; } // Add to select list if selected if (qc.Is_Selected_or_GroupBy_or_Sorted) // if selected add to expression list { if (mt.KeyMetaColumn == mc) // save index of compound id value object { KeyVoi = SelectList.Count; } if (Exprs.Length > 0) { Exprs += ","; } Exprs += mc.Name; SelectList.Add(mc); } } // end of column loop FromClause = "(select " + pivotExprs + " " + "from " + pivotTables; if (pivotCriteria != "") { FromClause += " where " + pivotCriteria; } FromClause += ")"; Sql = "select /*+ first_rows */ " + Exprs + " from " + FromClause; if (Qt.Alias != "") { Sql += " " + Qt.Alias; } if (eqp.CallerSuppliedCriteria != "") { Sql += " where " + eqp.CallerSuppliedCriteria; } return(Sql); }