Search string one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Search string one table to another

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-25 : 02:34:45

Hi I have a requirements to search string from one table to another. I will check if the corresponding IDnun from #sample2 table has a prefix of FRU,FRUA,TX or of else NON-FRU in #sample1. Thanks in advance.

Below is the DDL.


Create table #sample
(Item nvarchar(35), IdNum nvarchar(35))
Insert into #sample(Item,IdNum ) values('MAT3748','TRP005196603')
Insert into #sample(Item,IdNum) values('MAT3751','TRP005196603')
Insert into #sample(Item,IdNum) values('TX3001710DR','TRP005196603')
Insert into #sample(Item,IdNum) values('FRU300DRD','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3745','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3748','TRP005207420')
Insert into #sample(Item,IdNum) values('FRUA300DRD','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3771','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3774','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3824','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3977','TRP005207420')
Insert into #sample(Item,IdNum) values('CRR3000412iPH5','TRP005224362')

Create table #Sample2
( IdNum nvarchar(35))
Insert into #sample2(IdNum) values('TRP005196603')
Insert into #sample2(IdNum) values('TRP005207420')
Insert into #sample2(IdNum) values('TRP005207421')
Insert into #sample2(IdNum) values('TRP005207420')
Insert into #sample2(IdNum) values('TRP005224362')

sample desired result:

IDNUM-------REMARKS
---------------------
TRP005196603--TX
TRP005207421--FRUA
TRP005207420--FRU
TRP005224362--NON-FRU



with my Query, i get the 3 prefix but the problem will be the NON-FRU. if I include the 'FRU' in else even those IDNum that have those Prefix still filled by 'FRU'.

select b.IdNum,
	case when a.item like '%TX%' then 'TX' 
	     when a.Item like '%FRUA%' then 'FRUA' 
	     when a.Item like '%FRUB%' then 'FRUB' END as Remarks
from #Sample2 b
Inner Join #sample a
On a.IdNum = b.IdNum 
Group by b.IdNum, a.Item

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:24:10

Not quite elegant solution , but is still in dev ..



;WITH CTE 
AS
	(
		select 
			b.IdNum,
			case when a.item like '%TX%' then 'TX' 
				 when a.Item like '%FRUA%' then 'FRUA' 
				 when a.Item like '%FRU[0-9]%' then 'FRU' 
				 --else 'NON-FRU' 
				 END as Remarks
		from #Sample2 b
		Inner Join #sample a
		On a.IdNum = b.IdNum 
		Group by b.IdNum, a.Item
	)
	
select 
		A.IdNum
		,Remarks
FROM 
	CTE AS A	
WHERE
	Remarks IS NOT NULL
UNION ALL

SELECT
	DISTINCT	
	A.IdNum
	,'NON-FRU'
FROM	
	#Sample2 AS A
	LEFT JOIN CTE AS B
	ON A.IdNum = B.IdNum
	AND B.Remarks IS NOT NULL
WHERE
	B.IdNum IS NULL

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:24:27

the output:


IdNum	Remarks
TRP005196603	TX
TRP005207420	FRU
TRP005207421	FRUA
TRP005224362	NON-FRU

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:35:18

the same output , using Row Number



;WITH CTE 
AS
	(
		select 
			b.IdNum,
			case when a.item like '%TX%' then 'TX' 
				 when a.Item like '%FRUA%' then 'FRUA' 
				 when a.Item like '%FRU[0-9]%' then 'FRU' 
				 else 'NON-FRU' 
				 END as Remarks
			,case when a.item like '%TX%' then 1
				 when a.Item like '%FRUA%' then 2
				 when a.Item like '%FRU[0-9]%' then 3
				 else 4
				 END AS OrderField
			
		from #Sample2 b
		INNER Join #sample a
		On a.IdNum = b.IdNum 
		Group by b.IdNum, a.Item
	)
	
SELECT 
	IdNum
	,Remarks
FROM
(
	select 
			A.IdNum
			,Remarks			
			,ROW_NUMBER() OVER(PARTITION BY A.IdNum ORDER BY OrderField) AS RN
	FROM 
		CTE AS A	
)Sample3
WHERE
	RN = 1

result :


IdNum	Remarks
TRP005196603	TX
TRP005207420	FRU
TRP005207421	FRUA
TRP005224362	NON-FRU

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-26 : 03:01:14

Thank you very much stepson. This is what i'm looking for. Here is the final query based on your codes. I notice when I run the codes it takes time to generate which is quite not good. I try to filter a one day data it takes more than 5 minutes, how come when i filter a month month data i think maybe more than 30 mintus. I think the table Inventtrans as a lot of data. this is a trnasction table.


