Solved

SQL rows being inserted with incorrect null values.

Posted on 2014-02-26
3
443 Views
Last Modified: 2014-03-13
Hello Experts,

I am working with the following stored procedure.

USE [NGTest]
GO
/****** Object:  StoredProcedure [dbo].[LMC_Finalize_Split_Procedure3]    Script Date: 02/25/2014 17:58:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[LMC_Finalize_Split_Procedure3]
  @enterprise_id VARCHAR(5),
  @practice_id VARCHAR(4),
  @person_id CHAR(36),
  @enc_timestamp DATETIME,
  @user_id       INT,
  @enc_id        VARCHAR(36),
  @cpt_code		 VARCHAR(30),
  @provider_id   VARCHAR(36),
  @location_id   VARCHAR(36), 
  @offset		 int	-- This procedur number, to offset the ICDs
  
AS

BEGIN	
	DECLARE @pp_uniq_id UNIQUEIDENTIFIER; 
	DECLARE @serviceItemLibraryID UNIQUEIDENTIFIER;
	DECLARE @serviceItem varchar(100);
	
	SET @serviceItemLibraryID = '00CB861F-B72F-47B9-8BAD-669ED330B3E2';-- [LMA] hardcoded! probably need to be changed in future versions
	SET @pp_uniq_id = NEWID(); 
	
	-- (LMA) The service Item should not be confused with the CPT Code. They could be different. 
	SELECT @serviceItem = service_item_id
	FROM service_item_mstr 
	WHERE cpt4_code_id = @cpt_code
	AND service_item_lib_id = @serviceItemLibraryID;
	
	IF @serviceItem IS NULL 
	BEGIN
		/* debug infpormation 
		--INSERT INTO lma_debug (text) values ('Could not find a service item for ' + @cpt_code);
		
		PRINT "Invalid Service Item ID"; */
		RETURN
	END
	
	
		--,(select isnull(dx_code, 'V70.0') from ##ICDs where row_num =  (@offset * 4) -3)
--           ,(select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = 
--								(select isnull(dx_code, 'V70.0') from ##ICDs where row_num =  (@offset * 4) -3 )
								
declare @diag_code_1 varchar(10)
declare @lib_id_1 varchar(36)
declare @diag_code_2 varchar(10)
declare @lib_id_2 varchar(36)
declare @diag_code_3 varchar(10)
declare @lib_id_3 varchar(36)
declare @diag_code_4 varchar(10)
declare @lib_id_4 varchar(36)

DECLARE @TEMP INT
SET @TEMP = 0

set @diag_code_1 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -3)

if @diag_code_1 is null
    begin
             SET @TEMP = 1
	     set @diag_code_1 = 'V70.0'
	end
	
set @lib_id_1 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_1)



set @diag_code_2 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -2)

if @diag_code_2 is null
    begin
             IF @TEMP = 0
             BEGIN 
	         set @diag_code_2 = 'V70.0'
                 SET @TEMP = 1
             END
	end
	
set @lib_id_2 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_2)



set @diag_code_3 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -1)

if @diag_code_3 is null
    begin
             IF @TEMP = 0
             BEGIN
	        set @diag_code_3 = 'V70.0'
                SET @TEMP = 1
             END
	end
	
set @lib_id_3 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_3)



set @diag_code_4 = (select dx_code from ##ICDs where row_num =  (@offset * 4))

if @diag_code_4 is null
    begin
             IF @TEMP = 0
             BEGIN
	         set @diag_code_4 = 'V70.0'
                 SET @TEMP = 1
             END
	end

set @lib_id_4 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_4)


	INSERT INTO [dbo].[patient_procedure]
           ([uniq_id]
           ,[enterprise_id]
           ,[practice_id]
           ,[person_id]
           ,[enc_id]
           ,[service_item_lib_id]
           ,[service_item_group_name]
           ,[service_item_id]
           ,[service_item_desc]
           ,[cpt4_code_id]
           ,[service_date]
           ,[provider_id]
           ,[referring_provider_name]
           ,[date_resolved]
           ,[diagnosis_code_id_1]
           ,[diagnosis_code_lib_id_1]
           ,[diagnosis_code_id_2]
           ,[diagnosis_code_lib_id_2]
           ,[diagnosis_code_id_3]
           ,[diagnosis_code_lib_id_3]
           ,[diagnosis_code_id_4]
           ,[diagnosis_code_lib_id_4]
           ,[place_of_service]
           ,[accept_assign_ind]
           ,[units]
           ,[location_id]
           ,[amount]
           ,[suppress_billing_ind]
           ,[tooth]
           ,[surface]
           ,[quadrant]
           ,[delete_ind]
           ,[note]
           ,[created_by]
           ,[modified_by]
           ,[create_timestamp]
           ,[modify_timestamp]
           ,[start_time]
           ,[stop_time]
           ,[total_time]
		   ,[alt_code]
           ,[anesthesia_billing_ind]
           ,[diagnosis_code_id_5]
           ,[diagnosis_code_lib_id_5]
           ,[diagnosis_code_id_6]
           ,[diagnosis_code_lib_id_6]
           ,[diagnosis_code_id_7]
           ,[diagnosis_code_lib_id_7]
           ,[diagnosis_code_id_8]
           ,[diagnosis_code_lib_id_8]
           ,[source_product_id]
           ,[create_timestamp_tz]
           ,[modify_timestamp_tz]
           ,[rx_on_file_ind]
           ,[national_drug_code])
     VALUES
           (@pp_uniq_id
           ,@enterprise_id
           ,@practice_id
           ,@person_id
           ,@enc_id
           ,@serviceItemLibraryID
           ,''
           ,@serviceItem
           ,(select top 1 description from cpt4_code_mstr where cpt4_code_id = @cpt_code)
           ,@cpt_code
           ,(select convert(nvarchar(8), enc_timestamp, 112) from patient_encounter where enc_id = @enc_id)
           ,@provider_id
           ,''
           ,''
           ,@diag_code_1
           ,@lib_id_1
           ,@diag_code_2
           ,@lib_id_2
           ,@diag_code_3
           ,@lib_id_3
           ,@diag_code_4
           ,@lib_id_4
           ,''  --111  --!!!!!!!????????? [place_of_service]
           ,''
           ,1
           ,@location_id
           ,(select top 1 current_price from service_item_mstr where cpt4_code_id =  @cpt_code) --!!!!!!!????????? [amount]
           ,'N'
           ,''
           ,''
           ,''
           ,'N'
           ,''
           ,@user_id
           ,@user_id
           ,CURRENT_TIMESTAMP
           ,CURRENT_TIMESTAMP
           ,''
           ,''
           ,''
           ,''
           ,'N'
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,'EHR'
           ,0
           ,0
           ,'N'
           ,'')
           
		-- [LMA] Link to pending charges so EPM knows there are pending charges
		INSERT INTO pending_charge
			(practice_id, uniq_id, enc_id, create_timestamp, created_by, modify_timestamp, modified_by)
		VALUES
			(@practice_id, @pp_uniq_id, @enc_id, CURRENT_TIMESTAMP, @user_id, CURRENT_TIMESTAMP, @user_id); 

END

Open in new window


Here is what a sample set of data from the ##ICDs table looks like

row_num	dx_code
1	427.32
2	250.40
3	255.9
4	428.0
5	402.9
6	280.9
7	493.20
8	V49.72
9	535.50
10	583.81
11	153.9
12	287.50
13	300.00
14	252.00
15	413.9
16	278.00
17	274.9
18	440.1

Open in new window


When running the stored procedure there is an error when it runs the INSERT INTO [dbo].[patient_procedure] portion.

When the @offset variable has a value of 3 the codes that correspond to rows 9,10,11, and 12 in the ##ICDs table get inserted as 'null' values and i'm not entirely sure why this is happening.

Could it possibly be a problem with the logic here that is pulling the dx_code into the variable, perhaps a math or rounding error on the (@offset * 4) - 3 portion?

set @diag_code_1 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -3)

Open in new window

0
Comment
Question by:robthomas09
[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
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39889265
Most likely there is no matching data for the statement that generates the value to be inserted on that row/column wherever you populate that global temp table ##ICDs
0
 

Author Comment

by:robthomas09
ID: 39889392
Hi lcohan,

If i'm understanding correctly your saying that the problem lies with the ##ICDs table not getting populated with data? The ##ICDs temp table is getting populated with the result set that i posted above.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39889409
IF @offset is NULL, (@offset * 4) -3 is NULL; therefore, I also would check that your @offset parameter/variable is set as you expected.

Maybe it should default?  (COALESCE(@offset, 1) * 4) -3
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

632 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