Jump to content
OMRON Forums

LVS-95XX Useful Queries and examples


Recommended Posts

Query on the Reference field and Date (last 30 days) sorted by Date (Newest to Oldest)
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where Reference="9580-KLS" AND LclTime >= #2019-10-02 16:26:38# Order By LclTime DESC


Query on the DecodedText field and Date (last 30 days) sorted by Date newest to oldest
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="RGT23244037" AND LclTime >= #2019-10-02 16:31:55# Order By LclTime DESC


Query (DecodedText field and Date range) sorted by Date (Newest to Oldest)
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By LclTime DESC
Or sorted by ReportID (Descending)


Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By ReportID DESC


This works also if you don’t want to specify the time of day.
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01# AND #2019-01-01# Order By ReportID DESC


Query covering data from November 1, 2019 back to November 1, 2018 is:
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText= "12345678" and LclTime>=#2018-11-01# Order By ReportID


JOINING TWO DATA TABLES
Report joining two data tables for filtering by Reference (Report table) and Additional reference (Report.Data table)
Select Reports.ReportID, SectorID, LclTime, Reference, ReportData.ParameterValue as AddtionalReference, OverallGrade,DecodedText From Reports INNER JOIN ReportData ON Reports.ReportID = ReportData.ReportID Where ReportData.ParameterName = "Additional reference" AND ReportData.ParameterValue="xxx" AND Reports.Reference ="xxx" Order By Reports.ReportID

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share


×
×
  • Create New...