My Search-How to create column name from Tag Name (create table as select...)

[roman104]

Hi,
I am trying to collect data for 'project work evidence' process based on 'resource allocation' feature. I would like to record on with 'ProjectID' resources spent their time, by which 'ProjectActivities'. So I have added new Tag into special elements for collecting the timesheet records.
Values from resource allocation and tag values I would like to use as primary data in Pivot table.
Problem is, that 'My search' returns 'Tags name' and 'Tag value' as Column names, and I need special column for every 'Tag Value'. It is possible via sql command 'create table as select'... (see https://stackoverflow.com/questions/18637160/sql-create-columns-from-values-and-fill-with-other-column).
But it doesn't work for me. Maybe this sql command is not supported in EA at all?


My SQL search is:
 select t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,
t_object.Object_ID,t_object.CreatedDate as CreatedDate,
t_objectresource.[Resource], t_objectresource.[Role], t_objectresource.DateStart, t_objectresource.DateEnd, Round(t_objectresource.[Time]/60) AS AllocatedHours,
(t_objectresource.[ExpectedHours]/60)As ExpectedHours, (t_objectresource.[ActualHours]/60)As ActualHours, t_objectresource.PercentComplete, t_object.Name, t_object.Alias, t_objectresource.Notes, p.property as Tag, p.value as TagValue
from t_objectresource, t_object
INNER JOIN t_objectproperties p
ON t_object.object_id = p.object_id
where t_object.Object_ID = t_objectresource.Object_ID
and p.Value LIKE '#WC#<Search Term>#WC#'


How to adopt this SQL statement in internal EA Search?


Thank You
Roman

[qwerty]

What is the issue? I don't know what you need in resources etc. but the search does not throw any errors, so it should be working.

Maybe you can elaborate on what to set in EA('s objects) to get some result so one can test this.

q.

[roman104]

Yes the select works as it is written.
But my problem is, that I have defined in element more Tags values. Eg. Tag Name ProjectID. The select returns Table with 2 Columns:
Tag, tag Value. It is correct, but I need very strange thing. I need from TagValue to  create Table column Name.
It is some solution which is using select for creating new table with columns name created from TagValue.
But I am not able to modify my select in this way neither in ea clilent or in MySQL wokbench...
Roman

[qwerty]

You eventually need to write an add-in search, or just use an add-in to do what you intended in first place.

q.

[Helmut Ortmann]

Hi,

additionally to what q has said:

-  Use the features of your database like:
--   You can create a view and then simply query that view from EA
--   Stored procedures

Best regards,

Helmut