UDF SQL Function

I have a function created to calculate pricing on parts

I'm trying to run the function as a cross apply to my item file and using each item as a parameter in my function when it is called.

I'm receiving an error when i attempt to run the select statement.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

What am I doing wrong with my syntax?

select iv1.Itemnmbr
    , iv1.tcc
    , iv1.itemdesc
    , pl.listprice
from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
cross apply dbo.ufnGetACCTPricing(iv1.itemnmbr, '') pl
--on pl.itemnmbr=iv1.Itemnmbr
where iv1.itemtype <>'2'
    and iv1.itmclscd<>'NON-IV'
    and iv1.itemcode='ACTIVE_ALL'

Please not that the connection to the part master list is using a linked server

Thanks
jdr0606Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Multi-line table-valued functions always perform relatively poorly.  For any real performance, you need to convert it to an in-line table-valued function.  

I've done the conversion in the code below; it should at least be very close.  You invoke the function using CROSS APPLY in exactly the same way you do a multi-line function, you just get much better overall performance.


CREATE FUNCTION [dbo].[udfGetCUSTtPricing]
(
    @itemnmbr varchar(31),  
    @custnmbr varchar(15)
)
RETURNS TABLE 
AS
RETURN (
WITH cte_get_defaults AS (
    SELECT 
        RSKDiff = MAX(CASE WHEN Description = 'RSK' THEN value END),
        DefaultDiff = MAX(CASE WHEN Description = 'DEFAULT' THEN value END),
        OBOXDiff = MAX(CASE WHEN Description = 'OBOX' THEN value END),
        Override = MAX(CASE WHEN Description = 'OVERRIDE' THEN value END),
        NetPrice = MAX(CASE WHEN Description = 'NETPRICE' THEN value END),
        NonStockDiff = MAX(CASE WHEN Description='NONSTOCKDIFF' THEN value END),
        RSKCode = MAX(CASE WHEN Description='RSKCode' THEN value END)
    FROM dynamics_ext.dbo.udtCUSTDefault
)
    SELECT
        @custnmbr,
        @itemnmbr,
        ListPrice,
        TrueSEPrice,
        SEPrice
    FROM tempload.dbo.iv00101_Testing iv1
    CROSS APPLY cte_get_defaults
    LEFT JOIN tempload.dbo.eiv00600_Testing eiv6 on eiv6.itemnmbr = iv1.itemnmbr
    LEFT JOIN dynamics_ext.dbo.udtPartnerPricing_testing partner on partner.tcc = iv1.tcc
    LEFT JOIN tempload.dbo.eiv00800_Testing eiv8 on eiv8.itemnmbr = iv1.itemnmbr and 
        eiv8.expndate > getdate() and 
        eiv8.custnmbr = @custnmbr
    LEFT JOIN keleprd.mis.dbo.udtSE_Xref xref on xref.itemnmbr = iv1.itemnmbr
    LEFT JOIN keleg.dbo.rm00101 rm1 on rm1.custnmbr = @custnmbr
    LEFT JOIN tempload.dbo.eiv00700_Testing eiv7 on eiv7.pricegroup = iv1.pricegroup and 
        eiv7.prclevel = rm1.prclevel
    CROSS APPLY (
        SELECT 
        ListPrice = 	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2 else eiv6.listpricebasis_1 end)  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end)
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 -- OBOX
		  else eiv6.listpricebasis_1 end
 
        , TrueSEPrice =  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3 * NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)* partner.Differential  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode = RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode <> RSKCode then (ivlog.unitprice/eiv7.multiplier_1)* NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 * DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 * DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 * DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 * DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 * DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 * DefaultDiff end)

        , SEPrice=	-- This is the price that CUST pays Kele
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3 * NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end) *partner.Differential
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode = RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1*DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2*DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3*DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4*DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5*DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost
		  else eiv6.listpricebasis_1*DefaultDiff end)

        , CustomerPrice=  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then 
				eiv6.listpricebasis_2*cast(right(rtrim(rm1.prclevel),3) as numeric(9,3))/1000  -- Sale Not SBUY - CUST Only
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2*eiv7.multiplier_1-- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)*eiv7.multiplier_1  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode = RSKCode then ivlog.unitprice   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode <> RSKCode then ivlog.unitprice   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 *eiv7.multiplier_1 -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 *eiv7.multiplier_1 end)

        , OveridePrice=  -- overrides
	      (case when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) 
                then eiv8.multiplierval * Override else 0 end)
    ) AS alias1

    LEFT JOIN dynamics_ext.dbo.udtNonInventoryLog ivlog on ivlog.NonInventoryID = iv1.itemnmbr and 
        ivlog.vendorcode = RSKCode

    WHERE iv1.itemtype IN ('1','3')
    AND iv1.tcc not like '%promo%'
    AND iv1.tcc NOT LIKE '*%'
    AND iv1.itemnmbr NOT LIKE '%promo%'
    AND iv1.itemdesc NOT LIKE '%promo%'
    AND iv1.itemcode <> 'INTERNAL_ONLY'
    AND iv1.itemcode <> 'GP_ONLY'
    AND iv1.tcc = @itemnmbr
)

