reverse lookup? (SELECT x FROM table) IN LIKE %xy%

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 2012 Forums

Transact-SQL (2012)

reverse lookup? (SELECT x FROM table) IN LIKE %xy%

Author

Topic

northturton
Starting Member

4 Posts

Posted - 2014-05-15 : 07:26:30

Hi, I'm trying to fix a stored procedure within our production db.

We have a notes table where users can enter free format details against a record via our intranet portal...

We can also add notes to this table via a lookup table - to put items on hold and provide a pre-defined reason... the text is submitted into the note...

ironically, some users decide to edit the note, appending a date... to this pre-defined note

We have a lookup table, where a user selects a reason and this gets recorded in a free format note field

1 Reason1
2 Reason2
3 Reason3
4 Reason4

Thus if Reason4 is selected and added to the notes table, the note would be Reason4

however when a user edits this it becomes Reason4 (15/05/2014 for 1 week)

We have a stored procedure that works if the reason text is as expected... however we now have to plug that hole.

SELECT ProjectId,
(
SELECT ReasonText FROM OnHoldReasons
WHERE (ReasonText IN
(SELECT Note FROM Notes WHERE (NoteID IN
(SELECT MAX(NoteID) FROM Notes
WHERE (notes.PackageID = dbo.Package.PackageID) AND (dbo.Package.ProcessID = 8) AND (NoteTypeID IN (1,2,6)) AND
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons))
)) ))
) AS OnHoldID
FROM dbo.Package WHERE dbo.Package.Enabled = 1
AND ProjectID = 90

The problem is centered around
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons)

In effect, I need to reverse the check so that the ReasonText is somewhere within the note... but we have up to 10 Reasons !!!
akin to something like (SELECT ReasonText FROM dbo.OnHoldReasons) IN %NOTE%...

The reason text is variable length text, and should be at the start of the note... but cannot be guaranteed.

Any advice from SQL experts?

many thanks
Brian

btw, everyone may say this as an excuse but its true, I didn't design this database or how it works. I have to add things in and update it :-(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-15 : 09:18:08

[code]-- declare test-tables
declare @OnHoldReasons Table(ReasonText varchar(255))
declare @Notes Table(Note varchar(255), NoteID int, PackageID int, NoteTypeID int)
declare @Package Table(PackageID int, ProcessID int, Enabled int, ProjectID int)

-- insert some test-rows
insert @OnHoldReasons
select 'Reason1' union all
select 'Reason2' union all
select 'another Reason Number 666'

insert @Notes
select 'Reason2 2014/05/15', 500, 2000, 1 union all
select 'Reason2 2014/05/15', 501, 2000, 9 union all
select 'Reason2 2014/05/15', 502, 2000, 2 union all
select 'Reason1', 503, 1000, 1 union all
select 'Reason2', 504, 7000, 6 union all
select 'another Reason Number 666', 505, 6000, 2

insert @Package
select 2000, 8, 1, 90 union all
select 2000, 8, 0, 90 union all
select 1000, 8, 1, 10 union all
select 7000, 8, 1, 70

-- trying the solution
select
p.ProjectId,
r.ReasonText as OnHoldID
from @Package p
join @Notes n on p.PackageID = n.PackageID and
n.NoteTypeID in (1,2,6) and
n.NoteID = (select max(n2.NoteID)
from @Notes n2
where p.PackageID = n2.PackageID and
n2.NoteTypeID in (1,2,6) )
join @OnHoldReasons r on n.Note like '%'+r.ReasonText+'%'
where p.Enabled=1 and ProjectID = 90
[/code]

Too old to Rock'n'Roll too young to die.

northturton
Starting Member

4 Posts

Posted - 2014-05-15 : 10:26:33

That's looking very promising - I translated your SELECT to fit my query (without your INSERT to tables) and its returned 8 records that I was originally missing due to the extra text the users added... where as previosly it was returning only 1 that fitted correctly...

However it's now missing the original record!

I'll play with your script in full to ascertain what I may have done wrong in translation.

many thanks for your time
Brian

northturton
Starting Member

4 Posts

Posted - 2014-05-15 : 10:31:05

ahh, your test only returns 1 record, Reason2, despite Reason2 appearing within 4 records. I would hope to return all records where Reason2 appears.

Further down the line, I return counts of each individual reason - i.e. Reason1 = 1, Reason2=4, another Reaso Number 666=1

northturton
Starting Member

4 Posts

Posted - 2014-05-15 : 10:39:32

Fixed it be eliminating a NoteTypeID of 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-15 : 10:51:25

I gave my best without having table structure, sample data and wanted result in relation to the sample data.
Hope all is fine now :)

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources