Frank Langelage said:
>
> The JBossAS tries to execute this query and gets a syntax error:
> SELECT MAX(TXID) FROM (
> SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS
> UNION
> SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES
> )
>
> The goal is to find the maximum txid out of two tables with one query.
> I know I may create a view for the union select and select the max from
> this view.
> But I'd like to avoid this and use one query against the tables on my
> IDS 10 server.

Do you want both maxima, or just the bigger of the two?

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com
sending to informix-list

"Obnoxio The Clown" <obnoxio@serendipita.com> wrote in message
news:doekll$n0s$1@news.xmission.com...
>
> Frank Langelage said:
>>
>> The JBossAS tries to execute this query and gets a syntax error:
>> SELECT MAX(TXID) FROM (
>> SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS
>> UNION
>> SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES
>> )
>>
>> The goal is to find the maximum txid out of two tables with one query.
>> I know I may create a view for the union select and select the max from
>> this view.
>> But I'd like to avoid this and use one query against the tables on my
>> IDS 10 server.
>
> Do you want both maxima, or just the bigger of the two?

Can you have a bigger maxima of the two?