Solved

Onbase document storage database queries

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

749 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