Open in new window

0
 
_agx_Commented:
EDIT: Is this a scalar function? If yes, try changing the FROM clause to use this syntax:

FROM  gp9prd.cmp.dbo.iv00101 iv1 (nolock)
              CROSS APPLY (select dbo.ufnGetACCTPricing(iv1.itemnmbr, '')) AS pl(listprice)

... or remove the CROSS APPLY and use it within the SELECT

SELECT iv1.Itemnmbr
    , iv1.tcc
    , iv1.itemdesc
    , dbo.ufnGetACCTPricing(iv1.itemnmbr, '')  AS ListPrice
FROM  gp9prd.cmp.dbo.iv00101 iv1 (nolock)

Open in new window

0
 
jdr0606Author Commented:
Sorry this is a tabular function
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark WillsTopic AdvisorCommented:
Your query looks OK, so it is probably with the function

Might need to see the function.

Could well be the second param being a zero length string causing problems

what happens if you do :  SELECT * FROM dbo.ufnGetACCTPricing('put an itemnumber in here', '')

And being a linked server, your function needs (internally) the 4 part name identifier - linkedserver.dbname.schema.table which seems to be ok as far as : gp9prd.cmp.dbo.iv00101
0
 
jdr0606Author Commented:
If I do a SELECT * FROM dbo.ufnGetACCTPricing('#2001', '')  it works just fine
if i do the below, it works fine
select iv1.Itemnmbr
    , iv1.tcc
    , iv1.itemdesc
    , pl.listprice
from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
cross apply dbo.ufnGetACCTPricing('#2001', '') pl
--on pl.itemnmbr=iv1.Itemnmbr
where iv1.itemtype <>'2'
    and iv1.itmclscd<>'NON-IV'
    and iv1.itemcode='ACTIVE_ALL'

I get the error only when i use the iv1.itemnmbr (from the gp9prd.cmp.dbo.iv00101) as a parameter


BTW Mark, this is the function that you helped me create earlier in the day.
0
 
Mark WillsTopic AdvisorCommented:
Thought it looked familiar :)

Then maybe the function needs more error checking because it would seem that itemnmbr's might have (might be) generating errors / values that we are not catering for when inserting into the table...

And given the function RETURNS @CustomerPriceTable TABLE with a primary key on Custnmbr, why isnt there a second parameter ?

How many rows are returned when you hardcode the itemnmbr '#2001'

Can we check the itemnmbr's returned ?
0
 
jdr0606Author Commented:
The way the function works, i can pass a part number and customer number or just the partnumber. as parameters for the function to process.

Part numbers are unique so if I just pass '#2001' I get one row of results.

Using a join or cross apply against the item master (iv00101) is intended to return a price calculated row for each part number in the iv00101.
0
 
ZberteocCommented:
Try this:
;with tbl as (
	select 
		 iv1.Itemnmbr
		,iv1.tcc
		,iv1.itemdesc
	from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
	where iv1.itemtype <>'2'
		and iv1.itmclscd<>'NON-IV'
		and iv1.itemcode='ACTIVE_ALL'
)
select
	 tbl.*
	,pl.listprice
from
	tbl t
	cross apply dbo.ufnGetACCTPricing(t.itemnmbr, '') pl

Open in new window

0
 
jdr0606Author Commented:
It's interesting that when I remove the linked server dependencies I don't have the syntax error.

