public void GetRuntimeName_Strings_Pass() { var syntax = new MicrosoftQuerySyntaxHelper(); Assert.AreEqual(syntax.GetRuntimeName("[test]"), "test"); Assert.AreEqual(syntax.GetRuntimeName("`test`"), "`test`"); Assert.AreEqual(syntax.GetRuntimeName("`[test]`"), "`[test]`"); Assert.AreEqual(syntax.GetRuntimeName("[mydb].[test]"), "test"); Assert.AreEqual(syntax.GetRuntimeName("`mymysqldb`.`test`"), "`test`"); Assert.AreEqual(syntax.GetRuntimeName("[mydb]..[test]"), "test"); Assert.AreEqual(syntax.GetRuntimeName("[SERVER].[mydb]..[test]"), "test"); }
public string GetRuntimeName() { var helper = new MicrosoftQuerySyntaxHelper(); return(Alias ?? helper.GetRuntimeName(SelectSQL)); }
private string GetPivotPart1(List <CustomLine> lines, IQueryAxis axis, out IQuerySyntaxHelper syntaxHelper, out string pivotAlias, out string countAlias, out string axisColumnAlias) { syntaxHelper = new MicrosoftQuerySyntaxHelper(); //find the pivot column e.g. 'hb_extract AS Healthboard' var pivotSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Pivot); //the LHS e.g. hb_extract string pivotSqlWithoutAlias; //the RHS e.g. Healthboard syntaxHelper.SplitLineIntoSelectSQLAndAlias(pivotSelectLine.Text, out pivotSqlWithoutAlias, out pivotAlias); //ensure it has an RHS if (string.IsNullOrWhiteSpace(pivotAlias)) { pivotAlias = syntaxHelper.GetRuntimeName(pivotSqlWithoutAlias); } var countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction); string countSqlWithoutAlias; syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias); CustomLine axisColumn; string axisColumnWithoutAlias; CustomLine axisGroupBy; //if there is an axis we don't want to pivot on values that are outside that axis restriction. if (axis != null) { AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy); } else { axisColumnAlias = null; axisColumnWithoutAlias = null; } //Part 1 is where we get all the unique values from the pivot column (after applying the WHERE logic) bool anyFilters = lines.Any(l => l.LocationToInsert == QueryComponent.WHERE); string orderBy = countSqlWithoutAlias + " desc"; var topXOrderByLine = lines.SingleOrDefault(l => l.LocationToInsert == QueryComponent.OrderBy && l.Role == CustomLineRole.TopX); if (topXOrderByLine != null) { orderBy = topXOrderByLine.Text; } string havingSqlIfAny = string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.Having).Select(l => l.Text)); string part1 = string.Format( @" /*DYNAMICALLY FETCH COLUMN VALUES FOR USE IN PIVOT*/ DECLARE @Columns as VARCHAR(MAX) {0} /*Get distinct values of the PIVOT Column if you have columns with values T and F and Z this will produce [T],[F],[Z] and you will end up with a pivot against these values*/ set @Columns = ( {1} ',' + QUOTENAME({2}) as [text()] {3} {4} {5} ( {2} IS NOT NULL and {2} <> '' {7}) group by {2} {8} order by {6} FOR XML PATH(''), root('MyString'),type ).value('/MyString[1]','varchar(max)') set @Columns = SUBSTRING(@Columns,2,LEN(@Columns)) DECLARE @FinalSelectList as VARCHAR(MAX) SET @FinalSelectList = {9} --Split up that pesky string in tsql which has the column names up into array elements again DECLARE @value varchar(8000) DECLARE @pos INT DECLARE @len INT set @pos = 0 set @len = 0 WHILE CHARINDEX('],', @Columns +',', @pos+1)>0 BEGIN set @len = CHARINDEX('],[', @Columns +'],[', @pos+1) - @pos set @value = SUBSTRING(@Columns, @pos+1, @len) --We are constructing a version that turns: '[fish],[lama]' into 'ISNULL([fish],0) as [fish], ISNULL([lama],0) as [lama]' SET @FinalSelectList = @FinalSelectList + ', ISNULL(' + @value + ',0) as ' + @value set @pos = CHARINDEX('],[', @Columns +'],[', @pos+@len) +1 END if LEFT(@FinalSelectList,1) = ',' SET @FinalSelectList = RIGHT(@FinalSelectList,LEN(@FinalSelectList)-1) ", //select SQL and parameter declarations string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SELECT)), pivotSqlWithoutAlias, //FROM and JOINs that are not to the calendar table string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.FROM || l.LocationToInsert == QueryComponent.JoinInfoJoin && l.Role != CustomLineRole.Axis)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.WHERE)), anyFilters ? "AND" : "WHERE", orderBy, axisColumnWithoutAlias == null ? "": "AND " + axisColumnWithoutAlias + " is not null", havingSqlIfAny, axis != null ? "'joinDt'":"''" ); return(part1); }