Re: insert only if conditions are met?

Henry Ortega wrote:
> Ok. Here's TABLE A
>
> emp date hours type
> JSMITH 08-15-2005 5 WORK
> JSMITH 08-15-2005 3 WORK
> JSMITH 08-25-2005 6 WORK
>
> I want to insert the ff:
> 1.) JSMITH 08-15-2005 8 VAC
> 2.) DOE 08-16-2005 8 VAC
>
> #1 should fail because there is already 8 hours entered as being
> Worked on 08-15-2005 (same date).
>
> Any suggestions?

CREATE TABLE "tablea" (
"emp" varchar(6) NOT NULL,
"date" varchar(10) NOT NULL,
"hours" int NOT NULL,
"type" char(4) NOT NULL
);

grant select, insert, update, delete on tablea to public;

insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-25-2005', 6, 'WORK' );

create or replace function overtime( varchar, varchar, int, varchar )
returns void as '
insert into tablea( emp, date, hours, type )
select $1, $2, $3, $4
from tablea where ( select sum( hours ) from tablea where emp =
$1 and date = $2 group by emp, date ) + $3 <= 8
union
select $1, $2, $3, $4
from tablea where( select sum( hours ) from tablea where emp = $1
and date = $2 group by emp, date ) is null
' LANGUAGE SQL;

select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' ); # REJECTED
select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' ); # OK
select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' ); # OK

select * from tablea;

>
>
>
>
> On 8/31/05, Ragnar Hafstað < gnari(at)simnet(dot)is <mailto:gnari(at)simnet(dot)is> >
> wrote:
>
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
>
>>What I am trying to do is
>>* Insert a record for EMPLOYEE A to TABLE A
>>IF
>>the sum of the hours worked by EMPLOYEE A on TABLE A
>>is not equal to N
>>
>>Is this possible?
>
>
> Sure, given a suitable schema
>
> It is not clear to me, if the hours worked are
> to be found in the same table you want to insert
> into, or not.
>
> gnari
>
>
>
>
>
>
>

--
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202

(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))