Convert SQL server query to Delphi syntax

Question

This is the original SQL server query which works:

use HIS

SELECT
room_type,rate_start_date,rate_end_date,rate
, DATEDIFF(DAY,case when rate_end_date < '2011.08.21'
then '2011.08.19'
else rate_start_date
end,
case when rate_start_date > '2011.08.19'
then '2011.08.21'
else rate_end_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011.08.21'
AND rate_end_date > '2011.08.19'

I converted it to Delphi SQL :

procedure TForm1.Button1Click(Sender: TObject);
begin
uniQuery1.Close;
uniQuery1.SQL.Clear;
uniQuery1.SQL.Add('SELECT room_type,rate_start_date,rate_end_date,rate,');
uniQuery1.SQL.Add('DATEDIFF(DAY,case when rate_end_date < 2011.08.21 then 2011.08.19 else rate_start_date end,');
uniQuery1.SQL.Add('case when rate_start_date > 2011.08.19');
uniQuery1.SQL.Add('then 2011.08.21 else rate_end_date end) AS days FROM room_rates');
uniQuery1.SQL.Add('WHERE room_type = DBLMS AND rate_start_date <= 2011.08.21');
uniQuery1.SQL.Add('AND rate_end_date > 2011.08.19');
uniQuery1.Open;
end;

However I am getting 'Invalid column NAME 'DBLMS''

What am I missing here ? DBLMS is not a column.

RRUZ · Accepted Answer · 2011-06-25 07:24:00Z

10

You are no quoting the strings inside of your sql sentence try rewriting your code to this :

procedure TForm1.Button1Click(Sender: TObject);
begin
  uniQuery1.Close;
  uniQuery1.SQL.Clear;
  uniQuery1.SQL.Add('SELECT room_type,rate_start_date,rate_end_date,rate,');
  uniQuery1.SQL.Add('DATEDIFF(DAY,case when rate_end_date < ''2011.08.21'' then ''2011.08.19'' else rate_start_date end,');
  uniQuery1.SQL.Add('case when rate_start_date > ''2011.08.19''');
  uniQuery1.SQL.Add('then ''2011.08.21'' else rate_end_date end) AS days FROM room_rates');
  uniQuery1.SQL.Add('WHERE room_type = ''DBLMS'' AND rate_start_date <= ''2011.08.21''');
  uniQuery1.SQL.Add('AND rate_end_date > ''2011.08.19''');
  uniQuery1.Open;
end;

As addtional advice , try using parameters instead of string literals, in this way you will protect your code against sql injection beside others advantages. check this article Using Parameters in Queries

edited Jun 25, 2011 at 7:24

answered Jun 25, 2011 at 7:18

RRUZ

137k20 gold badges365 silver badges489 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user763539 Over a year ago

I used your suggestion and replaced dates,room_types with parameters but still DAYS do not show.Interesting...SQL server displays days without problem... Why is this ?

user763539 Over a year ago

it seems that TMS grid as well as the cxGrid do not want to show the 'days' field while the one from Delphi 8standard built in grid) does. Which is strange indeed....

Collectives™ on Stack Overflow

Convert SQL server query to Delphi syntax

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related