End user problem reading data when items have duplicate or more ids

Database

End user problem reading data when items have duplicate or more ids

Hi all,

This is just an example of the problem I have, in real life it’s has other columns and so on but this example suits my question.

The thing is this that in Table 1 there are records of Items stored and they are delivered from different systems. Each system has or generates a new ItemId if the item is new and inserts or updates the new rows to Table 1. Some of the deliveries are checked against table2 in order to get the a list of ItemId’s belonging to the same item but not all (example of this is the delivery that inserted the type of X and Y, they are stored on both item 1111 and 2222 by joining on table 2)

Problem: End users want to see a complete list of all items stored in the system regardless of what system it was delivered from. The logic in the end user system looks at the data through a teradata view which does not pick up all of the items by their item ids (no changes are to be done to the view).

Table1 – Item records

ItemId

Type

Amount

Date

1111

X

100

XXXX-XX-XX

1111

Y

200

XXXX-XX-XX

2222

X

100

XXXX-XX-XX

2222

Y

200

XXXX-XX-XX

3333

Z

300

XXXX-XX-XX

4444

P

600

XXXX-XX-XX

Table2 – Item explained

ItemId

State

Region

Municipality

City

1111

OKL

BER

Teradata

Teraville

2222

OKL

BER

Teradata

Teraville

3333

OKL

BER

Teradata

Teraville

4444

OUS

NUR

Teradata

Dataville

Table1 – How I would like the tables contents to be in order for end user to get the right results.

ItemId

Type

Amount

Date

1111

X

100

XXXX-XX-XX

1111

Y

200

XXXX-XX-XX

2222

X

100

XXXX-XX-XX

2222

Y

200

XXXX-XX-XX

3333

Z

300

XXXX-XX-XX

4444

P

600

XXXX-XX-XX

1111

Z

300

XXXX-XX-XX

2222

Z

300

XXXX-XX-XX

3333

X

100

XXXX-XX-XX

3333

Y

200

XXXX-XX-XX

Is there any good ways to solve this problem? Beforehand we do not know how many ItemIds there are for the same Item, we need to pick them up from Table 2 by means of State, Region, Municipality and City as the key. Analysis of the data has shown that the ItemIds for a specific Item can be as many as 12 but usually there are between 1-6 ItemIds per Item.

Tags (1)