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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.