Solved

Onbase document storage database queries

Posted on 2013-10-29
2
1,204 Views
Last Modified: 2013-10-30
I have a onbase database that is used as part of a document storage system.

I need to run some queries on some of the keyitem tables, but I'm seeing 9 duplicates out of over 11000 records.

The problem involves being able to uniquely match up employeeID, EmployeeFirstName, EmployeeLastName.

I know that the webfocus interface is able to uniquely access the individuals, but for some reason, when I try to query from ms sql, I cannot determine which tables to use to enforce a unique record to get the names correctly.  

How does webfocus reference these records?

Does webfocus have access to some other cross reference table that I'm not aware of?


Below is a script to create sample data from the Keyitem tables I need data from:

keyitem102 = EmployeeID
keyitem104 = FirstName
keyitem103 = LastName



-- create sample tables
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- EmployeeID Table
CREATE TABLE [WFAdmin].[keyitem102](
	[itemnum] [int] NULL,
	[keyvaluebig] [decimal](18, 0) NULL,
	[keysetnum] [int] NULL
) ON [PRIMARY]

GO
-- LastName Table
CREATE TABLE [WFAdmin].[keyitem103](
	[itemnum] [int] NULL,
	[keyvaluechar] [char](20) NULL,
	[keysetnum] [int] NULL
) ON [PRIMARY]

GO

-- FirstName Table
CREATE TABLE [WFAdmin].[keyitem104](
	[itemnum] [int] NULL,
	[keyvaluechar] [char](20) NULL,
	[keysetnum] [int] NULL
) ON [PRIMARY]


-- Insert Samples

-- keyitem102 - EmployeeID
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (65158,5263,0);
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (65158,6380,0);
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (162572,9729,0);
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (162572,10800,0);
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (164372,8592,0);
INSERT INTO [WFAdmin].[keyitem102] 
            (itemnum,keyvaluebig,keysetnum) 
VALUES      (164372,9155,0);


            
-- keyitem104 - LastName
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (65158,'ANDERSON',0);
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (65158,'PRATCHETT',0);
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (162572,'SMITH',0);
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (162572,'HENDERSON',0);
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (164372,'ORWELL',0);
INSERT INTO [WFAdmin].[keyitem103] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (164372,'CLINTON',0);

  
-- keyitem104 - FirstName
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (65158,'RICHARD',0);
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (65158,'TERRY',0);
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (162572,'JOHN',0);
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (162572,'CAL',0);
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (164372,'GEORGE',0);
INSERT INTO [WFAdmin].[keyitem104] 
            (itemnum,keyvaluechar,keysetnum) 
VALUES      (164372,'BILL',0);
 

Open in new window


Here is an attempt to query the data

-- Query to list employees FirstName,LastName,EmployeeID
SELECT 
       keyitem104.keyvaluechar AS FirstName, 
       keyitem103.keyvaluechar AS LastName, 
       keyitem102.keyvaluebig  AS EmployeeID 
FROM   keyitem102
         JOIN keyitem103 
         ON keyitem103.itemnum = keyitem102.itemnum  
         JOIN keyitem104 
         ON keyitem104.itemnum = keyitem102.itemnum 

Open in new window


here is the sample output:

sample data1
The duplicates are not allowing me to refer to the records correctly.  I don't see a way to link the table properly, but I know a way must exist because the webfocus application that uses this data has no problem returning the proper relationships.  I just don't know how webfocus maintains the relationships.

Here is what I want to see somehow.

sample 2
0
Comment
Question by:Jeff Darling
2 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39610644
Does webfocus have access to some other cross reference table that I'm not aware of?

Yes.

I dealt with the OnBase SW for a while in the past. There is another table that is needed, but I can't remember the name anymore. Basically it is the table where they assign the KeyItem to the CD/DVD/Blu-ray sizes. So the there is always three tables involved in the query.\

Plus the query needs to by dynamic.
0
 
LVL 12

Author Comment

by:Jeff Darling
ID: 39611366
Thanks.  I will see if I can get read access to at least see the tables.  I'm kind of working in the dark here because of security concerns.  at least now I can justify my request for more access confident that there is another table I need to reference.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question