Do I need an Array to achieve this? logic

1

To all my guru SQL optimizers out there. The follow script I have written below works fine. However I have been asked can i make a variable that can hold a list of IDS(in this case ownrshpIDs) to pass to the query and have it run through each ID one by one.

I'm thinking an array is needed, but wanted to see if theres away around that? any thoughts?

Select distinct
inv_corp.OWNRSHP_ID
,SE.Sending_entity_nm AS Bottler
,snap.FSH_DSTR_OWNRSHP_ID AS DELIVERY_SOURCE
,inv_corp.BTLR_DLVR_PNT_NO AS BTLR_DLVR_PNT_NO
,snap.DLVR_PNT_NM AS BTLR_DLVR_PNT_NM
,snap.CHNL_TYPE_DESC AS MARKET_TYPE
,snap.TRADE_CHNL_DESC AS TRADE_CHNL_DESC
,snap.TCCC_SUB_CHNL_NM AS TCCC_SUB_CHNL_NM
,snap.STATE AS STATE

,CASE
WHEN cbs.CBS_BUSINESS_TYPE = 1 THEN 'TRADE SALE'
WHEN cbs.CBS_BUSINESS_TYPE = 4 THEN 'FULL SERVE'
WHEN cbs.CBS_BUSINESS_TYPE = 5 THEN 'EXTRNL SLS'
ELSE NULL
END AS CBS_BUSINESS_TYPE
,ptype.PRD_TYP_NM AS PROD_TYPE
,cal.CUR_YR_AC_PRD AS CAL_YEAR_MONTH
--,cal.CUR_YR_AC_PRD AS CAL_YEAR_MONTH
,inv_corp.Inv_dt
,sum(CASE
WHEN (qty) IS NOT NULL THEN (qty* TCCC_192CONV(FLOAT))
ELSE 0
END )AS VolEQ --20 fields

,sum(CASE
WHEN (qty) IS NOT NULL THEN (cast(qty as FLOAT )* cast(prodet.STD_ADJ_FCT as FLOAT))
ELSE 0
END) AS VolStd

,(sum(Whlsl_Price_Xtnd) - sum(Off_Inv_Cma_Disc)) - (sum(Off_Inv_Ctm_Disc) - sum(On_Inv_Disc) ) as DNSI

/* ,sum(Whlsl_Price_Xtnd) as Whlsl_price_Xtnd
,sum(Off_Inv_Disc) as Off_inv_Disc
,sum(Off_Inv_Cma_Disc) as Off_inv_Cma_disc
,sum(Off_Inv_Ctm_Disc) as Off_inv_ctm_disc
,sum(On_Inv_Disc) as On_inv_disc
,sum(On_Inv_Cma_Disc) as On_inv_cma_disc
,sum(On_Inv_Ctm_Disc) as On_inv_ctm_disc --24fields
*/

FROM CORP_LINES_VIEWS.LINE_CORP line_corp
join corp_invoice_VIEWS.invoice_corp inv_corp
On line_corp.Tsr_Inv_Ref_No = inv_corp.Tsr_Inv_Ref_No
and line_corp.OWNRSHP_ID = inv_corp.OWNRSHP_ID

Join CBS_VIEWS.CBS_DELIVERY_POINT cbs
ON inv_corp.BTLR_DLVR_PNT_NO = cbs.BTLR_DLVR_PNT_NO
and inv_corp.OWNRSHP_ID = cbs.OWNRSHP_ID

Join CHR_VIEWS.DPT_SNAPSHOT_SELECT snap
ON inv_corp.BTLR_DLVR_PNT_NO = snap.BTLR_DLVR_PNT_NO
and inv_corp.OWNRSHP_ID = snap.OWNRSHP_ID

Join CBS_VIEWS.sending_entity se
ON inv_corp.OWNRSHP_ID = se.OWNRSHP_ID

Join NSR_VIEWS.PRODET prodet
ON prodet.cce_material_no = line_corp.cce_material_no

Join DLCCC_CUST_CNTC_CNTR_LAB.TBL_3PY_to_YTD_CAL cal
ON CAL.fact_dt = line_corp.Inv_dt
Join CORP_LINES.MATL_JOIN_KEY mkey
ON mkey.cce_material_no = line_corp.cce_material_no
and mkey.OWNRSHP_ID = line_corp.OWNRSHP_ID

Join IWR_VIEWS.MN_PROD_TYPE ptype
ON ptype.tsr_prd_typ = prodet.tsr_prd_typ

Where 1=1
and line_corp.inv_dt>'2018-01-01'
and line_corp.OWNRSHP_ID IN (10,55,75,110,65,85,95) ---Want to be able to provide list in variable

GROUP BY
inv_corp.OWNRSHP_ID
,SE.Sending_entity_nm
,snap.FSH_DSTR_OWNRSHP_ID
,inv_corp.BTLR_DLVR_PNT_NO
,snap.DLVR_PNT_NM
,snap.CHNL_TYPE_DESC
,snap.TRADE_CHNL_DESC
,snap.TCCC_SUB_CHNL_NM
,snap.STATE
,cbs.CBS_BUSINESS_TYPE
,ptype.PRD_TYP_NM
,cal.CUR_YR_AC_PRD
,inv_corp.Inv_dt

2

You can use a string split utility - like DelimitedSplit8K (Reaping the benefits of the Window functions in T-SQL – SQLServerCentral)

Or STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Learn if you are on 2017 or higher.

becomes:

and line_corp.OWNRSHP_ID IN (Select d.ItemValue From dbo.DelimitedSplit8K_Lead({your variable}, ',') d)

Alternatively - you can move that to a join using a derived table:

INNER JOIN (Select * From dbo.DelimitedSplit8K_lead({your variable}, ',') d) AS d1 ON d1.ItemValue = line_corp.OWNRSHP_ID

For either - you may need to cast ItemValue to an appropriate data type to avoid an implicit conversion if that implicit conversion affects cardinality estimates.

1 Like
3

Yeah my company isn't big on us using 3rd party tools . plus that would have to go through the DBAs. Thats why I was hoping I could solve this via some SQL logic that I havent thought of

4

These are not 3rd party tools - the delimited split is just a SQL function and STRING_SPLIT is a built-in function included in 2017 and greater.

If your DBAs don't allow you the ability to create functions or stored procedures - and will not create this function then they should provide you with the approved function. The delimited split function I linked to is highly optimized and will outperform almost all other methods - and most of the other methods I have seen use a multi-statement table valued function which tend to perform abysmally (especially when compared to this function).

5

Hey Jeff -

Met to relpy back to this. thanks for the suggestions. I was able to get this process working as I needed.