Back
  1. Home
  2. Cache SQL sorted after sorting

Cache SQL sorted after sorting

Primary tabs

p rd's picture
p rd 13 August 2017
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
p rd's picture
p rd 13 August 2017

Answers

Robert Cemper's picture
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   0
Accepted answer
p rd's picture
Comment by p rd 14 August 2017

Can be used, thank you.

0 0
Dmitriy Maslennikov's picture
Answer by Dmitriy 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   0
p rd's picture
Comment by p rd 14 August 2017

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

0 0
Achraf SOLTANI's picture
Comment by Achraf SOLTANI 1 June 2019

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

0 0

Comments

Robert Cemper's picture
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 0