Hi,

Not sure what I am doing wrong here but here are my statements:

select 2005 as yr, count(hiredate) as headcnt into memory memCalc from memory memStaff where ( ( terminatedate = null ) or ( terminatedate >= '2005-01-01' ) ) and ( hiredate <= '2005-12-31' );

insert into memory memCalc (yr, headcnt)
select 2006, count(hiredate) from memory memStaff where ( ( terminatedate = null ) or ( terminatedate >= '2006-01-01' ) ) and ( hiredate <= '2006-12-31' );;

The first statement creates the memory table fine with a single record.  the second I am trying to insert a record but the error I am receiving is:

Type mismatch between column headcnt and constant value in Insert statement.  

I am using DBISAM V3.3. Seemsstraight forward.  Am i missing something.  help appreciated.

Alex


What does memCalc do?

I assume the missing ; after insert into memory memCalc (yr, headcnt) is just a typo for the post

yr in statement 1 is an integer what does memCalc return it as?

V3 is a long time ago, I wasn't using SQL much back then and I'm not even sure the syntax is allowable.


Roy Lambert [Team Elevate]

Hi Alex

I changed your SQL to use one of my own DBISAM v3 tables that had two dates
and it executed OK:-
=============================================================
SELECT TransDate AS hiredate,
              DueDate AS terminatedate
INTO MEMORY memStaff
FROM SupplierTrans
;
select 2005 as yr, count(hiredate) as headcnt
into memory memCalc
from memory memStaff
where ( ( terminatedate = null ) or ( terminatedate >= '2005-01-01' ) )
  and ( hiredate <= '2005-12-31' )
;
insert into memory memCalc (yr, headcnt)
select 2006, count(hiredate)
from memory memStaff
where ( ( terminatedate = null ) or ( terminatedate >= '2006-01-01' ) )
  and ( hiredate <= '2006-12-31' )
;
SELECT * FROM MEMORY memCalc
=============================================================
.... BUT the two records created had null in the "yr" column, which I can't
account for.

Good Luck!

Cheers

Jeff



--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

<Alex Vastich> wrote in message
news:2B9186E0-9723-4EAE-8622-545CE543752E@news.elevatesoft.com...
> Hi,
>
> Not sure what I am doing wrong here but here are my statements:
>
> select 2005 as yr, count(hiredate) as headcnt into memory memCalc from
> memory memStaff where ( ( terminatedate = null ) or ( terminatedate >=
> '2005-01-01' ) ) and ( hiredate <= '2005-12-31' );
>
> insert into memory memCalc (yr, headcnt)
> select 2006, count(hiredate) from memory memStaff where ( ( terminatedate
> = null ) or ( terminatedate >= '2006-01-01' ) ) and ( hiredate <=
> '2006-12-31' );;
>
> The first statement creates the memory table fine with a single record.
> the second I am trying to insert a record but the error I am receiving is:
>
> Type mismatch between column headcnt and constant value in Insert
> statement.
>
> I am using DBISAM V3.3. Seemsstraight forward.  Am i missing something.
> help appreciated.
>

Wow thanks Jeff. Very weird then as I still get the error but can't see any genuine difference between your statement and mine.  This one really has me stumped.