Even what i works it is still slow to process so I do want to try your suggestion above and then look at optimizing the function. It does a great deal when the function is called and when I'm running against several hundred thousand items it takes a considerable amount of time.
0
 
ZberteocCommented:
Have you tried what I suggested?
0
 
Mark WillsTopic AdvisorCommented:
Well, the function does have a LOT in there - it was basically a "simple" convert of the stored procedure.

Given the complexity of building the columns, it will be difficult to optimise....

Will need to contemplate :)

Zbertoec's suggestion will remove the unwanted itemnmbrs first, so worth a try.
0
 
jdr0606Author Commented:
I did try the suggestion for Zbertoec and received and error message
     The column prefix 'tbl' does not match with a table name or alias name used in the query.

Just get back to my desk so haven't looked any further why I'm seeing the message
0
 
ZberteocCommented:
Sorry, my bad! A silly mistake! I aliased the tbl as t but then I still used tbl.* where I should have used t.*!Try this:
;with tbl as (
	select 
		 iv1.Itemnmbr
		,iv1.tcc
		,iv1.itemdesc
	from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
	where iv1.itemtype <>'2'
		and iv1.itmclscd<>'NON-IV'
		and iv1.itemcode='ACTIVE_ALL'
)
select
	 t.*
	,pl.listprice
from
	tbl t
	cross apply dbo.ufnGetACCTPricing(t.itemnmbr, '') pl

Open in new window

0
 
jdr0606Author Commented:
Zbertoec, that change did work unfortunately the performance is still not very good.

It took about 10 minutes just to process around 7,000 parts (the item master has several hundred thousand).

My next task is to look at optimizing within the function.

Zbertoec or Mark, since I'm using a number of case statements to filter results, is that the best method or do you recommend something different?

Thanks again for all your assistance.
0
 
jdr0606Author Commented:
I went back and reworked indexes on the tables that the function uses and get much better performance. I was able to process through 350K parts in about 15 minutes. Would normally think that was unacceptably slow but given all the work going on within the function I feel pretty good.

Any other advise you have?

Thanks again
0
 
Mark WillsTopic AdvisorCommented:
Will go through the code and have a look.
0
 
Mark WillsTopic AdvisorCommented:
Well there is a lot of checking which leads to the same result, and you could break it up depending on the type of pricing you are looking for.

And use the 'ELSE' condition as a catch-all - meaning you only need to check for the conditions that dont lead to ELSE eiv6.listpricebasis_1

By way of example, for listprice you could do
   Select
    @ListPrice = 	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  and eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2   -- CUST Special List
		  else eiv6.listpricebasis_1 end
   from tempload.dbo.iv00101_Testing iv1
   left join tempload.dbo.eiv00600_Testing eiv6 on eiv6.itemnmbr=iv1.itemnmbr
   left join dynamics_ext.dbo.udtPartnerPricing_testing partner on partner.tcc=iv1.tcc
   where iv1.itemtype in ('1','3')
   and iv1.tcc not like '%promo%'
   and iv1.tcc not like '*%'
   and iv1.itemnmbr not like '%promo%'
   and iv1.itemdesc not like '%promo%'
   and iv1.itemcode<>'INTERNAL_ONLY'
   and iv1.itemcode <>'GP_ONLY'
   and iv1.tcc =@itemnmbr

Open in new window

or using thatas a derived table, it would be interesting to try
;with tbl as (
	select 
		 iv1.Itemnmbr
		,iv1.tcc
		,iv1.itemdesc
	from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
	where iv1.itemtype <>'2'
		and iv1.itmclscd<>'NON-IV'
		and iv1.itemcode='ACTIVE_ALL'
)
select
	 t.*
	,pl.listprice
from
	tbl t
	cross apply ( Select
    ListPrice = 	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  and eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2   -- CUST Special List
		  else eiv6.listpricebasis_1 end
   from tempload.dbo.iv00101_Testing iv1
   left join tempload.dbo.eiv00600_Testing eiv6 on eiv6.itemnmbr=iv1.itemnmbr
   left join dynamics_ext.dbo.udtPartnerPricing_testing partner on partner.tcc=iv1.tcc
   where iv1.itemtype in ('1','3')
   and iv1.tcc not like '%promo%'
   and iv1.tcc not like '*%'
   and iv1.itemnmbr not like '%promo%'
   and iv1.itemdesc not like '%promo%'
   and iv1.itemcode<>'INTERNAL_ONLY'
   and iv1.itemcode <>'GP_ONLY'
   and iv1.tcc =t.itemnmbr) pl

