Ah.  Dateadd() is TSQL function.  Place it in the stored procedure after
setting @Today, get rid of @FirstPart and @2ndPart and define @1YearAgo and
@Today as 'datetime' data types.  I assume the table columns
absenc.ab_dt_unft and absenc.ab_dt_res are datetime columns:

	SET @1YearAgo = dateadd(yy,-1,@Today)

Also, it looks like the references to PERS and TELE are the 'old style' JOIN
syntax, where the JOIN condition is contained in the WHERE clause.  There is
no accounting for taste, but I really don't think it's a good idea to mix
the two forms.  Since you've decided to use the 'new' style with the OUTER
JOIN (and good thing, as it was problems with the 'old style' outer join
syntax which precipitated the change to the 'new'), you really should be
consistent and continue with that same style with the INNER JOINs of PERS
and TELE, along the lines of:

SELECT emp_war_numb, emp_col_numb, per_sur_name, per_for_name, tel_division,
tel_rank
		FROM employ emp LEFT OUTER JOIN
			(SELECT ab_war_numb FROM absenc
        			WHERE  ab_dt_unft >= @1YearAgo OR ab_dt_res between
               		@1YearAgo and @Today) as ab
			ON emp.emp_war_numb = ab.ab_war_numb
			INNER JOIN PERS ON per_war_numb = emp_war_numb
			INNER JOIN TELE ON tel_collar = emp_col_numb
	WHERE ab.ab_war_numb IS NULL;

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jane.Hollowell@l...
[mailto:Jane.Hollowell@l...]
Sent: Tuesday, September 10, 2002 6:01 AM
To: sql language
Subject: [sql_language] RE: Combining two SQL Selects


Jeff,

Following is the query I'm using.
It works fine in query analyser but when I try to get it working in my asp
it falls over.

declare @FirstPart char(7)
declare @2ndPart char(1)
declare @1YearAgo varchar(8)
declare @Today varchar(8)

select @FirstPart = (left(convert(varchar, getdate(),1 ),7))
select @2ndPart = (right(convert(varchar, getdate(),1 ),1)) - 1
select @1YearAgo = @FirstPart + @2ndPart
select @Today = getdate()

SELECT emp_war_numb, emp_col_numb, per_sur_name, per_for_name, tel_division,
tel_rank
		FROM employ emp LEFT OUTER JOIN
			(SELECT ab_war_numb FROM absenc
        			WHERE  ab_dt_unft >= @1YearAgo OR ab_dt_res between
               		(@1YearAgo) and @Today) as ab
		ON emp.emp_war_numb = ab.ab_war_numb,
                PERS,
                TELE
	WHERE ab.ab_war_numb IS NULL
        AND per_war_numb = emp_war_numb
        AND tel_collar = emp_col_numb;

I used

SET OneYearAgo = dateadd(yy,-1,Date()) in the asp and got the follwowing
error.

Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'dateadd'

Thanks

-----Original Message-----
From: p=NET;a=CWMAIL;c=GB;dda:RFC-822=jeffm.ma.ultranet(a)rcn.com;
Sent: 09 September 2002 13:33
To: p=NET;a=CWMAIL;c=GB;dda:RFC-822=sql(u)language(a)p2p.wrox.com;
Subject: [sql_language] RE: Combining two SQL Selects


Post the query; you cannot use a "...SET statement in [a] SELECT..."
statement, so let's see what you tried to do.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jane.Hollowell@l...
[mailto:Jane.Hollowell@l...]
Sent: Monday, September 09, 2002 5:21 AM
To: sql language
Subject: [sql_language] RE: Combining two SQL Selects


Jeff,

Took the convert to smalldatetime out and it still worked.

However, when I tried to use the SET statement in the SELECT, the query
returned all rows instead of selecting as before. It almost seemed to ignore
this statement.

Any further guidance would be appreciated.

Thanks