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?