Esempio n. 1
0
        private void tsbAddDerivedTable_Click(object sender, EventArgs e)
        {
            using (new UpdateRegion(queryBuilder1.ActiveUnionSubQuery.FromClause))
            {
                var sqlContext = queryBuilder1.ActiveUnionSubQuery.SQLContext;

                var fq = new SQLFromQuery(sqlContext)
                {
                    Alias = new SQLAliasObjectAlias(sqlContext)
                    {
                        Alias = queryBuilder1.ActiveUnionSubQuery.QueryRoot.CreateUniqueSubQueryName()
                    },
                    SubQuery = new SQLSubSelectStatement(sqlContext)
                };

                var sqse = new SQLSubQuerySelectExpression(sqlContext);
                fq.SubQuery.Add(sqse);
                sqse.SelectItems = new SQLSelectItems(sqlContext);
                sqse.From        = new SQLFromClause(sqlContext);
                queryBuilder1.SQLQuery.AddObject(queryBuilder1.ActiveUnionSubQuery, fq, typeof(DataSourceQuery));
            }
        }
        public void AddDerivedTable()
        {
            using (new UpdateRegion(QueryView.ActiveUnionSubQuery.FromClause))
            {
                var sqlContext = QueryView.ActiveUnionSubQuery.SQLContext;

                var fq = new SQLFromQuery(sqlContext)
                {
                    Alias = new SQLAliasObjectAlias(sqlContext)
                    {
                        Alias = QueryView.ActiveUnionSubQuery.QueryRoot.CreateUniqueSubQueryName()
                    },
                    SubQuery = new SQLSubSelectStatement(sqlContext)
                };

                var sqse = new SQLSubQuerySelectExpression(sqlContext);
                fq.SubQuery.Add(sqse);
                sqse.SelectItems = new SQLSelectItems(sqlContext);
                sqse.From        = new SQLFromClause(sqlContext);

                NavigationBar.Query.AddObject(QueryView.ActiveUnionSubQuery, fq, typeof(DataSourceQuery));
            }
        }
Esempio n. 3
0
        private void LoadQueryWithDerivedTableAndCte()
        {
            UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();

            // Derived Table
            SQLFromQuery fq = new SQLFromQuery(_query.SQLContext)
            {
                Alias = new SQLAliasObjectAlias(_query.SQLContext)
                {
                    Alias = _query.QueryRoot.CreateUniqueSubQueryName()
                },
                SubQuery = new SQLSubSelectStatement(_query.SQLContext)
            };

            var sqse = new SQLSubQuerySelectExpression(_query.SQLContext);

            fq.SubQuery.Add(sqse);
            sqse.SelectItems = new SQLSelectItems(_query.SQLContext);
            sqse.From        = new SQLFromClause(_query.SQLContext);

            var dataSourceQuery = (DataSourceQuery)_query.AddObject(unionSubQuery, fq, typeof(DataSourceQuery));
            var usc             = dataSourceQuery.SubQuery.FirstSelect();
            var dsDerivedTable  = _query.AddObject(usc, "MyDB.MySchema.SalesOrderHeader");

            var ciDerivedTable1 = usc.QueryColumnList.AddField(dsDerivedTable, "OrderDate");

            ciDerivedTable1.Selected = true;

            var ciDerivedTable2 = usc.QueryColumnList.AddField(dsDerivedTable, "SalesOrderID");

            ciDerivedTable2.ConditionStrings[0] = "> 25";

            // CTE
            var qn = new SQLQualifiedName(_query.SQLContext);

            if (!unionSubQuery.QueryRoot.SQLContext.SyntaxProvider.IsSupportCTE())
            {
                return;
            }

            DataSource dataSourceCte;

            try
            {
                var withClauseItemName = _query.QueryRoot.CreateUniqueCTEName("CTE");

                qn.Add(withClauseItemName);

                var parentSubQuery = unionSubQuery.ParentSubQuery ?? unionSubQuery.QueryRoot;

                if (parentSubQuery.IsMainQuery)
                {
                    _query.QueryRoot.AddNewCTE(null, withClauseItemName);
                }
                else
                {
                    if (parentSubQuery.IsSubQueryCTE())
                    {
                        var index = parentSubQuery.GetSubQueryCTEIndex();
                        parentSubQuery.InsertNewCTE(index, null, withClauseItemName);
                    }
                    else
                    {
                        parentSubQuery.AddNewCTE(null, withClauseItemName);
                    }
                }

                if (_query.IsUniqueAlias(unionSubQuery, withClauseItemName))
                {
                    dataSourceCte = _query.AddObject(unionSubQuery, qn, null);
                }
                else
                {
                    var withClauseItemNameStr = withClauseItemName.GetSQL(new SQLGenerationOptions());
                    using (var alias = _query.CreateUniqueAlias(unionSubQuery, withClauseItemNameStr))
                        dataSourceCte = _query.AddObject(unionSubQuery, qn, alias);
                }
            }
            finally
            {
                qn.Dispose();
            }

            var cte = _query.QueryRoot.GetSubQueryCTEList().FirstOrDefault();

            if (cte == null)
            {
                return;
            }


            var unionSubQueryCte = cte.FirstSelect();
            var ds1 = _query.AddObject(unionSubQueryCte, "MyDB.MySchema.Customers");

            // create output column
            QueryColumnListItem ci1 = unionSubQueryCte.QueryColumnList.AddField(ds1, "CustomerName");

            ci1.Selected = true;

            // create output column
            QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddField(dataSourceCte, "CustomerName");

            ci2.Selected = true;
        }