Query conversion from Sybase to MS SQL Server 2000
 

Hello there, we are converting our applications from a sybase server over to MS SQL Server 2000 and have run across a query that is acceptable in sybase but not ms sql. It is giving us a table name correlation error that we can't figuire out. In sybase the sql is

SELECT s_form_control.box_no ,
s_form_control.box_label ,
s_form_control.box_prompt_seq ,
s_form_control.initial_display ,
s_form_control.tolerance ,
s_form_control.box_type ,
t_form_data_fish_hd.form_key ,
t_form_data_fish.form_alpha ,
t_form_data_fish.valid_alpha ,
t_form_data_fish.form_num ,
t_form_data_fish.valid_num ,
t_form_data_fish.valid_fg ,
t_form_data_fish.sched_form_key ,
s_form_control.report_cd ,
s_form_control.box_dec ,
s_form_control.trans_type ,
s_form_control.sched_form_id ,
s_form_control.sched_form_rev ,
s_form_control.sched_form_type ,
t_form_data_fish_hd.fsn ,
s_form_control.form_id ,
s_form_control.form_revised ,
' ',
' ',
' '

FROM s_form_control ,
t_form_data_fish_hd ,
t_form_data_fish

WHERE ( t_form_data_fish_hd.form_key *= t_form_data_fish.form_key) and ( s_form_control.box_no *= t_form_data_fish.box_no)
and ( s_form_control.form_id = t_form_data_fish_hd.form_id )
and ( s_form_control.form_revised = t_form_data_fish_hd.form_revised ) and ( t_form_data_fish_hd.form_key = :form_key )

ORDER BY s_form_control.box_prompt_seq ASC


This codes compiles and works against a sybase server, errors agaisnt ms sql with a name correlation error for the left outer joins. Any help would be appreciated.
Thanks,
Jared

Re: Query conversion from Sybase to MS SQL Server 2000
 

G'd evening Jared!
At a glance the problem might be in the WHERE sintax. What i left in blue is not valid in MS SQL:

WHERE ( t_form_data_fish_hd.form_key *= t_form_data_fish.form_key) and ( s_form_control.box_no *= t_form_data_fish.box_no)
and ( s_form_control.form_id = t_form_data_fish_hd.form_id )
and ( s_form_control.form_revised = t_form_data_fish_hd.form_revised ) and ( t_form_data_fish_hd.form_key = :form_key )

Run your query without the where clause and test it.

Estuardo