Rita,

"Rita" <nospam@nospam> je napisal v sporoèilo
news:B6033880-B394-4C1F-95F3-68F290B55006@news.elevatesoft.com ...
> Iztok are u about ?
>
> Lots of errors but working up to ifthen line and I cant figure that
> bit out.
>
> procedure TForm1.Button4Click(Sender: TObject);
> var str:string;
> i:integer;
> begin
> str := '';
> for i := 1 to 6 do
>  str := str + ',' + quotedStr(TEdit(form1.findComponent('edit' +
> intToStr(i))).text);
>  delete(str,1,1);  // first comma is sufficient
> dbisamquery1.Active := false;
> dbisamquery1.Active := true;
> dbisamquery1.Prepare;
> dbisamquery1.SQL.Add('select date,b1,b2,b3,b4,b5,b5,b6,');
> for i := 1 to 6 do
> dbisamquery1.SQL.add('if (b' + intToStr(i) + ' in (' + str + ') then 1
> else 0) ') +
>
> ifThen(i < 6, '+',' as NumberOfWinners, ');      //FAILS HERE NO MATTER
> HOW I CHANGE IT ??????
>
> dbisamquery1.SQL.add('if(bb='01' then True else False) as BonusBall from
> lotto ' +
> group by date,b1,b2,b3,b4,b5,b6,NumberofWinners,BonusBall ' +
> having NumberofWinners > 2 ' +
> order by date ');
> end;
>

have you in your uses clause included math?

Regards
Iztok Lajovic

"Iztok Lajovic" <iztok.lajovic@amis.net> wrote in message
news:44D96F9F-681C-4EB9-9468-4A052E3F6B48@news.elevatesoft.com...
>
> have you in your uses clause included math?
>
Hi  Iztok yes I did after a couple of trys I figured that
then it just gives me

[Error] Unit1.pas(76): There is no overloaded version of 'IfThen' that can
be called with these arguments

So I checked the arguments and they look fine to me so I will keep trying
incase its a typo

Many Thanks
Rita

"Rita" <nospam@nospam> wrote in message
news:B6033880-B394-4C1F-95F3-68F290B55006@news.elevatesoft.com...
> Iztok are u about ?
>
> Lots of errors but working up to ifthen line and I cant figure that
> bit out.
>
> procedure TForm1.Button4Click(Sender: TObject);
> var str:string;
> i:integer;
> begin
> str := '';
> for i := 1 to 6 do
>  str := str + ',' + quotedStr(TEdit(form1.findComponent('edit' +
> intToStr(i))).text);
>  delete(str,1,1);  // first comma is sufficient
> dbisamquery1.Active := false;

**** the next two lines are unnecessary

> dbisamquery1.Active := true;
> dbisamquery1.Prepare;
> dbisamquery1.SQL.Add('select date,b1,b2,b3,b4,b5,b5,b6,');
> for i := 1 to 6 do
> dbisamquery1.SQL.add('if (b' + intToStr(i) + ' in (' + str + ') then 1
> else 0) ') +

I think your last ) is ending the SQL.ADD(..)