Open in new window

there might be a few 'gotcha' moments in the code, but if it does run, then it would prove that we can modify the udf to cater more for the types of pricing you are after at any point...

Indexing must exist for itemnmbrs in tempload.dbo.iv00101_Testing,  tempload.dbo.eiv00600_Testing
and tcc in dynamics_ext.dbo.udtPartnerPricing_testing

Anyway, see if it runs and timing
0
 
ZberteocCommented:
Where did that code came from?
0
 
ZberteocCommented:
Anyway, the CASE is no issue at all if you use only columns from the joined tables, which you do, but taking out that code from CROSS APPLY and simply join it should be much faster:
;with tbl as (
	select 
		 iv1.Itemnmbr
		,iv1.tcc
		,iv1.itemdesc
	from gp9prd.cmp.dbo.iv00101 iv1 (nolock)
	where iv1.itemtype <>'2'
		and iv1.itmclscd<>'NON-IV'
		and iv1.itemcode='ACTIVE_ALL'
)
select
	t.*,
    ListPrice = 	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  and eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2   -- CUST Special List
		  else eiv6.listpricebasis_1 end
from
	tbl t
	inner join tempload.dbo.iv00101_Testing iv1
		on iv1.tcc =t.itemnmbr
	left join tempload.dbo.eiv00600_Testing eiv6 
		on eiv6.itemnmbr=iv1.itemnmbr
	left join dynamics_ext.dbo.udtPartnerPricing_testing partner 
		on partner.tcc=iv1.tcc
where iv1.itemtype in ('1','3')
   and iv1.tcc not like '%promo%'
   and iv1.tcc not like '*%'
   and iv1.itemnmbr not like '%promo%'
   and iv1.itemdesc not like '%promo%'
   and iv1.itemcode<>'INTERNAL_ONLY'
   and iv1.itemcode <>'GP_ONLY'

Open in new window

0
 
ZberteocCommented:
One thing, though. What will the be the value of the ListPrice if eiv6.listpricebasis_1 is null? You left join the tempload.dbo.eiv00600_Testing(eiv6) tsble so I would expect that it will not have corresponding rows in some cases.
0
 
Mark WillsTopic AdvisorCommented:
@zberteoc

The code came from the stored procedure that was converted to the function in a different question.

Did not want to select from that code because we want to test the responsiveness if we made special allowances within the function to collect (say) listprice. Hence retained using the cross apply. And maybe should be outer apply for items without a listprice to show with zero or capture within isnull().

Well, from the function as I know it......And should be in-line....
0
 
ZberteocCommented:
That's fine. However, I don't see the need to build a function for this especially if it returns only 1 row with one column. It should be a scalar function, if anything. But using the join directly should be the way.
0
 
Mark WillsTopic AdvisorCommented:
@zberteoc,

There are a lot of prices - currently returns half a dozen prices - one of which is list price. Until this thread, it was itemnmbr and customer as the two parameters to return @custnmbr, @itemnmbr,@listprice,@TrueSEPrice,@SEPrice,@CustomerPrice,@OveridePrice


You were a participant in that thread : https://www.experts-exchange.com/questions/29091294/Capturing-a-result-set-from-a-SPROC-call-from-within-SQL.html
0
 
ZberteocCommented:
Yes, but I didn't go into the details. I only showed how to grab results from a procedure into a table. Anyway, one price or more it is still waaay better to use the join rather then a function.
0
 
jdr0606Author Commented:
Great comments, I will try these suggestions and let you know what I find!
0
 
ZberteocCommented:
If you go that way then what is the reason to keep that function instead of actually using the code inside and join it in the "normal" way?
0
 
Scott PletcherSenior DBACommented:
The typical reason to use functions: to isolate the code and logic in one place, including not having to use the code and the logic in other place(s) in code.

In-line functions are great for that, because the performance isn't really any worse than coding it directly.

