Example #1
0
        /// <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
        }
Example #2
0
        /// <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;
        }
Example #3
0
        /// <summary>
        /// Execute query in preparation for retrieving rows
        /// </summary>
        /// <param name="parms"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            BufferedRows = new List <UnpivotedAssayResult>();

            MultiTablePivotBrokerTypeData mpd = null;
            MetaTable mt;
            List <MultiDbAssayMetaBroker> mbList = null;
            MultiDbAssayMetaBroker        mb, mb2 = null;
            UnpivotedAssayResult          rr, rr2;
            List <UnpivotedAssayResult>   rrList = new List <UnpivotedAssayResult>(); // list of results summarized by target & result type
            bool unpivotedTableIsFirst;

            object[] vo;
            string   mtName = null;

            int t0 = TimeOfDay.Milliseconds();

            mt = eqp.QueryTable.MetaTable;

            Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;

            if (PivotInCode)
            {
                mpd = mbsi[MpGroupKey];

                if (PivotInCode && mpd.MbInstances.Count == 1 && UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name))
                {
                    PivotInCode = false; // don't multipivot if single unpivoted summary table
                }
            }

            if (!PivotInCode)     // if not multipivot then call generic broker
            {
                base.ExecuteQuery(eqp);
                return;
            }

            if (mpd.FirstTableName != Qt.MetaTable.Name)
            {
                return;                                              // retrieve data for all tables when we see first table
            }
            SetupSecondaryMetaBroker(eqp, mpd, out unpivotedTableIsFirst);
            mb2 = SecondaryMetaBroker;

            mpd.ClearBuffers();

            // Retrieve data & store for associated metabrokers

            if (mb2 == this)
            {
                base.ExecuteQuery(eqp);                  // execute with the base generic broker
            }
            else
            {
                mb2.ExecuteQuery(Eqp2);      // use the secondary broker that was created
            }
            AssayDict dict = new AssayDict();

            rr = new UnpivotedAssayResult();
            int  readCnt             = 0;
            bool includeResultDetail = MdbAssayVoMap.ResultDetailId.Voi >= 0;

            while (true)
            {
                if (mb2 == this)
                {
                    vo = base.NextRow();                      // get data via generic broker
                }
                else
                {
                    vo = mb2.NextRow();          // get data with secondary broker
                }
                if (vo == null)
                {
                    break;
                }

                rr.FromValueObject(vo, mb2.MdbAssayVoMap);         // parse values into a UnpivotedAssayResult
                int rowsFetched = 0, vosCreated = 0;

                for (int pup = 0; pup < 2; pup++)         // first pass for unpivoted table, 2nd for pivoted by gene
                {
                    //try
                    //{
                    if (pup == 0)
                    {
                        if (!unpivotedTableIsFirst)
                        {
                            continue;                                // if no unpivoted first table skip this
                        }
                        mtName = mpd.FirstTableName;                 // unpivoted table should be first
                    }

                    else             // pivoted table
                    {
                        if (Lex.IsNullOrEmpty(rr.GeneSymbol))
                        {
                            continue;                                                                      // skip if no target symbol
                        }
                        if (Lex.Contains(mpd.FirstTableName, "CORP"))                                      // mapped to pivoted corp only table
                        {
                            mtName = MultiDbAssayDataNames.BasePivotTablePrefix + rr.GeneSymbol.ToUpper(); // name of table mapped to
                        }
                        else                                                                               // combined tables
                        {
                            mtName = MultiDbAssayDataNames.CombinedPivotTablePrefix + rr.GeneSymbol.ToUpper();
                        }
                    }
                    //}
                    //catch (Exception ex) { ex = ex; }
                    mt = MetaTableCollection.Get(mtName);
                    if (mt == null)
                    {
                        continue;
                    }
                    if (!mpd.MbInstances.ContainsKey(mt.Name))
                    {
                        continue;                                                    // have row hash for broker?
                    }
                    int mbIdx = 0;
                    if (mpd.MbInstances[mtName] is MultiDbAssayMetaBroker)
                    {
                        mb = (MultiDbAssayMetaBroker)mpd.MbInstances[mtName];                 // broker assoc w/table
                    }
                    else
                    {
                        mbList = (List <MultiDbAssayMetaBroker>)mpd.MbInstances[mtName];
                        mb     = (MultiDbAssayMetaBroker)mbList[0];
                    }

                    while (true)             // copy out for each metabroker for metatable
                    {
                        UnpivotedAssayResultFieldPositionMap voMap = mb.MdbAssayVoMap;
                        vo = rr.ToValueObject(mb.Qt.SelectedCount, voMap);

                        if (mb.MultipivotRowList == null)
                        {
                            mb.MultipivotRowList = new List <object[]>();
                        }
                        mb.MultipivotRowList.Add(vo);

                        if (mbList == null)
                        {
                            break;               // single broker
                        }
                        mbIdx++;                 // go to next broker
                        if (mbIdx >= mbList.Count)
                        {
                            break;                                        // at end of brokers?
                        }
                        mb = (MultiDbAssayMetaBroker)mbList[mbIdx];
                    }
                } // tables to copy data to loop
            }     // row fetch loop

            return;
        }
