Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query Help Needed - column does not allow nulls

Posted on 2012-08-21
3
Medium Priority
?
1,865 Views
Last Modified: 2012-08-21
I am getting the following error on my below mentioned query. Any assistance is appreciated.

Msg 515, Level 16, State 2, Line 50
Cannot insert the value NULL into column 'InsBalance', table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________000000000018'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Line 86
Cannot insert the value NULL into column 'InsBalance', table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________000000000018'; column does not allow nulls. INSERT fails.
The statement has been terminated.


SET NOCOUNT ON

DECLARE
    @startdate DATETIME ,
    @enddate DATETIME

IF 1 = 1 
    BEGIN -- Date mode
        SET @startdate = ISNULL(NULL , '1/1/1900') 
        SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))
    END
ELSE 
    BEGIN  --month mode
        SET @startdate = ISNULL(NULL , '1/1/1900') 
        SET @enddate = DATEADD(month , 1 , ISNULL(NULL , '1/1/3000'))
    END

--Gather the charges
SELECT
    pv.PatientVisitId ,
    pp.PatientId ,
    pp.Last,
    pp.First,
    pv.TicketNumber, 
    dr.Listname AS Doctor ,
    pvp.TotalFee AS Charges ,
    CAST('0.00' AS MONEY) AS Payments ,
    CAST('0.00' AS MONEY) AS Adjustments ,
    pva.InsBalance, 
    pva.PatBalance, 
    pva.InsBalance + pva.PatBalance AS VisitBalance 
INTO
    #temp
FROM
    PatientVisit pv
    INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
    INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
    INNER JOIN PatientProfile pp ON pva.PatientProfileId = pp.PatientProfileId
    INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
    INNER JOIN Procedures p ON pvp.proceduresID = p.proceduresID
    INNER JOIN Batch b ON pvp.BatchId = b.BatchId
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE
    pvp.TotalFee <> 0
    AND b.Entry >= @StartDate
    AND b.Entry < @endDate


-- Insert Payments
INSERT  INTO #temp
        (
          PatientVisitId ,
          PatientId ,
          Last, 
          First, 
          TicketNumber, 
          Doctor ,
          Charges ,
          Payments 
        )
        
        SELECT
            pv.PatientVisitId ,
            pp.PatientId ,
            pp.Last, 
            pp.First, 
            pv.TicketNumber, 
            dr.ListName AS Doctor ,
            NULL AS Charges ,
            vt.Payments 
            
        FROM
            PaymentMethod pm
            INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
            INNER JOIN Batch b ON pm.BatchId = b.BatchId
            INNER JOIN PatientVisit pv ON pv.PatientVisitid = vt.PatientVisitId
            INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
            INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
            LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
        WHERE
            b.Entry >= @StartDate
            AND b.Entry < @endDate
            AND vt.Payments <> 0
                    
--Insert Adjustments
INSERT  INTO #temp
        (
          PatientVisitId ,
          PatientId ,
          Last, 
          First, 
          TicketNumber, 
          Doctor ,
          Charges ,
          Adjustments 	
        )
        
        SELECT
            pv.PatientVisitId ,
            pp.PatientId ,
            pp.Last, 
            pp.First, 
            pv.TicketNumber, 
            dr.ListName AS Doctor ,
            NULL AS Charges ,
            vt.Adjustments 
            
        FROM
            PaymentMethod pm
            INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
            INNER JOIN Batch b ON pm.BatchId = b.BatchId
            INNER JOIN PatientVisit pv ON vt.PatientVisitid = pv.PatientVisitId
            INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
            INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
            LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
        WHERE
            b.Entry >= @StartDate
            AND b.Entry < @endDate
            AND vt.Adjustments <> 0

--- Sum rows for total 

SELECT 
	PatientVisitId, 
	PatientId, 
	Last, 
	First, 
	TicketNumber,
	Doctor, 
	ISNULL(SUM(Charges),0) AS Charges,  
	ISNULL(SUM(Payments),0) AS Payments,  
	ISNULL(SUM(Adjustments),0) AS Adjustments, 
	InsBalance, 
	PatBalance, 
	VisitBalance
FROM 
	#temp 
	
GROUP BY
	PatientVisitId, 
	PatientId, 
	Last, 
	First, 
	TicketNumber,
	Doctor, 
	InsBalance, 
	PatBalance, 
	VisitBalance	
	
ORDER BY 
	3,
	5	

DROP TABLE #temp

Open in new window

0
Comment
Question by:Jeff S
3 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 38316379
There is a restriction on the receiving table that the column does not allow nulls, but NULL is being generated (or read) as part of the SELECT statement.

You'll need to default the values.  Zero perhaps?  If so, change lines 29 - 31 to something like this:

    coalesce (pva.InsBalance, 0),
    coalesce (pva.PatBalance, 0),
    coalesce (pva.InsBalance, 0) + coalesce (pva.PatBalance, 0) AS VisitBalance

That assumes that if either InsBalance or PatBalance is NULL, VisitBalance should acquire the value of the other field.  If not, the last line should be:

    coalesce (pva.InsBalance + pva.PatBalance, 0) as VisitBalance


Good Luck,
Kent
0
 
LVL 72

Expert Comment

by:Qlemo
ID: 38316391
Your SELECT ... INTO #temp seems to determine that the column InsBalance does not contain  NULLs, so the column is defined as NOT NULL. The following INSERTs do not fill that column, and as it has no default, an error is thrown.

You can
a) add a default value for #temp.InsBalance after creating the table (alter table #temp add default 0 for InsBalance)
b) include InsBalance with 0 in each INSERT
c) alter the column to be nullable - alter table #temp alter column InsBalance float null
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 38316395
thanks, I got same conclussion. Didnt see your response when I tried to delete. Please award Kent. Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

572 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