>
> ifThen(i < 6, '+',' as NumberOfWinners, ');      //FAILS HERE NO MATTER
> HOW I CHANGE IT ??????
>
> dbisamquery1.SQL.add('if(bb='01' then True else False) as BonusBall from
> lotto ' +
> group by date,b1,b2,b3,b4,b5,b6,NumberofWinners,BonusBall ' +
> having NumberofWinners > 2 ' +
> order by date ');
> end;
>

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:34B4430D-C936-4904-8F1E-150D1DC9CD62@news.elevatesoft.com...
>
> "Rita" <nospam@nospam> wrote in message
> news:B6033880-B394-4C1F-95F3-68F290B55006@news.elevatesoft.com...
>> Iztok are u about ?
>>
>> Lots of errors but working up to ifthen line and I cant figure that
>> bit out.
>>
>> procedure TForm1.Button4Click(Sender: TObject);
>> var str:string;
>> i:integer;
>> begin
>> str := '';
>> for i := 1 to 6 do
>>  str := str + ',' + quotedStr(TEdit(form1.findComponent('edit' +
>> intToStr(i))).text);
>>  delete(str,1,1);  // first comma is sufficient
>> dbisamquery1.Active := false;
>
> **** the next two lines are unnecessary
>
>> dbisamquery1.Active := true;
>> dbisamquery1.Prepare;

Above gone thanks.

>> dbisamquery1.SQL.add('if (b' + intToStr(i) + ' in (' + str + ') then 1
>> else 0 ') +

Fixed and moved closing bracket to right place thanks hawkeye.

> I think your last ) is ending the SQL.ADD(..) yes it was it didnt complain
> tho ?
>

>> ifThen(i < 6, '+',' as NumberOfWinners, '));     // Added closing bracket
>> to end of sql.add thanks Robert

But still getting the overload error grrrrrrrrrrr.
[Error] Unit1.pas(76): There is no overloaded version of 'IfThen' that can
be called with these arguments