declare @timezoneOffset int
set @timezoneOffset=8
;WITH CTE 
AS
	(
		select 
			s.PRODID,
			case when x.ITEMID like '%TX%' then 'TX' 
				 when x.ITEMID like '%FRUA%' then 'FRUA' 
				 when x.ITEMID like '%FRUB%' then 'FRUB' 
				 when x.ITEMID like '%FRU[0-9]%' then 'FRU' 
				 else 'NON-FRU' 
				 END as Remarks
			,case when x.ITEMID like '%TX%' then 1
				 when x.ITEMID like '%FRUA%' then 2
				 when x.ITEMID like '%FRUB%' then 3
				 when x.ITEMID like '%FRU[0-9]%' then 4
				 else 5
				 END AS OrderField
		FROM dbo.PRODTABLE s with (nolock)
		INNER Join dbo.INVENTTRANS x with (nolock)
		On x.TRANSREFID = s.PRODID  AND x.TRANSTYPE=8 AND x.DATAAREAID='tap'
		inner join dbo.prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid 
	    inner join dbo.inventdim ivd  with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid 
		WHERE  
			s.dataareaid = 'tap'  
			AND s.PRODSTATUS in (5,7)  
			AND s.prodpoolid IN       
	  		AND s.asuprodlineid IN 
			AND ivd.inventlocationid in 
			AND pp.ASUPOOLGROUP = 
			AND DATEADD(HOUR,convert(int,@timezoneOffset), s.ASURAFDATETIME) BETWEEN '2015-03-04 12:00 AM' AND  '2015-03-05 12:00 AM'
		GROUP by s.PRODID , x.ITEMID 
	)
	
SELECT 
	PRODID
	,Remarks
FROM
(
	select 
			A.PRODID 
			,Remarks			
			,ROW_NUMBER() OVER(PARTITION BY A.PRODID ORDER BY OrderField) AS RN
	FROM 
		CTE AS A	
) Sample3
WHERE
	RN = 1

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-26 : 04:26:12

One remark is related to this :


AND DATEADD(HOUR,convert(int,@timezoneOffset), s.ASURAFDATETIME) BETWEEN '2015-03-04 12:00 AM' AND  '2015-03-05 12:00 AM'

It is better to move the calculation on right side :


AND s.ASURAFDATETIME >= DATEADD(HOUR, - convert(int,@timezoneOffset), '2015-03-04 12:00 AM')
AND s.ASURAFDATETIME <= DATEADD(HOUR,- convert(int,@timezoneOffset), '2015-03-05 12:00 AM')

Also take a look / show us the execution plan for better solutions, ideas.
May be need to add some indexes, see the existing ones ...

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-29 : 21:33:10

This is the error i got when i'm running the query with execution plan.
By the way, Its okey if I will use cross apply in my query.

Msg 262, Level 14, State 4, Line 3

SHOWPLAN permission denied in database 'xxxxxx'.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-30 : 00:51:48

You don't have permission related to ShowPlan

GRANT SHOWPLANT TO USER_XXX
GO

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-30 : 00:53:54

GRANT SHOWPLANT TO USER_XXX
GO

sabinWeb MCP

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-30 : 12:34:53

[code]
DECLARE @timezoneOffset int
SET @timezoneOffset=8

;WITH CTE
AS
(
SELECT
s.PRODID,
MIN(case
when x.ITEMID like '%TX%' then '1TX'
when x.ITEMID like '%FRUA%' then '2FRUA'
when x.ITEMID like '%FRUB%' then '3FRUB'
when x.ITEMID like '%FRU[0-9]%' then '4FRU'
else '5NON-FRU'
END
) as Sort_and_Remarks
FROM dbo.PRODTABLE s with (nolock)
INNER Join dbo.INVENTTRANS x with (nolock)
On x.TRANSREFID = s.PRODID AND x.TRANSTYPE=8 AND x.DATAAREAID='tap'
inner join dbo.prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid
inner join dbo.inventdim ivd with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid
WHERE
s.dataareaid = 'tap'
AND s.PRODSTATUS in (5,7)
AND s.prodpoolid IN
AND s.asuprodlineid IN
AND ivd.inventlocationid in
AND pp.ASUPOOLGROUP =
AND s.ASURAFDATETIME >= DATEADD(HOUR, -CONVERT(int,@timezoneOffset), '20150304')
AND s.ASURAFDATETIME < DATEADD(HOUR, -CONVERT(int,@timezoneOffset), '20150305')
GROUP by s.PRODID
)

SELECT
PRODID
,SUBSTRING(Remarks, 2, 10) AS Remarks
FROM CTE
--ORDER BY PRODID

[/code]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-30 : 20:33:42

This is what i got when i run the query.
I dont have the permission to a database.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 262, Level 14, State 4, Line 5
SHOWPLAN permission denied in database 'xxxxx'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources