Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread query a query?
Sat, Jun 9 2007 7:38 AMPermanent Link

"Harry de Boer"
Ls.

I fetch a lot of records from a table (vh) where year (jaar) is in the
current year (see query below). The returned recordset shows amongst other
fields the months (maand). In a list I want to show only the distinct values
(the months) froim the returned resultset. I can iterate through the records
and fill the list but if I could query out the distinct values I guess it
would be faster. Is this possible and how? Or maybe an other solution?

Regards, Harry

dbIsam 3.3 (D6)

sql.text :=
'select * from vh where (jaar*100+maand >= EXTRACT(YEAR FROM
CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE))' +
             ' AND (i1 =TRUE OR i2 = TRUE OR i3 = TRUE OR i4 = TRUE OR i5 =
TRUE' +
             ' OR i6 = TRUE OR i7 = TRUE OR i8 = TRUE  OR i9 = TRUE OR i10
= TRUE' +
             ' OR i11 = TRUE OR i12 = TRUE OR i13 = TRUE OR i14 = TRUE OR
i15 = TRUE' +
             ' OR i16 = TRUE OR i17 = TRUE OR i18 = TRUE OR i19 = TRUE OR
i20 = TRUE' +
             ' OR i21 = TRUE OR i22 = TRUE OR i23 = TRUE OR i24 = TRUE OR
i25 = TRUE' +
             ' OR i26 = TRUE OR i27 = TRUE OR i28 = TRUE OR i29 = TRUE OR
i30 = TRUE' +
             ' OR i31 = TRUE)' +
             ' AND ( p1 <> 0 and p1 <> null OR p2 <> 0 and p2 <> null OR p3
<> 0 and p3 <> null' +
             ' OR p4 <> 0 and p4 <> null OR p5 <> 0 and p5 <> null OR p6 <>
0 and p6 <> null'+
             ' OR p7 <> 0 and p7 <> null OR p8 <> 0 and p8 <> null OR p9 <>
0 and p9 <> null'+
             ' OR p10 <> 0 and p10 <> null OR p11 <> 0 and p11 <> null OR
p12 <> 0 and p12 <> null'+
             ' OR p13 <> 0 and p13 <> null OR p14 <> 0 and p14 <> null OR
p15 <> 0 and p15 <> null'+
             ' OR p16 <> 0 and p16 <> null OR p17 <> 0 and p17 <> null OR
p18 <> 0 and p18 <> null'+
             ' OR p19 <> 0 and p19 <> null OR p20 <> 0 and p20 <> null OR
p21 <> 0 and p21 <> null'+
             ' OR p22 <> 0 and p22 <> null OR p23 <> 0 and p23 <> null OR
p24 <> 0 and p24 <> null'+
             ' OR p25 <> 0 and p25 <> null OR p26 <> 0 and p26 <> null OR
p27 <> 0 and p27 <> null'+
             ' OR p28 <> 0 and p28 <> null OR p29 <> 0 and p29 <> null OR
p30 <> 0 and p30 <> null'+
             ' OR p31 <> 0 and p31 <> null)';

Sun, Jun 10 2007 3:40 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Harry


Can't remember what V3 had in the way of memory tables but you could try a SELECT INTO and then you can query the result set to your heart's content. The only other suggestion is a separate query which returns the distinct values.

Roy Lambert
Sun, Jun 10 2007 2:44 PMPermanent Link

"Herbert Sitz"
"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:2E2210CA-1F01-4761-B804-1714314E3326@news.elevatesoft.com...
> Harry
>
>
> Can't remember what V3 had in the way of memory tables but you could try a
SELECT INTO and then you can query the result set to your heart's content.
The only other suggestion is a separate query which returns the distinct
values.
>
> Roy Lambert
>

Aren't views another option?  Create a view for the base query, then do a
SELECT DISTINCT on the view.  I have no idea how well elevatedb would
optimize it, but it's probably most elegant method.

-- Herb Sitz

Sun, Jun 10 2007 2:45 PMPermanent Link

"Herbert Sitz"
"Herbert Sitz" <hsitz@nwlink.com> wrote in message
news:D63B2107-046E-44F7-B570-EBF039A426D6@news.elevatesoft.com...
> "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
> news:2E2210CA-1F01-4761-B804-1714314E3326@news.elevatesoft.com...
> > Harry
> >
> >
> > Can't remember what V3 had in the way of memory tables but you could try
a
> SELECT INTO and then you can query the result set to your heart's content.
> The only other suggestion is a separate query which returns the distinct
> values.
> >
> > Roy Lambert
> >
>
> Aren't views another option?  Create a view for the base query, then do a
> SELECT DISTINCT on the view.  I have no idea how well elevatedb would
> optimize it, but it's probably most elegant method.
>
> -- Herb Sitz
>

Whoops, didn't notice Harry was still using DBISAM v3.

Mon, Jun 11 2007 3:10 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Herbert


get with it - just look at the newsgroup Smiley

Roy Lambert
Mon, Jun 11 2007 4:20 AMPermanent Link

"Harry de Boer"
Herb,

... we're usng EDB too, so ...thanks for the answers Smile

Harry


"Herbert Sitz" <hsitz@nwlink.com> schreef in bericht
news:F4F528C7-3387-4248-816A-16F6F1F85CE3@news.elevatesoft.com...
> "Herbert Sitz" <hsitz@nwlink.com> wrote in message
> news:D63B2107-046E-44F7-B570-EBF039A426D6@news.elevatesoft.com...
> > "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
> > news:2E2210CA-1F01-4761-B804-1714314E3326@news.elevatesoft.com...
> > > Harry
> > >
> > >
> > > Can't remember what V3 had in the way of memory tables but you could
try
> a
> > SELECT INTO and then you can query the result set to your heart's
content.
> > The only other suggestion is a separate query which returns the distinct
> > values.
> > >
> > > Roy Lambert
> > >
> >
> > Aren't views another option?  Create a view for the base query, then do
a
> > SELECT DISTINCT on the view.  I have no idea how well elevatedb would
> > optimize it, but it's probably most elegant method.
> >
> > -- Herb Sitz
> >
>
> Whoops, didn't notice Harry was still using DBISAM v3.
>
>

Image