Listing 2 A complex query using the CSQL class
Function FormatHourlyQuery(Yr as Long, Wk as Long, Dy as Long, Hr as Long) as string Dim mainQ as new CSQL Dim subQ as new CSQL ' First load the Sub Query to pick up the highest Calculation ID ' for this year, week, day hour subQ.Selct = "MAX(PC1.PRICE_CALCULATION_ID)" subQ.Table = "PRICE_CALCULATION PC1" subQ.Where = "PC1.YEAR = "& CStr(Yr) subQ.Where = "PC1.WEEK = " & Cstr(Wk) subQ.Where = "PC1.WEEKDAY = " & CStr(Dy) subQ.Where = "PC1.HOUR = " & CStr(Hr) ' extract the actual values against that calc ID mainQ.Selct = "convert(datetime,'1/1/2000') as OPR_DATE" mainQ.Selct = "-1 as ZONE_ID" mainQ.Selct = "'Unconstrained Market Clearing Price' AS ZONE_NAME" mainQ.Selct = "convert(varchar(10),'UMCP') AS ZONE_SHORT_NAME" mainQ.Selct = "PC.HOUR" mainQ.Selct = "convert(float,MCP.PRICE)/10000 as PRICE" mainQ.Selct = "convert(float,MCP.TOTAL_INCR_SUPPLY)/10 as SUP_INC" mainQ.Selct = "convert(float,MCP.TOTAL_DECR_SUPPLY)/10 as SUP_DEC" mainQ.Selct = "convert(float,MCP.TOTAL_INCR_DEMAND)/10 as DMD_INC" mainQ.Selct = "convert(float,MCP.TOTAL_DECR_DEMAND)/10 as DMD_DEC" mainQ.Selct = "PC.PRICE_CALCULATION_ID as SEQUENCE" mainQ.Selct = "PC.INSERT_TIME" mainQ.Selct = "PC.IS_VALID_MARKET" mainQ.Table = "MARKET_CLEARING_PRICE MCP" mainQ.Table = "PRICE_CALCULATION PC" ' After testing, it was determined that these conditions were not needed ' as the Calculation ID identifies a unique year, week, day, hour ' combination. ' mainQ.Where = "PC.YEAR = "& CStr(Yr) ' mainQ.Where = "PC.WEEK = " & Cstr(Wk) ' mainQ.Where = "PC.WEEKDAY = " & CStr(Dy) ' mainQ.Where = "PC.HOUR = " & CStr(Hr) mainQ.Where = "PC.PRICE_CALCULATION_ID = MCP.PRICE_CALCULATION_ID" mainQ.Where = "PC.PRICE_CALCULATION_ID = (" & subQ.SelectQuery & ")" FormatHourlyQuery = mainQ.SelectQuery End Function