question

Hmnt avatar image
Hmnt asked

Deploy view or use function for lookup

I have two master tables Org & Item and then there's an OrgItem table.I have to fetch ItemCodes for a particular Org. TABLE STRUCTURE:
  • Org ( Id, OrgCode, Name) - Org Master table
  • Item ( Id, ItemCode, Name) - Item Master table
  • OrgItem ( ItemId, OrgId, ItemCode) - Some Org specific ItemCodes


Only some Items have an Org-specific ItemCode, so by default I fetch the ItemCode from Item, but if for a particular Item-Org combination if its record exists in the OrgItem table then I have to fetch the ItemCode from the OrgItem table.

Creating a View_OrgItem_Item might be a solution but it would mean a view which consists of n-fold the size of the original Item table (where n = number of Orgs).

Another option is to create a GetItemCode(ItemId, OrgId) which does the lookup and return either Item.ItemCode or ORgITem.ItemCode

So, my question is - should I go for a 'View_OrgItem_Item' or simply create a function GetItemCode(ItemId, OrgId)?

Which one is best in terms of performance? Are there any other factors to consider?

*Note: It is NOT necessary that ALL the Orgs have records in OrgItem

performancefunctionslookups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
GPO avatar image
GPO answered
A scalar function is unlikely to be the answer in any lookup scenario. Would a CASE expression do the trick?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.