Solved

Onbase document storage database queries

Posted on 2013-10-29
2
1,140 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
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…
Viewers will learn how the fundamental information of how to create a table.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now