[SOLVED] Query isn't build right
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2015-11-13 13:44

I am trying to make an sql-statement with the following code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i VAR cSQL:
String;  si1, si2: Integer;........    cSQL := 'SELECT ' +   
'Scheids_ID, ' +    'REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || '
+    '  COALESCE(Tussenvoegsel || " ", "") || ' +    '
 COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter ' +    'FROM
tbl_Scheidsrechters ';    si1 :=
MLookup('Wed_Scheids_1','tbl_Wedstrijden','Wed_ID='+wids);  si2 :=
MLookup('Wed_Scheids_2','tbl_Wedstrijden','Wed_ID='+wids);  if not(si1
in [1,2,3,4]) then    cSQL := cSQL + 'WHERE (Scheids_ID=' +
IntToStr(si1) + ') ';    if (not(si2 in [1,2,3,4]) AND not(si1 in
[1,2,3,4])) then    cSQL := cSQL + 'OR (Scheids_ID=' + IntToStr(si2) +
');'  else    cSQL := cSQL + 'WHERE (Scheids_ID=' + IntToStr(si2) +
');';It works well when both are not in [1..4] and when si1 is in
[1..4] and si2 in not in [1..4].
But it goes wrong when si2 is [1..4] then i get 'WHERE ..... WHERE'.


What am i (again) doing wrong?

----------------------------------------------------------------------
fpcoder · 2015-11-13 21:08

If si2 is in [1..4] then another WHERE is added on line 22. The first
WHERE was added on line 16.

----------------------------------------------------------------------
rvk · 2015-11-13 21:47

My guess is you want something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  wh:
string;...  wh := '';if not(si1 in [1,2,3,4]) then wh :=
'(Scheids_ID=' + IntToStr(si1) + ') ';if not(si2 in [1,2,3,4]) then
begin  if wh <> '' then wh := wh + ' OR ';  wh := wh + '(Scheids_ID='
+ IntToStr(si2) + ');'end;if wh <> '' then cSQL := cSQL + ' WHERE ' +
wh;

----------------------------------------------------------------------
madref · 2015-11-14 09:01

As usual you are a life saver Rick