Solved

Onbase document storage database queries

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A list of useful business intelligence software.
If your app took Google’s lash recently, here are the 5 most likely reasons.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

743 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

16 Experts available now in Live!

Get 1:1 Help Now