Written by

Question p rd · Aug 13, 2017

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!

Discussion (8)0

Add reply

Comments

Dmitry Maslennikov · Aug 14, 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.

0 0

p rd Aug 14, 2017 to Dmitry Maslennikov

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

0 0

p rd Aug 14, 2017 to Robert Cemper

Can be used, thank you.

0 0

Dmitry Maslennikov Aug 14, 2017 to p rd

And which Caché version are you using?

%VID appeared since 2011.1.

0 0

p rd Aug 14, 2017 to Dmitry Maslennikov

cache 2016.

0 0

Achraf SOLTANI Jun 1, 2019 to p rd

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

0 0

Robert Cemper · Aug 14, 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

0 0

Robert Cemper · Aug 14, 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

0 0