>> dbisamquery1.SQL.add('if(bb='01' then True else False) as BonusBall from
>> lotto ' +
>> group by date,b1,b2,b3,b4,b5,b6,NumberofWinners,BonusBall ' +
>> having NumberofWinners > 2 ' +
>> order by date ');
>> end;
>>
>
>

Rita,

I found the mistake in my code. Please rearrange the piece of code in
following way:

::::
for i := 1 to 6 do
 dbisamquery1.SQL.add('if (b' + intToStr(i) + ' in (' + str + ') then 1
else 0) ');
disamquery1.SQL.add(' as NumberOfWinners, ');
if(bb='01' then True else False) as BonusBall from lotto
::::

Regards
Iztok Lajovic



"Rita" <nospam@nospam> je napisal v sporoèilo
news:76F7699D-E91C-4C5B-AD68-B96F77131DCB@news.elevatesoft.com ...
>
> "Iztok Lajovic" <iztok.lajovic@amis.net> wrote in message
> news:44D96F9F-681C-4EB9-9468-4A052E3F6B48@news.elevatesoft.com...
>>
>> have you in your uses clause included math?
>>
> Hi  Iztok yes I did after a couple of trys I figured that
> then it just gives me
>
> [Error] Unit1.pas(76): There is no overloaded version of 'IfThen' that can
> be called with these arguments
>
> So I checked the arguments and they look fine to me so I will keep trying
> incase its a typo
>
> Many Thanks
> Rita
>
>
>
>

I finally went this route using DBEDITS fired by a DBcombolist.
Its very fast and I only need the one SQL component. + You can add
new lines. Enjoy or hate I dont care but let me have opinions please.

procedure TForm1.Button5Click(Sender: TObject);
var
tF : TextFile;
sB1,sB2,sB3,sB4,sB5,sB6,s:String;
begin
Grid2.Visible := TRUE;
AssignFile(tF, 'Lottery.sql');
ReWrite(tF);

sB1 := EH1.text;         //3rd party dbedits
sB2 := EH2.text;
sB3 := EH3.text;
sB4 := EH4.text;
sB5 := EH5.text;
sB6 := EH6.text;

WriteLn(tF, 'select Day,Date,B1,B2,B3,B4,B5,B6,BBI,BB,');
WriteLn(tF,'if(B1 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)+  ');
WriteLn(tF,'if(B2 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)+  ');
WriteLn(tF,'if(B3 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)+  ');
WriteLn(tF,'if(B4 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)+  ');
WriteLn(tF,'if(B5 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)+  ');
WriteLn(tF,'if(B6 in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then 1 else 0)as You_Had, ');
WriteLn(tF,'if(BB='''+sB1+''' then True else False)  or (BB='''+sB2+''') or
(BB='''+sB3+''') or (BB='''+sB4+''') or (BB='''+sB5+''') or
(BB='''+sB6+''')as BBX from lotto ');
WriteLn(tF,'group By Date,B1,B2,B3,B4,B5,B6,You_Had,');
WriteLn(tF,'BBX having You_Had > 2');
WriteLn(tF,'order By Date descending ');

CloseFile(tF);
Reset(tF);
CloseFile(tF);
memo1.Visible := false;
SQ1.Active := false;
s := cb2.Text;
SQ1.SQL.LoadFromFile('Lottery.sql');
SQ1.Active := true;
labelB.Caption := s+' above has had 3 or more balls
'+inttostr(SQ1.RecordCount)+' '+'times since 1994.';
LabelC.Caption := 'If BBX is ticked you also had the bonus ball.';
Button12.enabled := true;          //print grid button
Grid2.SetFocus;

end;

Rita


Why are you using all the WriteLn statements for? Why not either use the queries SQL.Add or use a stringlist?

This

WriteLn(tF,'if(BB='''+sB1+''' then True else False)  or (BB='''+sB2+''') or
(BB='''+sB3+''') or (BB='''+sB4+''') or (BB='''+sB5+''') or
(BB='''+sB6+''')as BBX from lotto ');

could just be

WriteLn(tF,'if(BB in
('''+sB1+''','''+sB2+''','''+sB3+''','''+sB4+''','''+sB5+''','''+sB6+''')
then True else False)asfrom lotto '); - I think

I personally would rewrite the whole lot along the lines of

BallList := '(' + sB1 + ',' + sB2 + ',' + sB3 + ',' + sB4 + ',' + sB5 + ',' + sB6 + ')';
BallResult := BallList +' THEN 1 ELSE 0)'

SQ1.SQL.Clear;
SQ1.SQL.Add('SELECT Day,Date,B1,B2,B3,B4,B5,B6,BBI,BB,');
SQ1.SQL.Add('IF B1 IN ' + BallResult + ' + ');
SQ1.SQL.Add('IF B2 IN ' + BallResult + ' + ');
SQ1.SQL.Add('IF B3 IN ' + BallResult + ' + ');
SQ1.SQL.Add('IF B4 IN ' + BallResult + ' + ');
SQ1.SQL.Add('IF B5 IN ' + BallResult + ' + ');
SQ1.SQL.Add('IF B6 IN ' + BallResult AS You_Had,);
SQ1.SQL.Add('IF BB IN ' + BallList +' THEN TRUE ELSE FALSE) AS BBX');
SQ1.SQL.Add(FROM lotto ');
SQ1.SQL.Add(GROUP BY Date,B1,B2,B3,B4,B5,B6,You_Had, BBX');
SQ1.SQL.Add'(HAVING You_Had > 2');
SQ1.SQL.ORDER BY Date DESC');
SQ1.ExecSQL;

Having rewritten it like that I realise I have no idea if the query will actually work. I've never tried using HAVING as you are here so don't know if it works or not.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:EC4AC29B-1118-4938-A7B8-452BC66716D1@news.elevatesoft.com...
>
> Having rewritten it like that I realise I have no idea if the query will
> actually work. I've never tried using HAVING as you are here so don't know
> if it works or not.
>

I will try your way I was having problems doing it
that way in the begining and went for the sql text
file I admit i like it that way tho the text file that is.
The one for my own use has my numbers in code
but for other people it needed a generic approach.
If it aint broke dont fix it comes to mind here tho.
16 years of lotto results to parse out the numbers
on each line takes no time at all instant for my eyes
that is using the text file.

many thanks Rita

Rita


AFAICS the text file was just being used as a container for the sql. Its the SQ1.ExecSQL that will take up most of the time. Using the queries SQL property has the advantage that the text file isn't needed and hence you can't get into permissions problems.

Roy Lambert