Question
· 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
Log in or sign up to continue
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.

1 0
p rd · Aug 14, 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
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
3 0