Discussion :

[Jean.Cri1]

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
  create table #Filtered (
	ctrl_id int not null,
	cprt_idi int not null,
	dt_arrete datetime null,
	idi int not null 
)
go
 
insert into #Filtered (ctrl_id, cprt_idi, dt_arrete, idi) 
select ctrl_id, cprt_idi, dat, idi  
from ( 
	select  6753 as ctrl_id, cprt.cprt_idi, s.pos_dat as dat, d.pos_idi as idi 
	from 	pos as s, 
		pos_dyn as d, 
		(select 3311001 as cprt_idi) as cprt, 
		(select 1876 as desc_car_id, 6753 as ctrl_id) as f 
	where s.cprt_idi = cprt.cprt_idi 
	and s.pos_idi = d.pos_idi 
	and s.impl_cd = d.impl_cd 
	and s.impl_cd = "FR" 
	and d.impl_cd = "FR" 
	and f.ctrl_id = 6753 
	and f.desc_car_id = d.desc_car_id 
	and f.desc_car_id = 1876 
union all
	select  6753 as ctrl_id, cprt.cprt_idi, s.pos_dat as dat, d.pos_idi as idi 
	from 	pos as s, 
		pos_dyn as d, 
		(select 3311001 as cprt_idi) as cprt, 
		(select 1006 as desc_car_id, 6753 as ctrl_id) as f 
	where s.cprt_idi = cprt.cprt_idi 
	and s.pos_idi = d.pos_idi 
	and s.impl_cd = d.impl_cd 
	and s.impl_cd = "FR" 
	and d.impl_cd = "FR" 
	and f.ctrl_id = 6753 
	and f.desc_car_id = d.desc_car_id 
	and f.desc_car_id = 1006 
	and convert(int, d.value) IN (
		select elmt_grp_id 
		from 	elmt_grp_jr as j, 
			grp as g 
		where j.grp_id  = g.grp_id 
		and g.impl_cd = "FR" 
		and j.impl_cd = "FR" 
		and g.impl_cd = j.impl_cd 
		and g.tab_idi = 7 
		and g.grp_id = convert(int, 33345) 
		group by elmt_grp_id)
) as SR 
group by ctrl_id, cprt_idi, dat, idi 
having count(*) = 2

Le select tout seul marche ( sans le select into)
par contre avec tout :
Server Message: Number 107, Severity 15
Server 'VAS4_PAR_TST_SQL', Line 35:
The column prefix 'j' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
...

Pourquoi ?

[Fabien Celaia]

Juste une question au préalable : pourquoi un create table + insert into plutôt qu'un select into ?

[mpeppler]

J'ai eu des problèmes similaires avec des tables dérivées dans des SELECT INTO et/ou des UNION (sauf que j'avais plutôt des problèmes de noms de colonnes ambigues).

Je pense qu'il faut peut-être essayer de jouer sur l'utilisation des tables dérivées (et peut-être en enlevant l'UNION) pour voir si tu peux faire changer le comportement.

Voir éventuellement ouvrir un call avec Sybase...

Michael