July 8, 2014 at 7:40 am
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*
fromdbo.Callback_6cb
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
select*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
select*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
select*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
select*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
select*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
JOINProject.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*
fromdbo.Callback_6cb
JOINdbo.Phone ph ON ph.Call_No = cb.Call_No
JOINdbo.Personal per ON per.Call_no = ph.Call_No
JOINdbo.Category_6 ct ON ct.nId = per.nCat_Id
JOINdbo.r_callerrc on rc.call_no = ph.Call_No
JOINdbo.callerc on c.Caller_No = rc.Caller_No
cross apply dbo.CallbackService_ufn_GetCallerData (c.Caller_No, per.Language)
JOINProject.dbo.ufn_DelimitedStringToTable ('51961', '|') as t on CONVERT(integer, cRow) = cb.call_no
July 8, 2014 at 7:50 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2014 at 7:55 am
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".
July 8, 2014 at 7:59 am
The function CallbackService_ufn_GetCallerData throws an error?
What's the definition of the function?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2014 at 8:03 am
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_NoArgINTEGER,
@cCallerLangArgCHAR(1))
RETURNS @tRS TABLE (Caller_NoINTEGERNOT NULL,
ClientnameVARCHAR(64)NOT NULL,
ClientLanguageVARCHAR(16)NOT NULL,
ClientOrganizationVARCHAR(40)NOT NULL,
ClientAddressLine1VARCHAR(60)NOT NULL,
ClientAddressLine2VARCHAR(70)NOT NULL,
ClientAddressLine3VARCHAR(48)NOT NULL,
ClientTelephone1VARCHAR(20)NOT NULL,
ClientTelephone1ExtVARCHAR(5)NOT NULL,
ClientTelephone2VARCHAR(20)NOT NULL,
ClientTelephone2ExtVARCHAR(5)NOT NULL,
ClientEmailVARCHAR(100)NOT NULL)
AS
BEGIN
INSERT@tRS
SELECT@nCaller_NoArg,
RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name)),
CASEWHEN @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)) + ', ' +
CASEWHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(c.ps_prov_code)))
ELSE UPPER(RTRIM(LTRIM(c.ps_prov_code)))
END + ' ' +
RTRIM(LTRIM(CASEWHEN 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(CASEWHEN @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)), '')
FROMdbo.Callerc
JOINdbo.LookuplON l.lookup_ch = @cCallerLangArg
AND l.Item_name = 'Language'
LEFT OUTER JOINdbo.ProvincepON p.ps_Prov_Code = c.ps_Prov_Code
LEFT OUTER JOINdbo.CountryctON ct.ps_country_code = c.Country
WHEREc.Caller_No = @nCaller_NoArg
RETURN
END
GO
July 8, 2014 at 8:08 am
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,''))
July 8, 2014 at 8:12 am
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.
July 8, 2014 at 8:40 am
Is the column size for Clientname VARCHAR(64) enough ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 8, 2014 at 8:45 am
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.
July 8, 2014 at 9:45 am
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?
July 8, 2014 at 9:56 am
FWIW, Thinking it might have been generating an n x 1 CROSS JOIN, I moved the JOIN dbo.lookup out of the select statement in the function, since it wasn't actually JOINing the other tables. This had no effect.
July 9, 2014 at 5:38 am
So it turns out that in the 2 failing scenarios, the function is going through the full table before applying the WHERE clause, and failing on a row where first name or last name is null.
In the working scenarios, the WHERE clause is applied first, and only one row is acted on.
So am I to conclude that order does matter in the FROM clause?
July 10, 2014 at 4:41 am
schleep (7/9/2014)
So it turns out that in the 2 failing scenarios, the function is going through the full table before applying the WHERE clause, and failing on a row where first name or last name is null.In the working scenarios, the WHERE clause is applied first, and only one row is acted on.
So am I to conclude that order does matter in the FROM clause?
The execution plans for the working queries and the non-working queries are obviously different. You can't really jump to any conclusions without examining them.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2014 at 5:17 am
-- You might want to configure that function as an iTVF for performance:
CREATE FUNCTION [dbo].[iTVF_CallbackService_ufn_GetCallerData]
(
@nCaller_NoArgINTEGER,
@cCallerLangArgCHAR(1)
)
RETURNS TABLE
AS
RETURN (
SELECT
[Caller_No]= @nCaller_NoArg,
[Clientname]= RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name)),
[ClientLanguage]= CASE
WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(l.Descript_f)))
ELSE UPPER(RTRIM(LTRIM(l.Descript)))
END,
[ClientOrganization]= ISNULL(RTRIM(LTRIM(c.Organization)), ''),
[ClientAddressLine1]= ISNULL(RTRIM(LTRIM(c.Street)), ''),
[ClientAddressLine2]= ISNULL(RTRIM(LTRIM(c.City)) + ', ' + CASE
WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(c.ps_prov_code))) -- c.ps_prov_code_f???
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)), ''),
[ClientAddressLine3]= ISNULL(CASE
WHEN @cCallerLangArg = 'F'
THEN UPPER(RTRIM(LTRIM(s_country_name_f)))
ELSE UPPER(RTRIM(LTRIM(s_country_name)))
END, ''),
[ClientTelephone1]= ISNULL(RTRIM(LTRIM(c.Telephone)), ''),
[ClientTelephone1Ext]= ISNULL(RTRIM(LTRIM(c.Extension)), ''),
[ClientTelephone2]= ISNULL(RTRIM(LTRIM(c.Night_Phone)), ''),
[ClientTelephone2Ext]= '',
[ClientEmail]= ISNULL(RTRIM(LTRIM(c.s_Email)), '')
FROM dbo.[Caller] c
CROSS JOIN dbo.[Lookup] l
LEFT OUTER JOINdbo.Province p
ON p.ps_Prov_Code = c.ps_Prov_Code
LEFT OUTER JOINdbo.Countryct
ON ct.ps_country_code = c.Country
WHERE c.Caller_No = @nCaller_NoArg
AND l.lookup_ch = @cCallerLangArg
AND l.Item_name = 'Language'
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2014 at 7:35 am
Hi Chris,
Yep, that was my conclusion too. However, since query fails, I couldn't see the execution plan. Unless there's a way?
And, the iTVF would probably neatly obviate the original NULL value problem, too!
Thanks!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply