Left Join Scalar function

Can someone disect the join statement below and provide me with an explanation of the syntax?    dbo.smr_Cost_ID is a Scalar function.  I am unsure what the values nested in the parenthesis () are doing.    r.store_id, r.v_id,r.vi_id are all apparently aliased parameters in the function.  ip_start date is a field in a view referenced in the select statement.



 LEFT JOIN tciinstore.dbo.VENDOR_COST vc ON
              vc.VENDOR_COST_ID = dbo.SMR_COST_ID( (CASE WHEN r.store_id = 0 then 142 ELSE r.store_id END),  r.V_ID, r.vi_id, ip_start_date)
tike55Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
the function is finding an ID

it is using data elements  r.store_id, r.v_id, r.vi_id  and ip_start date as parameters for the function to use to find the ID

if you look at the full "FROM" you will find one of the tables (or derived tables) is given "r" as its alias

using a function is not a great use as a predicate for the join.

Because the function itself does not rely on the table  tciinstore.dbo.VENDOR_COST  you could include the use of the function in a select statement to see what it is trying to find
select r.store_id
       , r.V_ID
       , r.VI_ID
       ,  ip_start_date
       , dbo.SMR_COST_ID( (CASE WHEN r.store_id = 0 then 142 ELSE r.store_id END),  r.V_ID, r.vi_id, ip_start_date)
from some_table r

Open in new window

and when store_id is 0 the function is told to use 142 instead of 0, if not 0, then it will use store_id

So, all the function is doing, is trying to return an ID that matches VENDOR_COST_ID from the tciinstore.dbo.VENDOR_COST table, and needs 4 parameters to find an ID. It is a left join because there might not be a matching ID.

does that make sense ?
1
 
_agx_Commented:
It looks a function that accepts 4 parameters. All of the parameters appear to be columns in some other table or view in your JOIN.  (Look for another table/view aliased with "r.")

1st parameter:  (CASE WHEN r.store_id = 0 then 142 ELSE r.store_id END)
Translation:  Basically an if/else statement that says:   if the current value in column r.store_id equals 0, then pass in the value "142". Otherwise, use the current value of r.store_id

2nd parameter: r.V_ID
Pass in the current value in the r.V_ID column

3rd parameter:  r.vi_id
Pass in the current value in the r.vi_id  column

4th parameter: ip_start_date
Pass in the current value in the ip_start_date
1
 
_agx_Commented:
Here's an example to help visualize how it works

Demo Function (only echoes input parameters)
CREATE FUNCTION dbo.SMR_COST_ID
(
	@store_id INT
	, @V_ID INT
	, @vi_id INT
	, @ip_start_date DATE
)
RETURNS VARCHAR(100)
AS
BEGIN

	RETURN 'Values entered: '
			+ CAST(@store_id AS VARCHAR)
			+'/'+ CAST(@V_ID AS VARCHAR)
			+'/'+ CAST(@vi_id AS VARCHAR)
			+'/'+ CAST(@ip_start_date AS VARCHAR)

END
GO

Open in new window


Test query:
SELECT r.store_id
		, r.v_id
		, r.vi_id
		, ip_start_date
		, dbo.SMR_COST_ID( (CASE WHEN r.store_id = 0 then 142 ELSE r.store_id END),  r.V_ID, r.vi_id, ip_start_date) AS Result
FROM   (
			SELECT 0 AS store_id, 111 AS v_id, 222 AS vi_id, CAST(GETDATE() AS DATE) AS ip_start_date
			UNION ALL	
			SELECT 444 AS store_id, 111 AS v_id, 222 AS v_id, CAST(GETDATE() AS DATE) AS ip_start_date
	    ) r 

Open in new window


Results:
store_id	v_id	   vi_id	ip_start_date	 Result
0	        111	   222	2018-04-20	 Values entered: 142/111/222/2018-04-20
444	        111	   222	2018-04-20	 Values entered: 444/111/222/2018-04-20

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.