09-06-2019 08:42 AM
I am looking for some help with a SQL query that would give me all keywords, including datestored (since it has time) for 1 particular doc type. Currently my user is using Generate CSV file which has worked great but it doesn't give the time that it came in. She needs a query that gives all of her keywords, date and time. Ideally date and time would be two separate columns but I can handle them in one to at least get the data out. Once I have that she also needs to know when she is using the ad hoc tasks (to approve, deny or delete which is basically changing to status keyword #741) in the workflow for this particular doc type. This doc type is a Unity form and is the only doc type in the doc type group (#122). If anyone can help, that would be great. I have this so far which does give me my datestored info but it doesn't give me any keywords (and there are 25 of them). I am thinking that maybe I need to join each keyword?
Here is what I have so far:
select * from hsi.itemdata
where itemtypegroupnum = 122
09-06-2019 10:40 AM
if each keyword is its' own keyword in its' own table you'll need to do a left join to each keyitem table and then modify your select statement to only include the field from the keyitem table that you need. That said, if this is a MIKG, then everything is in the same table and you will only need to join to that table and select the columns you need. I didn't see how this was set up or perhaps I missed it... Do you need more?
09-09-2019 06:27 AM
Hey Tom, thanks for your response. These are not MIKG. I am still a newbie at SQL queries!
So for example, here are a couple of the keyword #'s:
Student ID | 742 |
First Name | 175 |
Last Name | 176 |
Address | 137 |
City | 624 |
Zip Code | 231 |
County - MN | 168 |
MN Leg District | 736 |
Could you maybe give me an example or two of the left join statements?
09-09-2019 11:19 AM
w3schools can do far better than me, but this is a part sample...
select top 5 *
from hsi.itemdata as it
Left join hsi.keyitem### as k###
On it.itemnum = k###.itemnum
where itemtypegroupnum = 122
Note: you’ll have to do a left join for every keyword. While repetitious this also makes for a pretty big query. These are not the types of queries that newbies should be doing. Any mistake along the way could create a join that returns a cartesian product that could grab all your database resources. So, I included a top 5 to limit your results to 5, which should help protect you from yourself until you are ready. Also, make sure to start in your test environment....
09-10-2019 12:24 PM
Thanks fro the advice Tom! I was trying to run your query but it does not like his.itemdata.
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.