Cache SQL sorted after sorting

Caché, Terminal, SQL

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!

Answers

Answer by Robert Cemper 14 August 2017

not a problem of version

A subquery using ORDER requires a TOP clause

Change the query to

SELECT *
FROM
  (SELECT TOP ALL
          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

+ 1

Accepted answer

Comment by p rd 14 August 2017

Can be used, thank you.

Answer by Dmitry Maslennikov 14 August 2017

I fixed formatting in your post a little bit just to look it more clear and prettier.

But noticed WHERE% VID instead of WHERE %VID, and AND% VID instead of AND %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

Comment by p rd 14 August 2017

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

------------------------------

info:

SQLCODE:<-1>:<Invalid SQL statement>
[Location:<Prepare>]
[%msg:<Need), find the identifier (order) >]

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

-----------------------------------------

info:

SQLCODE:<-29>:<The corresponding table did not find the field>
[Location:<Prepare>]
[%msg:<The corresponding table did not find the field '%VID' >]

Comment by Dmitry Maslennikov 14 August 2017

And which Caché version are you using?

%VID appeared since 2011.1.

Comment by p rd 14 August 2017

cache 2016.

Comment by Achraf SOLTANI 1 June 2019

Is that actually a table or a view ? it seems that %VID works only on SQL Views

Comments

Comment by Robert Cemper 14 August 2017

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

+ 2