Example #4
0
/// <summary>
///  Setup/configure any needed secondary metabroker
/// </summary>
/// <param name="eqp"></param>
/// <param name="mpd"></param>
/// <param name="unpivotedTableIsFirst"></param>

        void SetupSecondaryMetaBroker(
            ExecuteQueryParms eqp,
            MultiTablePivotBrokerTypeData mpd,
            out bool unpivotedTableIsFirst)
        {
            QueryEngine qe2;
            Query       q2;
            QueryTable  qt2;
            QueryColumn qc2;
            MetaTable   mt2;

            string    firstMtName = mpd.FirstTableName;
            MetaTable firstMt     = MetaTableCollection.GetWithException(firstMtName);

            unpivotedTableIsFirst = UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(firstMtName);

            if (unpivotedTableIsFirst) // just use unpivoted table as is
            {
                SecondaryMetaBroker = this;
                Eqp2 = eqp;
            }

            else // all pivoted tables, create secondary query on the summarized unpivoted table
            {
                if (SecondaryMetaBroker == null)
                {
                    MultiDbAssayMetaBroker mb2 = SecondaryMetaBroker = new MultiDbAssayMetaBroker();

                    if (UnpivotedAssayResult.IsCombinedMdbAssayTable(firstMtName))
                    {
                        mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedTableName);
                    }
                    else
                    {
                        mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseTableName);
                    }

                    q2  = new Query();
                    qt2 = new QueryTable(mt2);

                    qc2 = qt2.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol);
                    if (mpd.TableCodeCsvList.Length != 0) // limit by codes
                    {
                        qc2.Criteria = qc2.CriteriaDisplay = MultiDbAssayDataNames.GeneSymbol + " in (" + mpd.TableCodeCsvList + ")";
                    }

                    q2.AddQueryTable(qt2);

                    qe2       = new QueryEngine();
                    qe2.Query = q2;
                    Eqp2      = new ExecuteQueryParms(qe2, qt2);
                    mb2.PrepareQuery(Eqp2);

                    mb2.Sql += " where " + qc2.Criteria;
                }

                Eqp2.SearchKeySubset = eqp.SearchKeySubset;
            }

            return;
        }
Example #5
0
        /// <summary>
        /// Prepare query
        /// </summary>
        /// <param name="parms"></param>

        public override string PrepareQuery(
            ExecuteQueryParms eqp)
        {
            List <MultiDbAssayMetaBroker> mbList;
            MultiTablePivotBrokerTypeData mpd;             // multipivot data for this broker type
            Dictionary <string, MultiTablePivotBrokerTypeData> mbsi;
            QueryColumn qc;
            MetaTable   mt;

            Eqp = eqp;
            Qt  = eqp.QueryTable;
            mt  = eqp.QueryTable.MetaTable;
            BuildActivityBinCondFormat();
            MdbAssayVoMap = UnpivotedAssayResultFieldPositionMap.NewMdbAssayMap(Qt);
            MdbAssayVoMap.InitializeForQueryTable(Qt);
            LoadTargetMap();

            if (                                                          // check for basic conditions that disallow multipivoting
                !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
                !UnpivotedAssayResult.IsSummarizedMdbAssayTable(mt.Name)) // summarized tables only
            {
                return(base.PrepareQuery(eqp));
            }

            int pivotedColCount = 0;

            foreach (QueryColumn qc1 in Qt.QueryColumns)
            {             // if any non-key criteria then pivot individually rather than via multipivot
                if (qc1.Criteria != "" && !qc1.IsKey)
                {
                    if (UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name) && Qt.Query.SingleStepExecution)
                    {
                    }                        // special case: allow criteria on unpivoted summary table which also apply to associated pivoted tables
                    else
                    {
                        return(base.PrepareQuery(eqp));
                    }
                }
                if (IsPivotedColumn(qc1.MetaColumn))
                {
                    pivotedColCount++;
                }
            }

            if (pivotedColCount == 0)
            {
                return(base.PrepareQuery(eqp));                                  // must have at least one column to pivot
            }
            // Store pivot info for queryTable

            PivotInCode = true;
            Sql         = BuildSql(eqp);

            if (eqp.Qe.MetaBrokerStateInfo == null)
            {
                eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>();
            }

            mbsi = eqp.Qe.MetaBrokerStateInfo;

            MpGroupKey = MetaBrokerType.TargetAssay.ToString();             // key for broker for query

            if (!QueryEngine.AllowMultiTablePivot)
            {
                MpGroupKey += "_" + Qt.MetaTable.Name;
            }

            mpd = MultiTablePivotBrokerTypeData.GetMultiPivotData(eqp.Qe.MetaBrokerStateInfo, MpGroupKey, mt.Name);

            string geneSymbol = mt.Code;

            if (!Lex.IsNullOrEmpty(geneSymbol) && !mpd.TableCodeDict.ContainsKey(geneSymbol))
            {
                mpd.TableCodeDict[geneSymbol] = new MpdResultTypeData();                 // add key to hash list
                if (mpd.TableCodeCsvList.Length > 0)
                {
                    mpd.TableCodeCsvList.Append(",");
                }

                mpd.TableCodeCsvList.Append(Lex.AddSingleQuotes(geneSymbol));
            }

            mpd.AddMetaBroker(mt.Name, this);

            return(Sql);
        }