Multi-line functions, on the other hand, are known to cause performance issues for any reasonably large volume of use.
0
 
ZberteocCommented:
To isolate any code in a function only makes sense if that object will be then over and over be used in multiple places but if that function was built only to serve this particular task then is not needed and the performance impact of joining "normally" the tables involved would be still better then use a function with cross apply.
0
 
Scott PletcherSenior DBACommented:
An in-line function is "compiled into" the code anyway, it's not really that different than coding it directly in the query.

For such complex logic and joins, I would definitely isolate it in a function.  If nothing else, just the ease of finding it, and being sure which logic relates to account pricing and that only.  

If you had other logic around this in a combined query, and then you do need the ufnGetACCTPricing logic in a different place, how do you know exactly which code is for ufnGetACCTPricing only and which code is part of different logic?

If it's my choice, I isolate it in a function.  You can then also tune the function without regard to any other element of the main query.  And if/as the main query becomes more complex, it is still isolated from the function logic.
1
 
Mark WillsTopic AdvisorCommented:
I am still wanting to hear from JDR0606 if list price is as expected.

There are lots of things we can do, including splitting out list price, partner pricing, customer pricing etc etc.

The pricing was a stored procedure over at  https://www.experts-exchange.com/questions/29091294/Capturing-a-result-set-from-a-SPROC-call-from-within-SQL.html

the request was to turn it into a table valued function. and agree in-line would be better.

this question is a progression from that previous thread. I extracted one aspect of the code, changed it quite a bit (selecting from 3 tables instead of half a dozen - etc).

the idea of join v function et al is a little bit moot at this stage as to how to improve and where to improve a result set - for a start, it wasnt known that having a blank customer code implied list prices until this thread.

I strongly suggest we wait for feedback first because we simply dont know if the code provided so far will give the listprices

the components of the full stored proc / function (which Scott has obviously seen) revolve around @custnmbr, @itemnmbr, @listprice, @TrueSEPrice, @SEPrice, @CustomerPrice, @OveridePrice. And we have partner pricing to compound listprice. The performance and errors attached to getting just list price across the linked server is woeful at this stage....

it could well be a combination of list price as a join, and customerpricing as a function, or, any number of possibilities. What will be needed is rationalising those gigantuan case functions....
0
 
Scott PletcherSenior DBACommented:
Whatever logic is needed, over any reasonably large volume of data, an in-line function will vastly outperform a multi-line function (or a proc).

And, yes, as I agreed above, once we get the proper code isolated into an in-line function, we can continue to tune it -- or adjust it for accuracy -- as necessary.
0
 
jdr0606Author Commented:
I took the previous suggestions to the point where the results were returning as expected.

At that point it ran through several hundred thousand rows but took about 15 minutes.

I tried the suggestion this morning from Scott and the results were the same but the performance was significantly better.

To address the question of using a function, this will be used by multiple processes and achieves a single source of pricing in turn minimizing code changes whenever the logic needs to be changed.

At this point I believe we have achieved the correct results with the best performance.

Thanks to all in this thread, a huge success!!
0
 
Mark WillsTopic AdvisorCommented:
Great to hear.

A little surprised with all the extra joins that it was quicker than a straight select because it was bypassing the individual selects up front and bypassing all the work with
   left join tempload.dbo.eiv00800_Testing eiv8 on eiv8.itemnmbr=iv1.itemnmbr and eiv8.expndate>getdate() and eiv8.custnmbr=@custnmbr
   left join keleprd.mis.dbo.udtSE_Xref xref on xref.itemnmbr=iv1.itemnmbr
   left join keleg.dbo.rm00101 rm1 on rm1.custnmbr=@custnmbr
   left join tempload.dbo.eiv00700_Testing eiv7 on eiv7.pricegroup=iv1.pricegroup and eiv7.prclevel=rm1.prclevel
   left join dynamics_ext.dbo.udtNonInventoryLog ivlog on ivlog.NonInventoryID=iv1.itemnmbr and ivlog.vendorcode=@RSKCode

Open in new window

But then again, it was going across a linked server....
0
 
jdr0606Author Commented:
Thanks again to everyone, wish I could give you all credit.  It truly was a group effort.
0
 
Mark WillsTopic AdvisorCommented:
*laughing* you could always have awarded some assisted answers :)
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.