question

ASHMITP-0361 avatar image
0 Votes"
ASHMITP-0361 asked MelissaMa-msft commented

stored proc error

Hi there,

Anyone pls suggest why I am getting an error like below:

My Sp script is like below :

USE DB

Create PROCEDURE [dbo].[sp_MakeFact] AS

SET NOCOUNT ON;

DROP TABLE IF EXISTS AmberCare;

WITH AmberCare AS (
select * from table)

SELECT
AmberCare.[FACILITY]
,AmberCare.[MRN]
,AmberCare.[AUID]
,1 as Measure_AmberCare
,0 as Measure_Advancecare
INTO final_table
FROM AmberCare
LEFT JOIN
[APU_BI].[dbo].[VIEW_EPISODE_MASTER] EM
ON EM.stay_number = AmberCare.visit_id
AND EM.facility_identifier = AmberCare.Facility_identifier;

DROP TABLE IF EXISTS Advancecare;

WITH AmberCare AS (
select * from table)


SELECT
AdvanceCare.[FACILITY]
,AdvanceCare.[MRN]
,AdvanceCare.[AUID]
,0 as Measure_Ambercare
,1 as Measure_Advancecare
INTO final_table
FROM Advancecare
LEFT JOIN
[VIEW] EM
ON EM.stay_number = Advancecare.visit_id
AND EM.facility_identifier = Advancecare.Facility_identifier
AND ADVANCE_CARE_DIRECTIVE='YES';

when exec the sp I am getting below error:

98428-cap.png


sql-server-transact-sql
cap.png (2.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ASHMITP-0361,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ASHMITP-0361,

Welcome to Microsoft Q&A!

Please also provide the details of [VIEW] which may cause this warning. There could be one aggregation on one column which has null values in your [VIEW].

We could disable this warning by setting ansi_warnings off but this may cause other effects or cause failures when your queries use features like indexed views, computed columns or XML methods.

In some cases you could rewrite the aggregate to avoid it.

For example, COUNT(nullable_column) could be rewritten as SUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END) or SUM(IIF(nullable_column IS NULL,0 ,1) but this isn't always possible to do straightforwardly without changing the semantics.

You could refer below example:

 select count(id) from (
 select 1 id
 union
 select NULL) a

Warning: Null value is eliminated by an aggregate or other SET operation.

We could rewrite it as below:

 select sum(IIF(id is null,0,1)) from (
 select 1 id
 union
 select NULL) a

Output:
1

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

As the message indicates, this is a warning, not an error. What it is telling you is that this query as one or more aggregate function (for example COUNT() or SUM()) and one or more of the values being aggregated is NULL. When that happens, the NULL values are not included in the aggregate. For example, if you do

 Declare @T Table(i int);
 Insert @T(i) Values (1), (Null), (3);
 Select COUNT(i) As TheCount, SUM(i) As TheSum From @T;

the COUNT will show as 2, the SUM as 4 and you will get the warning message because the NULL value is not included in either the COUNT or the SUM.

But if you do

 Declare @T Table(i int);
 Insert @T(i) Values (1), (Null), (3);
 Select COUNT(IsNull(i, 0)) As TheCount, SUM(IsNull(i, 0)) As TheSum From @T;

Then the COUNT will be 3 and the sum will be 4 and you will not get the warning message.

Tom


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

This is not an error message, but an informational message mandated by the ANSI standard. Treat it as white noise. Occasionally, you can get rid of it by adjusting the query, but most of the time it is not worth the effort.

And most emphatically, never use SET ANSI_WARNINGS OFF!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.