August 7, 2018 at 3:59 am
I have two tables called Components and PDF_MSDS. This Component table contains f_chem_name,f_component_id and f_chem_name columns and it contains below sample data.
1, Components Table
-----------------------------
SELECT F_Cas_Number,F_Component_Id,F_Chem_Name FROM Components WHERE F_Chem_Name='CHMNM_17816'
OUTPUT
------------
F_Cas_Number F_Component_Id F_Chem_Name
CAS_5861 PRD1000826 CHMNM_17816
2 Sample data of PDF_MSDS Table
----------------------------------------------
F_PRODUCT F_CAS_NUMBERS F_COMPONENT_IDS
360 CAS_5779¿CAS_5861¿CAS_2614¿ 3E000685¿3E002268¿3E004960¿3E005217¿PRD1000826¿
Now i want display the f_product value from PDF_MSDS table by comparing F_Cas_Number and F_Component_Id in Components table with F_CAS_NUMBERS
and F_COMPONENT_IDS in PDF_MSDS TABLE for the given f_chem_name in components table.
But i am not able compare directly using join with F_Cas_Number in Components table with F_CAS_NUMBERS in PDF_MSDS table and also F_Component_Id in
Components table with F_COMPONENT_IDS in PDF_MSDS TABLE because multiple cas_numbers and component_ids are in F_CAS_NUMBERS and F_COMPONENT_IDS columns
in PDF_MSDS table.So how can i search and compare cas_numbers and component_ids and select particular record.Please help.F_Chem_Name is input parameter for Procedure.
August 7, 2018 at 4:49 am
If you're using SQL 2012 you will need a string splitter. I've used Jeff Moden's - see http://www.sqlservercentral.com/articles/Tally+Table/72993/
For SQL 2016 onwards you can use STRING_SPLIT.
USE [tempdb];
GO
-- Set up test data:
IF OBJECT_ID('dbo.Components') IS NOT NULL DROP TABLE dbo.Components;
IF OBJECT_ID('dbo.PDF_MSDS') IS NOT NULL DROP TABLE dbo.PDF_MSDS;
CREATE TABLE dbo.Components (F_Cas_Number Varchar(10),F_Component_Id Varchar(10),F_Chem_Name Varchar(20));
INSERT dbo.Components VALUES ('CAS_5861', 'PRD1000826', 'CHMNM_17816');
INSERT dbo.Components VALUES ('CAS_5860', 'PRD1000827', 'CHMNM_17816');
CREATE TABLE dbo.PDF_MSDS (F_PRODUCT Int, F_CAS_NUMBERS Varchar(250), F_COMPONENT_IDS Varchar(250));
INSERT dbo.PDF_MSDS VALUES (360, 'CAS_5779¿CAS_5861¿CAS_2614¿', '3E000685¿3E002268¿3E004960¿3E005217¿PRD1000826¿');
INSERT dbo.PDF_MSDS VALUES (361, 'CAS_5778¿CAS_5862¿CAS_2616¿', '3E000686¿3E002269¿3E004961¿3E005218¿PRD1000827¿');
-- SQL 2012: Using Jeff Moden's string splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_CAS_NUMBERS, '¿') WHERE Item <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_COMPONENT_IDS, '¿') WHERE Item <> '') b (F_COMPONENT_ID)
)
SELECT COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';
-- SQL 2016 onwards
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_CAS_NUMBERS, '¿') WHERE value <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_COMPONENT_IDS, '¿') WHERE value <> '') b (F_COMPONENT_ID)
)
SELECT COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';
August 8, 2018 at 4:55 am
This query returns the result of COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components table.But i want to display F_PRODUCT from PDF_MSDS in the result.
F_PRODUCT
---------
360
August 8, 2018 at 7:24 am
-- SQL 2012: Using Jeff Moden's string splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_CAS_NUMBERS, '¿') WHERE Item <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_COMPONENT_IDS, '¿') WHERE Item <> '') b (F_COMPONENT_ID)
)
SELECT CTE.F_PRODUCT, COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';
-- SQL 2016 onwards
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_CAS_NUMBERS, '¿') WHERE value <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_COMPONENT_IDS, '¿') WHERE value <> '') b (F_COMPONENT_ID)
)
SELECT CTE.F_PRODUCT, COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';
August 16, 2018 at 8:29 pm
Above query is working fine.i am going to use this query in my stored procedure.F_Chem_name and Language is input parameters for this below query.Is it possible to create view for this below query and pass parameter to the view(F_Chem_name and Language)?.They wants view for this one.
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_CAS_NUMBERS, '¿') WHERE value <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_COMPONENT_IDS, '¿') WHERE value <> '') b (F_COMPONENT_ID)
)
SELECT CTE.F_PRODUCT, COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name Like @Chem_Name and Language=@Language
August 17, 2018 at 12:14 am
You can't pass parameters to a view. So you can either convert it into an Inline Table-Valued Function (which is effectively a parameterised view) or leave off the WHERE clause and let callers apply that filtering when they use the view.
August 17, 2018 at 1:54 am
cross apply taking more time when the table contains more records.so Is any other way to achieve cross apply concept in my query to improve performance.
August 17, 2018 at 3:04 am
Yes, it's reasonable to expect a query to take longer to run when it runs against larger tables. Please see this article on how to post performance problems. You may wish to try Jeff's splitter function instead of STRING_SPLIT - it may perform better. See the link posted by Laurie above,
John
August 18, 2018 at 1:19 am
laurie-789651 - Wednesday, August 8, 2018 7:24 AM
-- SQL 2012: Using Jeff Moden's string splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_CAS_NUMBERS, '¿') WHERE Item <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_COMPONENT_IDS, '¿') WHERE Item <> '') b (F_COMPONENT_ID)
)
SELECT CTE.F_PRODUCT, COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';-- SQL 2016 onwards
WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_CAS_NUMBERS, '¿') WHERE value <> '') a (F_CAS_NUMBER)
CROSS APPLY (SELECT value FROM STRING_SPLIT(F_COMPONENT_IDS, '¿') WHERE value <> '') b (F_COMPONENT_ID)
)
SELECT CTE.F_PRODUCT, COM.F_Cas_Number, COM.F_Component_Id, COM.F_Chem_Name
FROM dbo.Components COM
INNER JOIN CTE ON CTE.F_CAS_NUMBER = COM.F_Cas_Number AND CTE.F_COMPONENT_ID = COM.F_Component_Id
WHERE COM.F_Chem_Name = 'CHMNM_17816';
Thank you.It is working fine for me.
August 18, 2018 at 9:59 pm
Since you are on SQL Server 2012, I suggest you use DelimitedSplit8k_LEAD
😎
August 19, 2018 at 5:20 am
ok.Thank You.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply