?
Solved

Onbase document storage database queries

Posted on 2013-10-29
2
Medium Priority
?
1,356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 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 13

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
Curious about the latest ransomware attack? Check out our timeline of events surrounding the spread of this new virus along with tips on how to mitigate the damage.
Sometimes we receive PDF files that are in the wrong orientation. They may be sideways or even upside down. This most commonly happens with scanned or faxed documents. It is possible to rotate the view of these PDFs with the free Adobe Reader produc…
We often encounter PDF files that are pure images, that is, they do not have text characters, but instead contain only raster graphics. The most common causes of this are document scanning software and faxing software/services that create image-only…

771 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