Returning the MAX value of 2 fields in a select statement

Question

Question

Stella Ticker · Mar 18, 2017

I am trying to return the maximum of the value of 2 fields: LastViewed and LastDownloaded AS a local variable -LastAccessed for each row, using a SQL query . These values are stored as $ H format. Is there an existing SQL command that compares two column values ? I could not find one, so I tried using a $Select statement . I got an error that said A term expected beginning with either of: identifier, constant, aggregate, $$,(,:,+....)

Here is the SQL Query I am trying to run:-

Select ID, $Select($Translate(LastDownloaded, ',' ) > $Translate(LastViewed, ',' ):LastDownloaded, 1:LastViewed ) As LastAccessed From MyTable

I have successfully used $ Functions like $Piece before in SQL queries, so I am not sure if the query above is syntactically wrong or $Select is just now allowed in SQL queries. $Case did not work either.

Any ideas on how this SQL query should be structured?

Discussion (5)1

Log in or sign up to continue

Eduard Lebedyuk · Mar 18, 2017

Use GREATEST:

SELECT GREATEST(TO_DATE(58073), TO_DATE(58072))

3 0

score 0 · Answer 1 · 2017-03-18T14:08:43-04:00

Stefan Cronje · Mar 18, 2017

Have you tried just regular SQL case.

case when some-condition

then x

else y

end

0 0

score 1 · Answer 2 · 2017-03-18T14:38:50-04:00

Eduard is right, you should use GREATEST in this case. CASE statements are kind of sloppy, and while they are fine to use, I like to avoid them in order to keep the query as readable as possible.

More to your other points, you can find a list of valid SQL functions here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Note that all $-functions are not SQL Standard and will only work with Caché (most likely), so if you are interested in database agnosticism then try to use one of the more standard functions. Good luck!

-Kyle

score 0 · Answer 3 · 2017-03-18T16:34:45-04:00

Stella Ticker · Mar 18, 2017

Thanks guys ! GREATEST it is !!

0 0

score 0 · Answer 4 · 2017-03-20T04:53:56-04:00

There's a flaw in your original idea of eliminating the comma from two $H-format timestamps and then comparing them with the "greater than" operation. For most of a 24 hour period the resulting $TR(dollarH,",") is 10 digits long, but from midnight until 00:00:09 it is only 6 digits long, from 00:00:10 to 00:01:39 it is 7, from 00:01:40 to 00:16:39 it is 8 and from 00:16:40 to 02:46:39 it is 9 long.

A working alternative would be to compute $P(dollarH,",")*86400+$P(dollarH,",",2) for each and compare the results, which are the number of seconds since midnight at the start of 31st December 1840.

if you need to compare a $H-format timestamp against the current time don't use $P($H,",")*86400+$P($H,",",2) as one of the expressions because there's a small possibility that your two fetches of $H will fall either side of midnight. Instead, fetch $H once, store it in a variable, then process that variable.