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-19-2019 01:04 PM
I got it to work, thank you so much Linda for your help getting started!
09-09-2019 12:35 PM
Sorry I missed the time part. You can add
i.datestored AS 'Date Stored',
08-03-2021 06:50 PM
I know that I'm a bit late to the party, but this query will generate SQL statements for all doctypes in your system. I include a where clause at the end "where itemnum = 0" just so you don't accidentally run a query that will take years to complete. 🙂 You'll want to change that to the itemnums that you're interested in, or you can eliminate the where clause if you're not worried about retrieving too many items.
set nocount onif object_id('tempdb..#doctypes') is not null drop table #doctypescreate table #doctypes( itemtypenum bigint not null,-- itemtypename varchar(66) not null, keytypenum bigint not null)-- get a list of document types and their associated keyword typesinsert into #doctypes(itemtypenum,/* itemtypename,*/ keytypenum) select dt.itemtypenum, --rtrim(dt.itemtypename), itk.keytypenum from hsi.doctype dt inner join hsi.itemtypexkeyword itk on dt.itemtypenum = itk.itemtypenumif object_id('tempdb..#keywords') is not null drop table #keywordscreate table #keywords( keytypenum bigint not null primary key, keyword varchar(51) not null, --datatype bigint not null, columnExpression nvarchar(500) not null, columnAlias nvarchar(100) not null, joinStatement nvarchar(500) not null, cteExpression nvarchar(1000) null)-- get the column expressions and join statements necessary for each keyword type;with KeywordTable(keytypenum, tableName, tableAlias, keyword, datatype)as( select ktt.keytypenum, 'hsi.keyitem' + cast(ktt.keytypenum as varchar(3)) as tableName, 'ki' + + cast(ktt.keytypenum as varchar(3)) as tableAlias, ktt.keytype as keyword, ktt.datatype from hsi.keytypetable ktt)insert into #keywords(keytypenum, keyword,/* datatype,*/ columnExpression, columnAlias, joinStatement, cteExpression) select kt.keytypenum, kt.keyword, case when kt.datatype = 1 then kt.tableAlias + '.keyvaluebig' when kt.datatype = 2 then 'RTRIM(cte' + cast(kt.keytypenum as varchar(3)) + '.keyvaluechar)' when kt.datatype = 3 then kt.tableAlias + '.keyvaluecurr' when kt.datatype = 4 then kt.tableAlias + '.keyvaluedate' when kt.datatype = 5 then kt.tableAlias + '.keyvaluefloat' when kt.datatype = 6 then kt.tableAlias + '.keyvaluesmall' when kt.datatype = 9 then kt.tableAlias + '.keyvaluetod' when kt.datatype = 10 then 'RTRIM(' + kt.tableAlias + '.keyvaluechar)' when kt.datatype = 11 then kt.tableAlias + '.keyvaluecurr' when kt.datatype = 12 then 'RTRIM(cte' + cast(kt.keytypenum as varchar(3)) + '.keyvaluecharcs)' when kt.datatype = 13 then 'RTRIM(' + kt.tableAlias + '.keyvaluecharcs)' else cast (kt.datatype as varchar(20)) end, '['+ RTRIM(kt.keyword) + ']', case when kt.datatype in (2, 12) then ' left join cte' + cast(kt.keytypenum as varchar(3)) + ' on id.itemnum = cte' + cast(kt.keytypenum as varchar(3)) + '.itemnum' else ' left join '+ kt.tableName + ' as ' + kt.tableAlias + ' on id.itemnum = ' + kt.tableAlias + '.itemnum' end, case when kt.datatype in (2, 12) then 'cte' + cast(kt.keytypenum as varchar(3)) + ' as (select kxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum, stuff((select '', '' + rtrim(kt' + cast(kt.keytypenum as varchar(3)) +'.keyvaluechar) [text()] ' + ' from hsi.keyxitem' + cast(kt.keytypenum as varchar(3)) + ' ikxi' + cast(kt.keytypenum as varchar(3)) + ' inner join hsi.keytable' + cast(kt.keytypenum as varchar(3)) + ' kt' + cast(kt.keytypenum as varchar(3)) + ' on ikxi' + cast(kt.keytypenum as varchar(3)) + '.keywordnum = kt' + cast(kt.keytypenum as varchar(3)) + '.keywordnum' + ' where ikxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum = kxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum' + ' for xml path (''''), type) ' + '.value(''.'',''nvarchar(max)''),1,2,'' '') keyvaluechar' + ' from hsi.keyxitem' + cast(kt.keytypenum as varchar(3)) + ' kxi' + cast(kt.keytypenum as varchar(3)) + ' group by itemnum)' else null end from KeywordTable kt--select-- *-- from-- #doctypes dt-- inner join #keywords kw-- on dt.keytypenum = kw.keytypenum-- order by-- dt.itemtypenum,-- kw.keyworddeclare @itemnum bigintdeclare @itemtypenum bigintif object_id('tempdb..#sqlByDocType') is not null drop table #sqlByDocTypecreate table #sqlByDocType( itemtypenum bigint not null, sqlStatement nvarchar(max))-- loop through all the document types that have this keyword and keyword valuedeclare docTypeList cursor for select itemtypenum from #doctypes dt group by dt.itemtypenumopen docTypeListfetch next from docTypeList into @itemtypenumwhile @@FETCH_STATUS = 0begin declare @ctes nvarchar(max) = '' declare @selectStatement nvarchar(max) = 'select id.itemnum, rtrim(dt.itemtypename) as [Document Type], lc.lifecyclename as [Life Cycle], lcs.statename as [Queue]' declare @fromClause nvarchar(max) = ' from hsi.itemdata id inner join hsi.doctype dt on id.itemtypenum = dt.itemtypenum left join hsi.workitemlc wilc on id.itemnum = wilc.contentnum left join hsi.lcstate lcs on wilc.statenum = lcs.statenum left join hsi.lifecycle lc on wilc.lcnum = lc.lcnum ' declare @joinStatement nvarchar(500) declare @columnExpression nvarchar(500) declare @cteExpression nvarchar(1000) -- loop through the keywords and get the necessary column expressions and join statements declare sqlBuilder cursor for select ',' + kw.columnExpression + ' ' + kw.columnAlias, kw.joinStatement, kw.cteExpression from #doctypes dt inner join #keywords kw on dt.keytypenum = kw.keytypenum where dt.itemtypenum = @itemtypenum group by kw.columnExpression, kw.columnAlias, kw.joinStatement, kw.cteExpression open sqlBuilder fetch next from sqlBuilder into @columnExpression, @joinStatement, @cteExpression declare @haveCtes bit = 0 while @@FETCH_STATUS = 0 begin if @cteExpression is not null begin if @haveCtes = 0 set @ctes = '; with ' else set @ctes = @ctes + ', ' set @ctes = @ctes + @cteExpression set @haveCtes = 1 end -- add each column to the select statement set @selectStatement = @selectStatement + @columnExpression -- add each join to the from clause set @fromClause = @fromClause + @joinStatement fetch next from sqlBuilder into @columnExpression, @joinStatement, @cteExpression end close sqlBuilder deallocate sqlBuilder -- set up the final sql statement to execute with a dummy where clause insert into #sqlByDocType(itemtypenum, sqlStatement) select @itemtypenum, @ctes + @selectStatement + @fromClause + ' where id.itemnum = 0' fetch next from docTypeList into @itemtypenumendclose docTypeListdeallocate docTypeListselect * from #sqlByDocType order by 1
08-04-2021 06:19 AM
That's a solid piece of work! Good job!
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.