sort order

[EPNICO]

Hello Everyone:

Here is my sql query, I'm MS SQL 7.0 accessing a sybase database. I need the result of my query to be by location_number but anytime I try to place the order by command SQL does not like it. Any ideas as to how I can get the resulting set of the union by location number.

Thanks.

DECLARE @clave int;
set @clave = "40003227";

SELECT
d.insured_loc_name,
d.insured_loc_street1,
d.insured_loc_street2,
d.insured_loc_city +', '+ d.insured_loc_state + ' '+ d.insured_loc_zip,
b.location_number,
c.insured_name

FROM wcps..wcps.policy_named_insured as a,
wcps..wcps.policy_insured_location as b,
wcps..wcps.insured_business as c,
wcps..wcps.insured_location as d


WHERE
( a.insured_business_id = d.insured_business_id ) and
( b.location_id = d.location_id ) and
( c.insured_business_id = a.insured_business_id ) and
( a.policy_id = @clave and b.policy_id = @clave )

union

select
null,
e.street_addr1,
e.street_addr2,
e.city + ', ' + e.state + ' '+ e.zip,
f.location_number,
e.insured_name


from wcps..wcps.insured_business as e,
wcps..wcps.policy_named_insured as f
where f.policy_id = @clave and f.insured_business_id = e.insured_business_id

 

[fluteplr]

You should be able to add,

order by location_number

after the last line in your example.

Is that what you were doing? If it was what was the EXACT error message.

 

[EPNICO]

Thanks for your suggestion, I tried it here is the error:
Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'f' does not match with a table name or alias name used in the query.

the f.location_number is valid.

 

[fluteplr]

Not after the union is done.

Remember the sort is done after the two tables are unioned.

Add the line as written above.

do NOT put in the f. prefix, if that does not work, post the code you are actually using since there is a discrepency here.

 

[EPNICO]

Thans for your reply. I got it to work by using b.location_number the results are sorted by location number.
here is the final query:

DECLARE @clave int;
set @clave = "40003227";

SELECT
d.insured_loc_name,
d.insured_loc_street1,
d.insured_loc_street2,
d.insured_loc_city +', '+ d.insured_loc_state + ' '+ d.insured_loc_zip,
b.location_number,
c.insured_name


FROM wcps..wcps.policy_named_insured as a,
wcps..wcps.policy_insured_location as b,
wcps..wcps.insured_business as c,
wcps..wcps.insured_location as d


WHERE
( a.insured_business_id = d.insured_business_id ) and
( b.location_id = d.location_id ) and
( c.insured_business_id = a.insured_business_id ) and
( a.policy_id = @clave and b.policy_id = @clave )


union

select
null,
e.street_addr1,
e.street_addr2,
e.city + ', ' + e.state + ' '+ e.zip,
f.location_number,
e.insured_name


from wcps..wcps.insured_business as e,
wcps..wcps.policy_named_insured as f
where f.policy_id = @clave and f.insured_business_id = e.insured_business_id
order by b.location_number


Thanks for your help.