November 4, 2020 at 10:05 pm
This summer I authored a report that referenced nine tables in a query. This is against an older database. This is for a pharmacy application that someone back when wrote originally in a Microsoft Access application. Along the way somebody upgraded the database from the original .MDB to a SQL 2012 database, where it currently exists. Today they still use the Access app as a front-end. (We're working on replacing it with a modern app and upgrading the database, but that's not the issue I'm writing about.)
Today the manager of the pharmacy contacted me to say that the report wasn't returning any values. It turns out that this is because the record that the user is trying to get the data from has a null value in the field that the query uses for the relevant table. Here's the definition of the table:
CREATE TABLE [dbo].[PharmSupplierVendors](
[SupplierID] [int] IDENTITY(1,1) NOT NULL,
[SupplierName] [nvarchar](40) NULL,
[MainSupplies] [nvarchar](50) NULL,
[WarehouseSupplierYN] [bit] NOT NULL,
[Address1] [nvarchar](40) NULL,
[Address2] [nvarchar](40) NULL,
[CityLine] [nvarchar](40) NULL,
[SupplierCity] [nvarchar](50) NULL,
[SupplierState] [nvarchar](2) NULL,
[SupplierZip] [nvarchar](10) NULL,
[Contact1Name] [nvarchar](50) NULL,
[Phone1] [nvarchar](15) NULL,
[Contact2Name] [nvarchar](50) NULL,
[Phone2] [nvarchar](15) NULL,
[Fax] [nvarchar](15) NULL,
[DateEntered] [datetime] NULL,
[DateModified] [datetime] NULL,
[EnteredByEmployeeID] [int] NULL,
[NDCLabelerCode] [nvarchar](5) NULL,
CONSTRAINT [aaaaaPharmSupplierVendors_PK] PRIMARY KEY NONCLUSTERED
(
[SupplierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
Another table that's important to the problem I face is the PharmInventoryIn table. Here is it's definition:
CREATE TABLE [dbo].[PharmInventoryIn](
[InventoryInID] [int] IDENTITY(1,1) NOT NULL,
[StateFiscalYearAbbrev] [nvarchar](10) NULL,
[ShipmentInPONum] [nvarchar](20) NULL,
[InventoryActionID] [smallint] NOT NULL,
[ProgramID] [int] NULL,
[SupplierID] [int] NULL,
[SupplierOrderNum] [nvarchar](20) NULL,
[SupplierInvoiceNum] [nvarchar](20) NULL,
[ReturnedFromLocationID] [int] NULL,
[DateReceived] [datetime] NOT NULL,
[OrderedByEmployeeID] [int] NULL,
[ReceivedByEmployeeID] [int] NULL,
[PostedYN] [bit] NOT NULL,
[ReasonReturned] [nvarchar](50) NULL,
[Notes] [ntext] NULL,
[DateEntered] [datetime] NULL,
[DateModified] [datetime] NULL,
CONSTRAINT [aaaaaPharmInventoryIn_PK] PRIMARY KEY NONCLUSTERED
(
[InventoryInID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The problem is the SupplierID in the PharmInventoryIn table has a null value, for the record in the record that it should connect to in the PharmSupplierVendors table. The issue is that sometimes the places we ship product to, returns the product back to us. Whoever wrote the original Access .MDB and app wasn't a professional DBA, because the only way they accounted for that was by using the NVARRCHAR(20) field SupplierOrderNum in the PharmInventoryIn table. So, over the many years this has been used people will get into the app (or go directly to the table) and enter the previous SupplierID. Only they might do this multiple times depending upon how often we ship to distributors and they ship it back to us. Looking at the 4394 records which have some value for SupplierID in PharmInventoryIn I've seen up to 5 trades back and forth between us and different distributors. And that's not including things like people entering "ORG INV: 15946" in SupplierID instead of just 15946. Sigh.
Somehow, I've got to find a way to fix this, if possible. I've hit about the idea of making the PharmSupplierVendors table be a self-referential table by adding a new column to it indicating the parent of the current PharmSupplierVendors record. But I'm not sure how I'd go back recursively in a query, to find the original, first PharmSupplierVendors record, which would yield the original PharmInventoryIn record.
Any ideas?
Kindest Regards, Rod Connect with me on LinkedIn.
November 4, 2020 at 10:39 pm
Looking at the 4394 records which have some value for SupplierID in PharmInventoryIn I've seen up to 5 trades back and forth between us and different distributors. And that's not including things like people entering "ORG INV: 15946" in SupplierID instead of just 15946.
Oh boy... Can you do something like strip off the non-numeric portion of the string (I would swear Alan Burstein posted code that does this, so check his stuff) and maybe persist it by creating a materialized view? (Assuming you can't change the structure of the original table).
What do you mean by "last record" - the latest by date? (Maybe use a windowing function? or use CROSS APPLY and TOP(1) ORDER BY <date column> DESC?
November 5, 2020 at 8:26 pm
Good question, @pietlinden. I think they do mean by date. Most likely DateReceived in the PharmInventoryIn table. Getting the oldest one of a group all related to the same series of shipping from the pharmacy to the distributor would, I think is correct. I'll have to check with the users to make sure I've got it correct.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply