Cache SQL sorted after sorting
I used Cache 2016.
I used the following statement to sort after paging, but suggested an error:
SELECT *
FROM
(SELECT sslog.userRowid,
sslog.CreateDate,
sslog.CreateTime,
Sslog.SSUSR,
sslog.Terminal,
ssuser.Initials,
ssuser.USName
FROM SUser_Log sslog
LEFT JOIN DHC_SSuser ssuser ON (Sslog.SSUSR = ssuser.userRowid
OR sslog.ssusr = ssuser.Initials
OR sslog.ssusr = ssuser.USName)
WHERE ssuser.USName ['admin'
ORDER BY sslog.CreateDate DESC)
WHERE %VID> 5
Also used the following way:
SELECT sslog.userRowid, sslog.CreateDate, sslog.CreateTime, Sslog.SSUSR, sslog.Terminal, ssuser.Initials, ssuser.USName FROM SUser_Log sslog LEFT JOIN DHC_SSuser ssuser ON (Sslog.SSUSR = ssuser.userRowid OR sslog.ssusr = ssuser.Initials OR sslog.ssusr = ssuser.USName) WHERE ssuser.USName ['admin' AND %VID> 5 ORDER BY sslog.CreateDate DESC
But suggested that% VID does not exist.
How can I modify my SQL statement? Thank you!
I fixed formatting in your post a little bit just to look it more clear and prettier.
But noticed
WHERE% VID
instead ofWHERE %VID
, andAND% VID
instead ofAND %VID
. And decided that it was an formatter error, which I used, but looks like it was in original SQL as well.So, can you add error message? For me, the first query should work, while next one will not work, because %VID will not exist.
1.
------------------------------
info:
SQLCODE:<-1>:<Invalid SQL statement>
[Location:<Prepare>]
[%msg:<Need), find the identifier (order) >]
2.
-----------------------------------------
info:
SQLCODE:<-29>:<The corresponding table did not find the field>
[Location:<Prepare>]
[%msg:<The corresponding table did not find the field '%VID' >]
Can be used, thank you.
And which Caché version are you using?
%VID appeared since 2011.1.
cache 2016.
Is that actually a table or a view ? it seems that %VID works only on SQL Views
not a problem of version
A subquery using ORDER requires a TOP clause
Change the query to
Pls. don't forget to mark your question as "answered" on Developer Community,
please click the checkmark alongside the answer you (as author of the question) accept
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue