How to convert a SQL query into hibernate criteria?

Is there a way to use a While within a SQL Union query?

• I have the following SQL query. I need to have a WHILE loop in the second half. I can't break the query into two queries, because the queries are in a SSRS report, and need to be in the same table. I get an error where the WHILE is. Is there any way around this? select DATEName(month, DateAdd(d,1,trendsDate)) AS [TrendMonth],DATEPART(wk, DateAdd(d,1,trendsDate)) AS [TrendWeek], DATEName(weekday, DateAdd(d,1,trendsDate)) AS [TrendWeekDay], DateAdd(d,1,trendsDate) as [TrendsDate], 0 as [TrendsOccurrences], 'Weekend' as [trendsField], 'Weekend' as [reportCategory], 'Weekend' as [Team Name], 'Weekend' as [WLName], 0 as [repseq], DATEPART(weekday, DateAdd(d,1,trendsDate)) AS [TrendWeekDayNum], DATEPART(month, DateAdd(d,1,trendsDate)) AS [TrendMonthNum], DATEPART(year, DateAdd(d,1,trendsDate)) AS [TrendYear], 'Weekend' as [Metric], -1 as [calccount], 1 as [metricseq], 1 as [FieldCount], ''as [WLCode] from trends where trendsDate >= @ReportStartDt and trendsDate <> convert(varchar, GETDATE(), 101) and datename(weekday,trendsDate) = 'Friday' group by TrendsDate, trendsField UNION WHILE (@incdate <= @endDate) BEGIN select DATEName(month, @incDate) AS [TrendMonth], DATEPART(wk, @incDate) AS [TrendWeek], DATEName(weekday, @incDate) AS [TrendWeekDay], @incDate as [TrendsDate], 1 as [TrendsOccurrences], clientTableDesc as [TrendsField], clientTableInfo as [reportCategory], clientTableInfo2 as [Team Name], clientTableDesc as [WLName], clientTableInfo3 as [repseq], DATEPART(weekday, @incDate) AS [TrendWeekDayNum], DATEPART(month, @incDate) AS [TrendMonthNum], DATEPART(year, @incDate) AS [TrendYear], 'Open' as [Metric], -1 as [calccount], 1 as [metricseq], count (distinct workListCode) as [FieldCount], workListCode as [WLCode] from workListRecs, clientSelfDescInfo where workListCode = clientTableCode and clientSelfDescInfo.clientTableType = 'DR' and @incDate NOT IN (select convert(datetime, virtualTableCode) from virtualSelfDescInfo where virtualTableType = 'HD') and clientTableInfo <> 'Draft' and clientTableInfo <> 'Prep/Draft' and workListCode <> 'PBI' and workListCode <> 'PPN' and clientTableInfo <> 'Claim Follow-Up Calls' and (@calculation = 'Average') and workListCode in (select clientTableCode from clientSelfDescInfo where clientTableType = 'OM' and SUBSTRING(clientTableInfo, convert(int,@rpReportType), 1) = 'Y') set @incdate = DATEADD(dd, 1, @incdate) END

• Answer:

  Look at Recursive CTE (Common Table Expressions), which is supported from SQL Server 2005.