OR expression in the Where Clause 2

[boligoma]

I have this query:

insert into Hours
(CnCveFolio, DxDescHorasRegistradas)
select cncvefolio, HorasRegistradas as DxDescHorasRegistradas
from temp_hexpediente where CdFechaRecepcion between '01/01/01' and '31/01/01' or (cncvefolio in (select cncvefolio from FechaCargaExpedientes))

and the Query Analizer returns the error 8623 saying that it has been an internal error because it can't create a consulting plan.

The select part returns me the values I want (If I exclude the insert into Clause). It also works if I eliminate the or part of at the end of the WHERE Clause. The three table exists (Hours, temp_hexpediente and FechaCargaExpedientes).

Any ideas???

thanks!

 

[cheyney]

I don't really know...try sticking parenthases around your whole where clause.

Like this:

insert into Hours
(CnCveFolio, DxDescHorasRegistradas)
select cncvefolio, HorasRegistradas as DxDescHorasRegistradas
from temp_hexpediente where (CdFechaRecepcion between '01/01/01' and '31/01/01' or (cncvefolio in (select cncvefolio from FechaCargaExpedientes)))

might work.

cheyney

 

[boligoma]

I tried it and it still gives me the same error.

I have other querys with the same where clause, the difference is that I have a AND Clause to it, for example:

where cncvefolio is not null and (CdFechaRecepcion between '01/01/01' and '31/01/01' or (cncvefolio in (select cncvefolio from FechaCargaExpedientes)))

I tried to put it into this query and it stills show me the same error.

 

[cheyney]

check this out, you need a service pack

http://support.microsoft.com/default.aspx?scid=kb;en-us;q325658

 

[boligoma]

The funny thing is that we installed the SP3 two days ago and before that the query was running perfect. I think that we have to reinstall SQL Server with SP3 and then check it again.

Thanks cheyney!!!!

 

[boligoma]

NO, I DON'T NEED TO REINSTAL SQL SERVER, not for that reason at least. The problem was due to a trace flag I put it yesterday. I you change the trace flag the problem is solved. For example:

dbcc traceon (126)
dbcc traceoff (126)

the query analizer returned me no errors. The complete information is in the page of the error 8623 that Cheyney post it above.

thanks again!!!!

 

[tlbroadbent]

Avoid the problem completely by not using the IN clause. The following modified query may run considerably faster than the original. In fact, this might return the correct results where the original would not due the date format.
[tt]
insert into Hours (CnCveFolio, DxDescHorasRegistradas)
select cncvefolio, HorasRegistradas
from temp_hexpediente
where CdFechaRecepcion between '2001-01-01' and '2001-01-31'
or Exists (Select * from FechaCargaExpedientes Where cncvefolio= t.cncvefolio)[/tt] If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm