Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Strange behaviour Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 7:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
Can someone explain why only the last 2 versions of this query fail (in the cross applied function)? I'm mystified.

The only thing that changes is the placement of the JOIN Project.dbo.... line.


select	* 
from dbo.Callback_6 cb
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)

select *
from dbo.Callback_6 cb
JOIN dbo.Phone ph ON ph.Call_No = cb.Call_No
JOIN dbo.Personal per ON per.Call_no = ph.Call_No
JOIN dbo.Category_6 ct ON ct.nId = per.nCat_Id
JOIN dbo.r_caller rc on rc.call_no = ph.Call_No
JOIN dbo.caller c on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
JOIN Project.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no




Post #1590359
Posted Tuesday, July 8, 2014 7:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,702, Visits: 37,154
Fail as in throw an error? Fail as in give incorrect results? Fail as in the server self-destructs?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1590364
Posted Tuesday, July 8, 2014 7:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
Lol! Fail as in throws an error. (But for my users, it's the End. Of. The. World.)
The cross apply returns a table, but the table has the columns defined not null, and the error is "Unable to insert a NULL value in column xxx in table yyy".




Post #1590367
Posted Tuesday, July 8, 2014 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,702, Visits: 37,154
The function CallbackService_ufn_GetCallerData throws an error?
What's the definition of the function?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1590371
Posted Tuesday, July 8, 2014 8:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
Function code below.

Specifically, the error thrown is:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Clientname', table '@tRS'; column does not allow nulls. INSERT fails.
The statement has been terminated.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CallbackService_ufn_GetCallerData]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CallbackService_ufn_GetCallerData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[CallbackService_ufn_GetCallerData]
(@nCaller_NoArg INTEGER,
@cCallerLangArg CHAR(1))

RETURNS @tRS TABLE ( Caller_No INTEGER NOT NULL,
Clientname VARCHAR(64) NOT NULL,
ClientLanguage VARCHAR(16) NOT NULL,
ClientOrganization VARCHAR(40) NOT NULL,
ClientAddressLine1 VARCHAR(60) NOT NULL,
ClientAddressLine2 VARCHAR(70) NOT NULL,
ClientAddressLine3 VARCHAR(48) NOT NULL,
ClientTelephone1 VARCHAR(20) NOT NULL,
ClientTelephone1Ext VARCHAR(5) NOT NULL,
ClientTelephone2 VARCHAR(20) NOT NULL,
ClientTelephone2Ext VARCHAR(5) NOT NULL,
ClientEmail VARCHAR(100) NOT NULL)
AS
BEGIN
INSERT @tRS

SELECT @nCaller_NoArg,
RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name)),
CASE WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(l.Descript_f)))
ELSE UPPER(RTRIM(LTRIM(l.Descript)))
END,
ISNULL(RTRIM(LTRIM(c.Organization)), ''),
ISNULL(RTRIM(LTRIM(c.Street)), ''),
ISNULL(RTRIM(LTRIM(c.City)) + ', ' +
CASE WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(c.ps_prov_code)))
ELSE UPPER(RTRIM(LTRIM(c.ps_prov_code)))
END + ' ' +
RTRIM(LTRIM( CASE WHEN c.Postcode LIKE '[A-Z][0-9][A-Z][0-9][A-Z][0-9]'
THEN LEFT(c.PostCode, 3) + ' ' + RIGHT(c.PostCode, 3)
ELSE c.PostCode
END)), ''),
ISNULL(CASE WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(s_country_name_f)))
ELSE UPPER(RTRIM(LTRIM(s_country_name)))
END, ''),
ISNULL(RTRIM(LTRIM(c.Telephone)), ''),
ISNULL(RTRIM(LTRIM(c.Extension)), ''),
ISNULL(RTRIM(LTRIM(c.Night_Phone)), ''),
'',
ISNULL(RTRIM(LTRIM(c.s_Email)), '')
FROM dbo.Caller c
JOIN dbo.Lookup l ON l.lookup_ch = @cCallerLangArg
AND l.Item_name = 'Language'
LEFT OUTER JOIN dbo.Province p ON p.ps_Prov_Code = c.ps_Prov_Code
LEFT OUTER JOIN dbo.Country ct ON ct.ps_country_code = c.Country
WHERE c.Caller_No = @nCaller_NoArg

RETURN
END

GO




Post #1590374
Posted Tuesday, July 8, 2014 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 2,485, Visits: 3,032
Keep in mind that when one of the parts is NULL, the combined string becomes NULL. It looks like you have one or more rows where "c.First_Name" or "c.Last_Name" is a NULL value.

Try changing the code:
RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name))
to:
RTRIM(LTRIM(ISNULL(c.First_Name, ''))) + ' ' + RTRIM(LTRIM(ISNULL(c.Last_Name,''))


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1590378
Posted Tuesday, July 8, 2014 8:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
That's not the problem, HanShi. The first name and last names are not null in this instance, and if you look at the original post, you'll note the code works in 5 of 7 formulations.




Post #1590382
Posted Tuesday, July 8, 2014 8:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Is the column size for Clientname VARCHAR(64) enough ?
Post #1590396
Posted Tuesday, July 8, 2014 8:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
Once again, that's not the problem: call that function directly with the single value -- and there is only one single value in this example -- and it works just fine. For the record, first name field is 15, last name field is 30.

I want to understand how the placement of a JOIN (see original post) causes the call to the function to throw an error.



Post #1590400
Posted Tuesday, July 8, 2014 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 439, Visits: 1,017
Now, when I change the definition of that column in the table function to allow NULLs, it works!
And the last 2 versions which previously threw the error do NOT return NULL in that column, they return the data as expected.

It's as if it's trying to fully populate the function table first -- some first name / last names are NULL -- then filter by the WHERE clause... But why on only the last 2 formulations of what *should* be identical queries?





Post #1590419
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse