Syntax again

[CrystalStart]

Please help me one more time with syntax

select * from call_master where call_master.dertis in

(SELECT dertis FROM mss_dertis
WHERE LTRIM(RTRIM(prodcode)) in ('GFLI') AND LTRIM(RTRIM(compcode)) in ('6180') AND runreport = 'Y')
and date = '2005-02-12'
----------------------------
as yyz inner join mss_dertis on (mss_dnis.dertis = yyz.dertis and mss_dertis.compcode = yyz.compcode


I fixed top portion before the line but whole thing need to be executed and it's not workinmg
Incorrect syntax near the keyword 'as' (I've tried commas and all - no luck)

THANKS

 

[checkai]

select * 
from call_master 
where date = '2005-02-12' 
	AND
	call_master.dertis in 
	(SELECT dertis 
	FROM mss_dertis 
	WHERE LTRIM(RTRIM(prodcode)) = 'GFLI' AND LTRIM(RTRIM(compcode)) = '6180' AND runreport = 'Y')

I understand this...now why do you need the additonal join? what's your logic right now?

 

[CrystalStart]

It is a part of a huge SP and I am editing it accordingly to client's specs.

It is actually not starting with select * from call_master

It goes like this:

SELECT
*******
****
***** 300 lines
from

(
select
******
***
**** 300 lines

from call_master
where date = '2005-02-12'
AND
call_master.dertis in
(SELECT dertis
FROM mss_dertis
WHERE LTRIM(RTRIM(prodcode)) = 'GFLI' AND LTRIM(RTRIM(compcode)) = '6180' AND runreport = 'Y')
)as yyz inner join mss_dertis on (mss_dertis .dertis = yyz.dertis and mss_dertis .compcode = yyz.compcode)

So I was getting error at ')' before 'as'
Whatever I do it is not executing unless those lines removed
)as yyz inner join mss_dertis on (mss_dertis .dertis = yyz.dertis and mss_dertis .compcode = yyz.compcode)

 

[Pattycake245]

You have an extra closing bracket:

from call_master
where date = '2005-02-12'
AND
call_master.dertis in
(SELECT dertis
FROM mss_dertis
WHERE LTRIM(RTRIM(prodcode)) = 'GFLI' AND LTRIM(RTRIM(compcode)) = '6180' AND runreport = 'Y')
)as yyz inner

remove the bolded bracket