10-15-2013 09:36 AM
Hello,
How can I get the report or query to see all the possible / exiting values for keyword if that key word has multiple values?
I can execute the search like show me all docs created in last two days (assuming that some of the docs have multiple values (F6) for the KW, when I extract the KW through List report off the search result, I can see only first value of that KW, I need to know all the values for that KW
Your help is greatly appreciated
Thanks
Anil
10-15-2013 10:55 AM
Thanks John/Greg,
it does work partially (no errors) but i need to list these reviewer names along with the count whic is under hsi.keyitem1394 table
SELECT kw.itemnum, Count(kw.itemnum) AS 'numkeys' FROM hsi.itemdata it with(nolock) inner join hsi.keyitem1394 kw with(nolock) on kw.itemnum=it.itemnum WHERE it.status = 0 and it.itemtypenum=453 GROUP BY kw.itemnum HAVING (Count(kw.itemnum)>1)
i got following as a result of above query
18987780 5
19888517 5
20354835 5
20396295 5
Sorry, i am not an expert on SQL, please help
Thanks
Anil
10-15-2013 10:59 AM
select i.itemname AS [Document], k.keyvaluechar AS [Reviewer]
from hsi.itemdata i inner join hsi.keyitem1394 k on i.itemnum = k.itemnum
order by i.itemnum
-- try this to align with your desired output.
-- Hope this helps!
10-15-2013 12:26 PM
Thank you so much for your help
Anil
10-15-2013 12:41 PM
Anil - The previous query doesn't work for what you need because you cannot list each itemnum, for each Reviewer and only count each Reviewer. So if you only need to know the names of each Reviewer, for each document type - and not the itemnums, then Philip's answer will suffice.
10-17-2013 10:53 PM
Anil,
As I've previously demonstrated elsewhere, this can be done. Just add this SQL using the inbuilt stuff() function as an extra column to your query to get the value of your Reviewers.
stuff( (SELECT '/' + rtrim(KEYITEM1394.keyvaluechar) FROM HSI.KEYITEM1394 WHERE KEYITEM1394.itemnum = KW.itemnum GROUP BY KEYITEM1394.keyvaluechar FOR XML PATH('')), 1, 1, '') as keywords
You should get something like this
1234567 6 Reviewer1/Reviewer2/Reviewer3
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.