Hello Svein!

The query you supplied looks good to me! That is great news, and
thanks!

I have provided this information to the applications' author, and he
is reviewing it now.

Thanks again for the help!

-b
--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> 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