Cache SQL sorted after sorting

Primary tabs

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!

  • 0
  • 0
  • 399
  • 6
  • 2

Answers

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

Can be used, thank you.

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.

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) >]

2.

 

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' >]
 

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

Comments

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