It looks like there is a bug with complicated SQL views when used in @DBColumn.
The following SQL in a SQL View works:
in Notes (I can see results in the client when I open the view),
works in Web (I can open the view on the browser …/viewname?openview and see results)
works when used with @DBColumn in Notes (in a form I have a dialog list field with values: @DBColumn(“”;“”;“viewname”;1) and I can select a value)
BUT
it doesn’t work when used with @DBColumn on web (in the same form as before I have the dialog list field with values @DBColumn(“”;“”;“viewname”;1) and I get the ERROR: “Note item not found” in the field.
Any idea if this is a bug? Thanks.
s:=@DB2Schema(@DbName);
"SELECT " +
"tmp1.ProjectId AS Id, " +
"tmp1.ProjectName AS ProjectName, " +
"tmp1.SiteId AS SiteId, " +
"site.Name AS SiteName, " +
"tmp1.EventId AS EventId, " +
"tmp1.EventName AS EventName " +
"FROM " +
"(SELECT " +
"project.Id AS ProjectId, " +
"project.Name AS ProjectName, " +
"project.Site_Id AS SiteId, " +
"project.Event_Id AS EventId, " +
"event.Name AS EventName " +
"FROM " +
s + ".Project_V AS project " +
"INNER JOIN " +
"(SELECT " +
"Id, " +
"Name " +
"FROM " +
s + ".Event_V " +
"WHERE " +
"IsActive = '1') AS event " +
"ON " +
"project.Event_Id = event.Id) AS tmp1 " +
"INNER JOIN " +
"(SELECT " +
"Id, " +
"Name " +
"FROM " +
s + ".Site_V " +
"WHERE " +
"IsActive = '1') AS site " +
"ON " +
"tmp1.SiteId = site.Id "