sql question

[kaminari]

I have this query
select max (last_update) from trnsfr_main where user_id='706'
union
select max (last_update) from ship_main where user_id='706'
union
select max (last_update) from return_main where user_id='706'
union
select max (date(dl_date)) from file_main where user_id='706'

When I run the query I get two different dates results back. I only want the latest date of the two back how do I do this?

 

[baghira]

Hello Kaminari,

I am not sure and I hope I don´t tell you some wrong. But is it possible that you have two different datatypes in your query? Try to form every selected Column in a datetype like you did it in the last queryrow.
Like I say I am not sure if this is the key for a right result but it is a try.

cu Olli

 

[kaminari]

actully the first three querys are date stamps and the last query was a datetime stamp I changed it to a date, so now they are all the same data type.

 

[Capoerista]

Please can you clarify how many rows this query returns ? If there is data in all these tables you will be getting 4 dates returned.

If you want to get the MAX of these 4 dates you will probably need to put the results in a TEMP table and then do your SELECT MAX from there.


Ade

 

[kaminari]

if each date is different there could be a chance that there might be 4 rows that would be returned. I only need the latest date out of the results. how would you suggest to put it into a temp table?

 

[Capoerista]

Sorry, I have been away for a week but the following should work

select max (last_update) last_date from trnsfr_main where user_id='706'
union
select max (last_update) last_date from ship_main where user_id='706'
union
select max (last_update) last_date from return_main where user_id='706'
union
select max (date(dl_date)) last_date from file_main where user_id='706'
into temp temp_table;

select max(last_date) from temp_table;

Ade

 

[kaminari]

I tried your sugestion but I get an error saying that the temp_table isn't in the database. any suggestions?

 

[Siggy19]

You may not have permissions to create temporary tables.

Alternatively, you may have spelled the temporary table name differently in the two places you refer to it.

(I know that you aren't that dumb, but I am and have done it myself so...)