How can I convert that nice piece of SQL  to dynamic
sql using variables like Edit1.Text through to Edit6.Text
to replace the '01' through to '06' in the query below ?

select date,b1,b2,b3,b4,b5,b5,b6,
if(b1 in ('01','02','03','04','05','06') then 1 else 0)+
if(b2 in ('01','02','03','04','05','06') then 1 else 0)+
if(b3 in ('01','02','03','04','05','06') then 1 else 0)+
if(b4 in ('01','02','03','04','05','06') then 1 else 0)+
if(b5 in ('01','02','03','04','05','06') then 1 else 0)+
if(b6 in ('01','02','03','04','05','06') then 1 else 0) as NumberofWinners,
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

Rita

Rita

>How can I convert that nice piece of SQL to dynamic
>sql using variables like Edit1.Text through to Edit6.Text
>to replace the '01' through to '06' in the query below ?
>
>select date,b1,b2,b3,b4,b5,b5,b6,
>if(b1 in ('01','02','03','04','05','06') then 1 else 0)+
>if(b2 in ('01','02','03','04','05','06') then 1 else 0)+
>if(b3 in ('01','02','03','04','05','06') then 1 else 0)+
>if(b4 in ('01','02','03','04','05','06') then 1 else 0)+
>if(b5 in ('01','02','03','04','05','06') then 1 else 0)+
>if(b6 in ('01','02','03','04','05','06') then 1 else 0) as NumberofWinners,
>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

You can't substitute the values in the IN test using parameters so you have two options:

1. forget parameters and just use StringReplace to create the sql

2. forget the IN and go to OR eg

if(b1 = :Ball1 OR b1 = :Ball2 OR b1 = :Ball3 OR b1 = :Ball4 OR b1 = :Ball5 OR b1 = :Ball6 then 1 else 0) +
........

Roy Lambert [Team Elevate]

Dear Roy,


> 2. forget the IN and go to OR eg
>
> if(b1 = :Ball1 OR b1 = :Ball2 OR b1 = :Ball3 OR b1 = :Ball4 OR b1 = :Ball5 OR b1 = :Ball6 then 1 else 0) +
> .......
>
> Roy Lambert [Team Elevate]

Will this slow down the query ? (replacing IN with OR)

--
Charalampos Michael - [Creation Power] - http://www.creationpower.gr

Charalampos


Way back I asked this very question and the answer back then was that multiple ORs were faster. I have no idea what the situation is now.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:6FE2F93B-E877-4B96-B366-FE8C0CBAC370@news.elevatesoft.com...
>
> if(b1 = :Ball1 OR b1 = :Ball2 OR b1 = :Ball3 OR b1 = :Ball4 OR b1 = :Ball5
> OR b1 = :Ball6 then 1 else 0) +
> .......

Thanks Roy scratchin my bum on this I will let u know how I get on.
1st the shops Tesco will not deliver just bread and milk ;-(
Rita

Rita

>1st the shops Tesco will not deliver just bread and milk ;-(

I'm not surprised - prisoners should only get bread and water....

Roy Lambert

Rita,

"Rita" <nospam@nospam> je napisal v sporoèilo
news:F83BF6A0-5C10-4AC5-9630-4A22209BDD92@news.elevatesoft.com ...
> How can I convert that nice piece of SQL  to dynamic
> sql using variables like Edit1.Text through to Edit6.Text
> to replace the '01' through to '06' in the query below ?
>
> select date,b1,b2,b3,b4,b5,b5,b6,
> if(b1 in ('01','02','03','04','05','06') then 1 else 0)+
> if(b2 in ('01','02','03','04','05','06') then 1 else 0)+
> if(b3 in ('01','02','03','04','05','06') then 1 else 0)+
> if(b4 in ('01','02','03','04','05','06') then 1 else 0)+
> if(b5 in ('01','02','03','04','05','06') then 1 else 0)+
> if(b6 in ('01','02','03','04','05','06') then 1 else 0) as
> NumberofWinners,
> 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
>
> Rita

did you think about creating SQL statement by code like this example
(untested and made from head):

str := '':
for i := 1 to 6 do
 str := str + ',' + qoutedStr(TEdit(form.findComponent('edit' +
intToStr(i))).text);
delete(str,1,1);  // first comma is sufficient
query.clear;
query.add('select date,b1,b2,b3,b4,b5,b5,b6,');
for i := 1 to 6 do
 query.add('if (b' + intToStr(i) + ' in (' + str + ') then 1 else 0) ') +
ifThen(i < 6, '+',' as NumberOfWinners, ');
query.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 ');

HTH

Iztok Lajovic

"Iztok Lajovic" <iztok.lajovic@amis.net> wrote in message
news:C24A4CB7-94D3-449C-9B32-6746BCAFA8A1@news.elevatesoft.com...
>
> did you think about creating SQL statement by code like this example
> (untested and made from head):
>

I love this group 1st John Hay gives me a headsup then
Roy points me to another route, then u Iztok show me
another even more exciting route. Any SQL I have ever
done has always been in the TQuery or TDBisamQuery,
I have never really needed it but now I'am thinking I could
have done this that or whatever much better with SQL.
Thanks for that lovely piece of code untested and made
from your head, I love whats in your head just write it down
and post it here
Rita xxx

Roy,

<< Way back I asked this very question and the answer back then was that
multiple ORs were faster. I have no idea what the situation is now. >>

IN is faster, but not by much.  However, you can't use parameters in a
SELECT expression, only in the JOIN or WHERE clauses.

--
Tim Young
Elevate Software
www.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;