Hello there
Is it possible to change the result of a query to a Y or N? For example, I want to search eB for all containers that contain a file. I would like a column in the search results that simply says "Y" if there is at least one file in the container, or N for no files at all.
For info, we're running Assetwise ALIM 16.
Many thanks!
Chris
Use a CASE statement:
START WITH DocumentCopy SELECT Document.Id, Document.Code,
[. . .] CASE IFNULL(Files.File.Id, 0) WHEN 0 THEN 'N' ELSE 'Y' END "Has File?" WHERE Document.IsTemplate = 'N'
Eric Rajala | Consultant
Cohesive
Email: eric.rajala@cohesivegroup.com
www.bentley.com
Hi Eric. Thank you for your response. The results lists which containers contain files but if a row has five files (or the file has been checked out a number of times) the row is repeated five times. Is there any way of removing these duplicate entries?
In the WHERE clause try: . . . AND IFNULL( Files.Sequence, 1 ) = 1
If you want to include hidden files in the results use "<=" instead of "=", but in this case you might get some duplicates again because hidden files are all sequenced with "-1".
The original question was about containers, so it used START WITH DocumentCopy . . . Be aware that if an object, usually a document in this case, has more than one copy (container) it may appear more than once in the results.