[SQL SERVER 2005] Pb de syntaxe

**valoji** · 05/06/2009, 10h37

Bonjour,

je suis en train de migrer une requête qui fonctionnait sous informix en sqlserver 2005.
Voici ma requête d'origine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
select ufocod, ufolib, sum(decode((select count(*) from parli rli where rli.rlinumser = parli.rlinumser 
and rlidatdebval <= date(:par1)),0,decode((select count(*) from parli rli where rli.rlinumser = parli.rlinumser),
0,( - ufodatdebval + ufodatdebval + date(:par2) - date(:par1) + 1) ,
rlidatdebval - date(:par1)),0) + decode((select count(*) from parli rli where rli.rlinumser = parli.rlinumser 
and (rlidatfinval is null or rlidatfinval >= date(:par2))),0,decode((select count(*) 
from parli rli where rli.rlinumser = parli.rlinumser),0,0,date(:par2) - rlidatfinval + 1),0)) as NbJourInocc, 
SUM(date(:par2) - date(:par1) + 1) as NbJourTot from stlit, stufo, outer(parli) 
where stlit.ufonumser = stufo.ufonumser and parli.litnumser = stlit.litnumser 
and rlidatdebval <= date(:par2) and (rlidatfinval is null or rlidatfinval >= date(:par1)) 
and ufocod between :par3 and :par4 and litdis = 'O' GROUP BY 1,2 order by ufocod

Voici ma requête modifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select ufocod, ufolib, 
sum(
	 case (select count(*) from parli rli where rli.rlinumser = parli.rlinumser and rlidatdebval <= '01/01/2008')
	 when 0 then case(select count(*) from parli rli where rli.rlinumser = parli.rlinumser) 
				  when 0 then (- ufodatdebval + ufodatdebval + '31/12/2008' - '01/01/2008' + 1) 
     when (rlidatdebval - '01/01/2008') then 0
 
  + 
     case (select count(*) from parli rli where rli.rlinumser = parli.rlinumser and (rlidatfinval is null or rlidatfinval >= '31/12/2008'))
	 when 0 then case (select count(*) from parli rli where rli.rlinumser = parli.rlinumser) 
				  when 0 then 0
	 when ('31/12/2008' - rlidatfinval + 1) then 0
   ) NbJourInocc  , 
 
SUM('31/12/2008' - '01/01/2008' + 1)  NbJourTot 
 
from stufo join stlit 
				LEFT OUTER JOIN parli on stlit.litnumser = parlit.litnumser and rlidatdebval <= '31/12/2008' 
											and (rlidatfinval is null or rlidatfinval >= '01/01/2008') 
			on stufo.ufonumser = stlit.ufonumser 
where  ufocod between 0 
and 99999 and litdis = 'O' 
GROUP BY 1,2 
order by ufocod