Hi Bill!

You write "generated code", so I don't know whether my suggestion will
work or not.

In general, ORDER BY <position> is the only way to order a UNIONed
result set. If you cannot modify the query to not use UNION, then you
cannot ORDER BY <name>. However, this particular query may be
rewritten so that you do not need any union:

select distinct alert.alert_id, alert.primary_entity_name,
alert.primary_entity_level_code, alert_description, alert.run_date,
alert.create_date, alert.alert_risk_score, S.scenario_name,
alert.alert_status_code, alert.user_id, alert.actual_values_text,
S.SCENARIO_CATEGORY_DESC, alert.primary_entity_number,
alert.scenario_id, alert.primary_entity_key, alert.group_id,
usr.display_name
from fsk_alert alert
join fsk_group_user group_user
on group_user.group_id = alert.group_id
or alert.user_id = -2
join fsk_scenario S on alert.scenario_id = S.scenario_id
join fsk_user usr on alert.user_id = usr.user_id
where alert.logical_delete_ind = 'N'
and alert.alert_status_code = 'ACT'
and alert.user_id != 100
and group_user.user_id in (-2, 100)
order by alert.alert_risk_score desc

To understand your query, I also changed to SQL-92 and tried to
simplify a bit.

Check if I got the correct result or did an error somewhere in the
simplification process.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Bill Oliver" wrote:
> Hi all!
>
> I have this huge query (which works!)
>
> select distinct alert.alert_id, alert.primary_entity_name,
> alert.primary_entity_level_code,
> alert_description, alert.run_date, alert.create_date,
> alert.alert_risk_score, S.scenario_name, alert.alert_status_code,
> alert.user_id, alert.actual_values_text,
> S.SCENARIO_CATEGORY_DESC, alert.primary_entity_number,
> alert.scenario_id, alert.primary_entity_key,
> alert.group_id, usr.display_name
> from fsk_alert alert, fsk_group_user group_user, fsk_scenario S,
> fsk_user usr
> where (group_user.group_id = alert.group_id or group_user.user_id =
> alert.user_id)
> and group_user.user_id in (-2)
> and alert.user_id != 100
> and alert.logical_delete_ind = 'N'
> and alert.alert_status_code = 'ACT'
> and alert.scenario_id = S.scenario_id
> and alert.user_id = usr.user_id
> union select distinct alert.alert_id, alert.primary_entity_name,
> alert.primary_entity_level_code, alert.alert_description,
> alert.run_date, alert.create_date, alert.alert_risk_score,
> S.scenario_name, alert.alert_status_code,alert.user_id,
> alert.actual_values_text, S.SCENARIO_CATEGORY_DESC,
> alert.primary_entity_number, alert.scenario_id,
> alert.primary_entity_key, alert.group_id, usr.display_name from
> fsk_alert alert, fsk_group_user group_user, fsk_scenario S, fsk_user
> usr where group_user.group_id = alert.group_id and
> alert.logical_delete_ind = 'N'and alert.alert_status_code = 'ACT'
> and group_user.user_id != alert.user_id and alert.scenario_id =
> S.scenario_id and group_user.user_id = 100 and alert.user_id =
> usr.user_id
> order by 7 desc
>
>
> I would really like to use the column name in the ORDER BY clause,
> as in "ORDER BY alert_risk_score DESC" but no luck. I've tried
> FSK_ALERT.alert_risk_score, ALERT.alert_risk_score, and tried
> quoting the column name, but nothing seems to work.
>
> Normally this wouldn't be such a big deal, but this is generated
> code, and I won't be able to easily modify the query.
>
> -b