convert sql from server syntax to absolute database

Question

This is the original code that worked with SQL Server using Unidac components : In the SQL (TStrings) of the uniQuery I had :

 use HIS
    DECLARE @StartDate  DATETIME,
            @EndDate    DATETIME,
            @RoomType   VARCHAR(6)
    ;

     SELECT @StartDate  = '2011-04-15',
            @EndDate    = '2011-04-26',
            @RoomType   = 'DBLMS'
    ;
    WITH 
    cteStayDates AS
    ( 
     SELECT RoomType  = Room_Type,
            StartDate = CASE WHEN Rate_Start_Date < @StartDate THEN @StartDate ELSE Rate_Start_Date END,
            EndDate   = CASE WHEN Rate_End_Date   > @EndDate   THEN @EndDate   ELSE Rate_End_Date   END,
            Rate
       FROM dbo.Room_Rates
      WHERE @RoomType  = Room_Type
        AND @StartDate < Rate_End_Date
        AND @EndDate  >= Rate_Start_Date
    ) 
     SELECT RoomType, StartDate, EndDate, Rate, 
            Days = DATEDIFF(dd,StartDate,EndDate)
                 + CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END
       FROM cteStayDates
      ORDER BY StartDate
    ;

On Button click I had :

procedure TForm1.Button1Click(Sender: TObject);
//uniQuery1.SQL.Add('SET DATEFORMAT DMY');
begin
uniQuery1.Close;
uniQuery1.SQL.Clear;
uniQuery1.SQL.Add('DECLARE @StartDate  DATETIME,');
uniQuery1.SQL.Add('@EndDate    DATETIME,');
uniQuery1.SQL.Add('@RoomType   VARCHAR(6);');
uniQuery1.SQL.Add('SELECT @StartDate  = :a2,');
uniQuery1.SQL.Add('@EndDate    = :a3,');
uniQuery1.SQL.Add('@RoomType   = :a1;');
uniQuery1.SQL.Add('WITH');
uniQuery1.SQL.Add('cteStayDates AS');
uniQuery1.SQL.Add('( SELECT RoomType  = Room_Type,');
uniQuery1.SQL.Add('StartDate = CASE WHEN Rate_Start_Date < @StartDate THEN @StartDate ELSE Rate_Start_Date END,');
uniQuery1.SQL.Add('EndDate   = CASE WHEN Rate_End_Date   > @EndDate   THEN @EndDate   ELSE Rate_End_Date   END,');
uniQuery1.SQL.Add('Rate');
uniQuery1.SQL.Add('FROM dbo.Room_Rates');
uniQuery1.SQL.Add('WHERE @RoomType  = Room_Type');
uniQuery1.SQL.Add('AND @StartDate < Rate_End_Date');
uniQuery1.SQL.Add('AND @EndDate  >= Rate_Start_Date)');
uniQuery1.SQL.Add('SELECT RoomType, StartDate, EndDate, Rate,');
uniQuery1.SQL.Add('Days = DATEDIFF(dd,StartDate,EndDate)');
//uniQuery1.SQL.Add('+ CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END');
uniQuery1.SQL.Add('FROM cteStayDates  ORDER BY StartDate;');
uniQuery1.Params.ParamByName('a1').AsString := cxTextEdit1.Text;
uniQuery1.Params.ParamByName('a3').AsDate := cxDateEdit2.Date; // end date
uniQuery1.Params.ParamByName('a2').AsDate := cxDateEdit1.Date; // start date
uniQuery1.Open;


end;

Now I am trying to port this code to absolute database (table fields are the same) but can not get it to work. Can someone help me with this ?

I also tried putting this query in the SQL text of the ABSQuery1:

SELECT RoomType, StartDate, EndDate, Rate, 
            Days = DATEDIFF(dd,StartDate,EndDate)
                 + CASE WHEN EndDate = EndDate THEN 0 ELSE 0 END
  RoomType  = Room_Type,
            StartDate = CASE WHEN Rate_Start_Date < StartDate THEN StartDate ELSE Rate_Start_Date END,
            EndDate   = CASE WHEN Rate_End_Date   > EndDate   THEN EndDate   ELSE Rate_End_Date   END,
            Rate
       FROM Room_Rates
      WHERE RoomType  = Room_Type
        AND StartDate < Rate_End_Date
        AND EndDate  >= Rate_Start_Date      ORDER BY StartDate
    ; 

But I can not retrieve fields so it does not wok. Really dont know why. Connection is set up properly. When I try and retrieve the fields I get :

Answer 1

Common Table Expressions (with cteStayDates as (..)) are a SQL Server feature that are probably not in absolute. You could try something like:

 use HIS
    DECLARE @StartDate  DATETIME,
            @EndDate    DATETIME,
            @RoomType   VARCHAR(6)
    ;

     SELECT @StartDate  = '2011-04-15',
            @EndDate    = '2011-04-26',
            @RoomType   = 'DBLMS'
    ;
     SELECT RoomType, StartDate, EndDate, Rate, 
            Days = DATEDIFF(dd,StartDate,EndDate)
                 + CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END
  RoomType  = Room_Type,
            StartDate = CASE WHEN Rate_Start_Date < @StartDate THEN @StartDate ELSE Rate_Start_Date END,
            EndDate   = CASE WHEN Rate_End_Date   > @EndDate   THEN @EndDate   ELSE Rate_End_Date   END,
            Rate
       FROM dbo.Room_Rates
      WHERE @RoomType  = Room_Type
        AND @StartDate < Rate_End_Date
        AND @EndDate  >= Rate_Start_Date      ORDER BY StartDate
    ;