Hi,

I have the following SQL but am not getting the desired results and was
hoping someone could help:

select
1 as typ,Staff_Id,  count(*) as bookTotal,  0 as cancelTotal,  0 as
fillTotal, 0 as notfillTotal
into memory OutTable
from "Booking.DAT" b
where ((b.book_Date between "2008-07-01" and "2008-07-31"))
group by staff_id
order by Staff_Id;

insert
into memory OutTable
 (typ,Staff_Id,  bookTotal ,  cancelTotal,  fillTotal , notfillTotal )
select 2 as typ,Staff_Id,  0 as bookTotal ,  count(*) as cancelTotal,  0 as
fillTotal , 0 as notfillTotal from "Booking.DAT" b
where ((b.book_Date between "2008-07-01" and "2008-07-31") and (b.cancel =
2))
group by staff_id
order by Staff_Id;

I first perform a select statement into a memroy table, and then I attempt
to insert records into the memory table grouping by staff_id.  The first
part works fine but the second part results in a cancelTotal of zero for all
records.  It shouldn't be zero and if I run the insert statement as a select
I definitely get totals.

I presume I am doing this SQL incorrectly (which is a highly likely case
with my limited SQL skills).

It is V3.30 DBISAM

Thanks

Alex

Alex,

<< I first perform a select statement into a memroy table, and then I
attempt to insert records into the memory table grouping by staff_id.  The
first part works fine but the second part results in a cancelTotal of zero
for all records.  It shouldn't be zero and if I run the insert statement as
a select I definitely get totals. >>

Are you sure that it isn't the first statement that is inserting rows with
zeros for the CancelTotal, etc. ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi,

I am still having problems with this statement and can not for the life of
me see why its giving incorrect results.

The first select statement always provides the correct result but any
subsequent insert statement does not seem to add any more records even
though as a separate select statement it would return results.  Is it a key
thing if staff_id is the same?

Alex

"Al Vas" <noreply@noreply.com> wrote in message
news:AB578C3C-F33A-4FFB-97C9-E691479ED6A9@news.elevatesoft.com...
> Hi,
>
> I have the following SQL but am not getting the desired results and was
> hoping someone could help:
>
> select
> 1 as typ,Staff_Id,  count(*) as bookTotal,  0 as cancelTotal,  0 as
> fillTotal, 0 as notfillTotal
> into memory OutTable
> from "Booking.DAT" b
> where ((b.book_Date between "2008-07-01" and "2008-07-31"))
> group by staff_id
> order by Staff_Id;
>
> insert
> into memory OutTable
>  (typ,Staff_Id,  bookTotal ,  cancelTotal,  fillTotal , notfillTotal )
> select 2 as typ,Staff_Id,  0 as bookTotal ,  count(*) as cancelTotal,  0
> as fillTotal , 0 as notfillTotal from "Booking.DAT" b
> where ((b.book_Date between "2008-07-01" and "2008-07-31") and (b.cancel =
> 2))
> group by staff_id
> order by Staff_Id;
>
> I first perform a select statement into a memroy table, and then I attempt
> to insert records into the memory table grouping by staff_id.  The first
> part works fine but the second part results in a cancelTotal of zero for
> all records.  It shouldn't be zero and if I run the insert statement as a
> select I definitely get totals.
>
> I presume I am doing this SQL incorrectly (which is a highly likely case
> with my limited SQL skills).
>
> It is V3.30 DBISAM
>
> Thanks
>
> Alex
>

Hmmm must be something to do with grouping because ungrouping so that I end
up with individual records and then doing a final select which groups by
staff_id works fine.  Go figure.

Alex

"Al Vas" <noreply@noreply.com> wrote in message
news:845D6DFA-6F98-4BF4-BBBC-512F7A4DB922@news.elevatesoft.com...
> Hi,
>
> I am still having problems with this statement and can not for the life of
> me see why its giving incorrect results.
>
> The first select statement always provides the correct result but any
> subsequent insert statement does not seem to add any more records even
> though as a separate select statement it would return results.  Is it a
> key thing if staff_id is the same?
>
> Alex
>
> "Al Vas" <noreply@noreply.com> wrote in message
> news:AB578C3C-F33A-4FFB-97C9-E691479ED6A9@news.elevatesoft.com...
>> Hi,
>>
>> I have the following SQL but am not getting the desired results and was
>> hoping someone could help:
>>
>> select
>> 1 as typ,Staff_Id,  count(*) as bookTotal,  0 as cancelTotal,  0 as
>> fillTotal, 0 as notfillTotal
>> into memory OutTable
>> from "Booking.DAT" b
>> where ((b.book_Date between "2008-07-01" and "2008-07-31"))
>> group by staff_id
>> order by Staff_Id;
>>
>> insert
>> into memory OutTable
>>  (typ,Staff_Id,  bookTotal ,  cancelTotal,  fillTotal , notfillTotal )
>> select 2 as typ,Staff_Id,  0 as bookTotal ,  count(*) as cancelTotal,  0
>> as fillTotal , 0 as notfillTotal from "Booking.DAT" b
>> where ((b.book_Date between "2008-07-01" and "2008-07-31") and (b.cancel
>> = 2))
>> group by staff_id
>> order by Staff_Id;
>>
>> I first perform a select statement into a memroy table, and then I
>> attempt to insert records into the memory table grouping by staff_id.
>> The first part works fine but the second part results in a cancelTotal of
>> zero for all records.  It shouldn't be zero and if I run the insert
>> statement as a select I definitely get totals.
>>
>> I presume I am doing this SQL incorrectly (which is a highly likely case
>> with my limited SQL skills).
>>
>> It is V3.30 DBISAM
>>
>> Thanks
>>
>> Alex
>>
>

Alex,

<< I am still having problems with this statement and can not for the life
of me see why its giving incorrect results. >>

Sorry about the delay in getting back to you on this.  Can you perform the
SQL in a different fashion, or does it have to be the INTO followed by the
INSERT ?  There's definitely a bug in 3.30 regarding this, but you can work
around it with this statement, for example:

select
1 as typ,Staff_Id,  count(*) as bookTotal,  0 as cancelTotal,  0 as
fillTotal, 0 as notfillTotal
into memory OutTable
from "Booking.DAT" b
where ((b.book_Date between "2008-07-01" and "2008-07-31"))
group by staff_id
union all
select 2 as typ,Staff_Id,  0 as bookTotal ,  count(*) as cancelTotal,  0 as
fillTotal , 0 as notfillTotal from "Booking.DAT" b
where ((b.book_Date between "2008-07-01" and "2008-07-31") and (b.cancel =
2))
group by staff_id;

--
Tim Young
Elevate Software
www.elevatesoft.com