July 3, 2008 at 12:51 pm
I'm still a novice at SQL and I'm stuck trying to set up a search on a table that could have four 'AND' and two 'OR' searches, if that makes any sense. I've looked at using CASE but had no luck there, and I can't see how JOINs would work.
One table with five columns to search through. I want to select any rows that contain all four words in any of the five columns, or those rows and any rows with two other words in any of the five columns. Some of the rows have NULL values in some columns, and some of the search criteria will be spaces. The Designer can be any of the four or six search criteria words. Here is what I have that doesn't work if I have all of the search words and criteria included. Any suggestions would be most appreciated.
CREATE Procedure dbo.spLocal_Drawing_Entry_Search_Results;1
@sectnum int,
@criteriaOne varchar(20),
@criteriaTwo varchar(20),
@criteriaThree varchar(20),
@criteriaFour varchar(20),
@criteriaFive varchar(20),
@criteriaSix varchar(20)
AS
Set Nocount On
create table #section_results (sr_size varchar(50), sr_number int, sr_section int, sr_type varchar(16), sr_date varchar(24),
sr_project varchar(120), sr_designer varchar(80), sr_scan bit, sr_area varchar(80),
sr_lineOne varchar(150), sr_lineTwo varchar(150), sr_lineThree varchar(150), sr_lineFour varchar(150))
INSERT INTO #section_results
SELECT DrawingSize, DrawingNumber, DrawingSection, DrawingType, DrawingDate,
Project, Designer, Scan, Area, LineOne, LineTwo, LineThree, LineFour
FROM DrawingInfo
WHERE DrawingSection = @sectnum
ORDER BY DrawingNumber
SELECT sr_number, sr_lineOne, sr_lineTwo, sr_lineThree, sr_lineFour,
sr_section, sr_date, sr_project, sr_designer, sr_scan,
sr_size, sr_area, sr_type
FROM #section_results
WHERE (sr_lineONE Like '%' + rtrim(@CriteriaOne) + '%' AND
sr_lineONE Like '%' + rtrim(@CriteriaTwo) + '%' AND
sr_lineONE Like '%' + rtrim(@CriteriaThree) + '%' AND
sr_lineONE Like '%' + rtrim(@CriteriaFour) + '%')
OR
(sr_lineTWO Like '%' + rtrim(@CriteriaOne) + '%' AND
sr_lineTWO Like '%' + rtrim(@CriteriaTwo) + '%' AND
sr_lineTWO Like '%' + rtrim(@CriteriaThree) + '%' AND
sr_lineTWO Like '%' + rtrim(@CriteriaFour) + '%')
OR
(sr_lineTHREE Like '%' + rtrim(@CriteriaOne) + '%' AND
sr_lineTHREE Like '%' + rtrim(@CriteriaTwo) + '%' AND
sr_lineTHREE Like '%' + rtrim(@CriteriaThree) + '%' AND
sr_lineTHREE Like '%' + rtrim(@CriteriaFour) + '%')
OR
(sr_lineFOUR Like '%' + rtrim(@CriteriaOne) + '%' AND
sr_lineFOUR Like '%' + rtrim(@CriteriaTwo) + '%' AND
sr_lineFOUR Like '%' + rtrim(@CriteriaThree) + '%' AND
sr_lineFOUR Like '%' + rtrim(@CriteriaFour) + '%')
OR
(sr_lineDesigner Like '%' + rtrim(@CriteriaOne) + '%' OR
sr_lineDesigner Like '%' + rtrim(@CriteriaTwo) + '%' OR
sr_lineDesigner Like '%' + rtrim(@CriteriaThree) + '%' OR
sr_lineDesigner Like '%' + rtrim(@CriteriaFour) + '%')
OR
(sr_lineONE Like '%' + rtrim(@CriteriaFive) + '%' AND
sr_lineONE Like '%' + rtrim(@CriteriaSix) + '%')
OR
(sr_lineTWO LIKE '%' + rtrim(@CriteriaFive) + '%' AND
sr_lineTWO Like '%' + rtrim(@CriteriaSix) + '%')
OR
(sr_lineTHREE Like '%' + rtrim(@CriteriaFive) + '%' AND
sr_lineTHREE Like '%' + rtrim(@CriteriaSix) + '%')
OR
(sr_lineFOUR Like '%' + rtrim(@CriteriaFive) + '%' AND
sr_lineFOUR Like '%' + rtrim(@CriteriaSix) + '%')
OR
(sr_DESIGNER Like '%' + rtrim(@CriteriaFive) + '%' OR
sr_DESIGNER Like '%' + rtrim(@CriteriaSix) + '%')
ORDER by sr_number, sr_date
drop table #section_results
GO
July 4, 2008 at 4:15 am
Am I right in saying that you have a table that represents a catalogue of Design Drawings? This table has the name of the designer, and also a number of other criteria or categorisations for the drawings contained in four 'lines'? You wish have one stored procedure that allows you to select drawings either by one or more designer or by one or more categorisations?
To test out your stored procedure, and find out why it is not 'working', it would be nice if you could provide us with a small amount of sample data, done as insert statements.
The database hasn't been normalised, but there is a way around that that you might consider if there are a lot of drawings. If you maintain a table of all the categorisations used, and a linking table that links the drawing number with the categorisations selected for that drawing, then you'll get a very fast searching method for selecting drawings. Do the same for designers too and the SQL becomes much, much easier.
Best wishes,
Phil Factor
July 4, 2008 at 12:04 pm
As "Phil Factor" indicated "The database hasn't been normalised" but additionally the search criteria is also an array and arrays cannot be easily manipulated with SQL.
The solution is to create temporary tables that are normalized and then run the query.
CREATE TABLE DrawingInfo
(DrawingNumberinteger not null
,DrawingSection integer not null
,Designervarchar(80) null
,LineOnevarchar(150) null
,LineTwovarchar(150) null
,LineThreevarchar(150) null
,LineFourvarchar(150) null
, constraint DrawingInfo_P primary key (DrawingNumber,DrawingSection)
)
go
create procedure dbo.spLocal_Drawing_Entry_Search_Results
(@sectnum int,
@criteriaOne varchar(20),
@criteriaTwo varchar(20),
@criteriaThree varchar(20),
@criteriaFour varchar(20),
@criteriaFive varchar(20),
@criteriaSix varchar(20)
)
as
DECLARE @CriteriaCountinteger
,@PartialMatchCountinteger
-- Normalize the criteria:
create table #criteria (criteria varchar(20) )
IF @criteriaOne is not null and @criteriaOne <> ''
insert into #criteria (criteria) values (@criteriaOne)
IF @criteriaTwo is not null and @criteriaTwo <> ''
insert into #criteria (criteria) values (@criteriaTwo)
IF @criteriaThree is not null and @criteriaThree <> ''
insert into #criteria (criteria) values (@criteriaThree)
-- Repeat for the remaining criteria
SELECT @CriteriaCount = count(*) FROM #criteria
IF 0 = @CriteriaCount
begin
RAISERROR('No search criteria was provided',16,1)
return +1
end
-- Normalize the table
CREATE TABLE #Lines
( DrawingNumberinteger not null
,DrawingSection integer not null
,Linevarchar(150) not null
, constraint Lines_P primary key (Line,DrawingNumber,DrawingSection)
)
insert into #Lines (DrawingNumber,DrawingSection,Line)
SELECT DrawingNumber,DrawingSection,LineOne
FROMDrawingInfo
WHEREDrawingInfo.DrawingSection = @sectnum
ANDEXISTS
(SELECT 1
FROM#criteria
WHEREDrawingInfo.Line like '%' + #criteria.criteria + '%'
)
UNION ALL
SELECTDrawingNumber,DrawingSection,LineTwo
FROMDrawingInfo
WHEREDrawingInfo.DrawingSection = @sectnum
ANDEXISTS
(SELECT 1
FROM#criteria
WHEREDrawingInfo.Line like '%' + #criteria.criteria + '%'
)
-- Repeat for LineThree and LineFour
SELECT @PartialMatchCount = count(*) from #Lines
IF 0 = @PartialMatchCount
begin
RAISERROR('No rows match the provided criteria',16,1)
return +1
end
selectDrawingInfo.*
FROMDrawingInfo
join(selectDrawingNumber,DrawingSection
from#Lines
GROUP BY DrawingNumber,DrawingSection
HAVINGCOUNT(*) = @CriteriaCount
) as MatchRows
on MatchRows.DrawingNumber = DrawingInfo.DrawingNumber
and MatchRows.DrawingSection = DrawingInfo.DrawingSection
go
SQL = Scarcely Qualifies as a Language
July 4, 2008 at 6:53 pm
tybee (7/3/2008)
I'm still a novice at SQL and I'm stuck trying to set up a search on a table that could have four 'AND' and two 'OR' searches, if that makes any sense. I've looked at using CASE but had no luck there, and I can't see how JOINs would work.
If you want an answer that's actually been tested, please provide some data as outlined in the link in my signature line... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 8:16 am
Thanks for the replies so far. This database is probably normalized as much as it needs to be since most of the data is freehand--except for the section list, which is in a separate table. I haven't tested the procedure above, but while I try and do that here is the script that will create the two tables as described in the etiquette link (thanks for that nudge). If there aren't enough records let me know. The database has 58,072 entries in it.
Note: I did have trouble finding enough records to extract because the 'DrawingType' and 'LineFour' columns are mostly NULL values, and I couldn't figure out how to get QUOTENAME to work unless I ran it with a WHERE statement to ignore any rows with NULLs in both of those columns.
Thanks,
Greg
==============================================================
--================================================================================
--Create DrawingInfo and SectionList tables for testing
--================================================================================
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#drawinginfo','U') IS NOT NULL
DROP TABLE #drawinginfo
--===== Create the test table with
CREATE TABLE #drawinginfo
(RecNo int IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
Scan bit,
DrawingSize varchar(50),
DrawingNumber int,
DrawingSection int,
DrawingType varchar(15),
DrawingDate varchar(24), --the old Access database had this as a text field and it has not been straightened out yet
Project varchar(120),
Designer varchar(80),
Area varchar(80),
LineOne varchar(150),
LineTwo varchar(150),
LineThree varchar(150),
LineFour varchar(150)
)
--===== Setup any special required conditions especially where dates are concerned
--SET DATEFORMAT DMY --not needed yet
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #drawinginfo ON
--===== Insert the test data into the test table
INSERT INTO #drawinginfo
(Recno, Scan, DrawingSize, DrawingNUmber, DrawingSection, DrawingType, DrawingDate,
Project, Designer, Area, LineOne, LineTwo, LineThree, LineFour)
SELECT '4855','0','CD','1113','4','GA','121388','JR0919','PDQ','EVAPORATORS 303 - PIPING','GENERAL ARRANGEMENT',' NO. 5 AND 6 EVAPORATOR SURFACE','CONDENSERS','EVAPORATOR ROOM' UNION ALL
SELECT '4856','0','CD','1115','4','EQ','111389','JR0919','PDQ','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT - PLAN',' NO.7 EVAPORATOR AND CONDENSATE','STRIPPER',' EVAPORATOR ROOM' UNION ALL
SELECT '4857','0','CD','1116','4','EQ','111089','JR0919','JONES','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT - SECTIONS & DETAILS',' NO. 7 EVAPORATOR AND CONDENSATE',' STRIPPER',' EVAPORATOR ROOM' UNION ALL
SELECT '4858','0','CD','1117','4','EQ','062289','JR0919','PDQ','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT - SECTIONS & DETAILS',' NO. 5 AND 6 EVAPORATOR SURFACE','CONDENSERS','EVAPORATOR ROOM' UNION ALL
SELECT '4859','0','CD','1118','4','EQ','110789','JR0919','PDQ','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT - PLAN',' NO. 5 AND 6 EVAPORATOR SURFACE','CONDENSERS','EVAPORATOR ROOM' UNION ALL
SELECT '4860','0','CD','1119','4','EQ','062289','JR0919','PDQ','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT - SECTIONS & DETAILS',' NO. 5 AND 6 EVAPORATOR SURFACE','CONDENSERS','EVAPORATOR ROOM' UNION ALL
SELECT '4862','0','CD','1125','4','EQ','110789','JR0919','PDQ','EVAPORATORS 303 - PIPING','EQUIPMENT LAYOUT PLAN',' K-2 WBL FILTER SCREEN, PIPE RACKS',' F-SOUTH AND G',' EVAPORATOR ROOM' UNION ALL
SELECT '4877','0','CD','1174','4','EL','090889','JR0919','ADAMS','EVAPORATORS 303 - ELECTRICAL','WIRING DIAGRAM','BLOX, K-2 WBL FILT. SCREN. AND','RES. TK. 3 AND 4',' EVAPORATOR ROOM' UNION ALL
SELECT '4878','0','CD','1175','4','EL','073189','JR0919','PDQ','EVAPORATORS 303 - ELECTRICAL','480V ELEMENTARY AND CONNECTION DIAGRAM','#1 HBL TK. NO. PMP 303-021-1210',' AND NO. PMP 303-021-1213',' EVAPORATOR ROOM' UNION ALL
SELECT '4885','0','CD','1186','4','EL','080789','JR0919','PDQ','EVAPORATORS 303 - ELECTRICAL','ELECTRICAL ONE LINE DIAGRAM',' 480V MCC (EVAPORATOR NO. 3)',' (EVAPORATOR NO. 5 MOTORS)',' EVAPORATOR ROOM' UNION ALL
SELECT '4899','0','CD','1203','4','IN','012490','JR0919','PDQ','EVAPORATORS 303 - INSTRUMENT','NO. 4, 5 AND 6 EVAPORATOR',' CONTROL CONSOLE',' EVAPORATOR CONTROL ROOM',' EVAPORATOR ROOM' UNION ALL
SELECT '4939','0','CD','1254','4','ST','060989','JR0919','SMITH','EVAPORATORS 303 - STRUCTURAL STEEL','STRUCT. STL. TANK SUPPORTS AND','PLATFORM ELEVATIONS AND DETAILS',' NO. 5 EVAPORATOR SET',' EVAPORATOR ROOM - MECH' UNION ALL
SELECT '4940','0','CD','1255','4','ST','101089','JR0919','PDQ','EVAPORATORS 303 - STRUCTURAL STEEL','STRUCT. STL. TANK SUPPORT AND',' PLATFORM PLAN',' NO. 6 EVAPORATOR SET',' EVAPORATOR ROOM - MECH' UNION ALL
SELECT '4949','0','CD','1264','4','ST','052589','JR0919','PDQ','EVAPORATORS 303 - STRUCTURAL STEEL','STRUCT. STL. - PLAN @ EL. 32''-10 1/2"',' AND 23''-4 1/8"','NO. 7 EVAPORATOR SET',' EVAPORATOR ROOM' UNION ALL
SELECT '4955','0','CD','1281','4','ST','052589','JR0919','MG','EVAPORATORS 303 - STRUCTURAL STEEL','STRUCT. STL. WEST ELEVS @ 1.1',' AND DETAILS',' NO. 7 EVAPORATOR SET',' EVAPORATOR ROOM - MECH' UNION ALL
SELECT '4956','0','CD','1282','4','ST','121389','JR0919','PDQ','EVAPORATORS 303 - STRUCTURAL STEEL','STRUCT. STL. - PLAN @ 78''-8 3/4"',' AND DETAILS',' NO. 7 EVAPORATOR SET',' EVAPORATOR ROOM - MECH' UNION ALL
SELECT '4967','0','CD','1303','4','EL','090889','JR0919','PDQ','EVAPORATORS 303 - ELECTRICAL','480V ELEMENTARY DIAGRAM',' K2 WBL SCREEN 303-038-1300',' RESERVE TK''S 3 & 4','EVAPORATOR ROOM' UNION ALL
SELECT '4970','0','CD','1306','4','EL','092789','JR0919','BOOKER','EVAPORATORS 303 - ELECTRICAL','EVAP. NO. 7 CONTROL CONSOLE',' RELAY AND TERMINAL AREA',' NO. 1 EVAPORATOR CONTROL ROOM',' EVAPORATOR ROOM' UNION ALL
SELECT '4987','0','CD','1342','4','EL','011590','JR0919','J.D.','EVAPORATORS 303 - ELECTRICAL','ELECTRICAL TRAY PLAN',' REJECTS PRESS, SEC. BLOW COND AND',' NO. 4 EVAPORATOR',' NO. 8 PULPING LINE/EVAPORATORS' UNION ALL
SELECT '4990','0','CD','1345','4','EL','072589','JR0919','PDQ','EVAPORATORS 303 - ELECTRICAL','ELECTRICAL KEY PLAN',' AREA 303 EVAPORATOR NO. 7',' BLACK LIQUOR OXIDATION','EVAPORATOR ROOM' UNION ALL
SELECT '4994','0','CD','1349','4','EL','090889','JR0919','PDQ','EVAPORATORS 303 - ELECTRICAL','ELEMENTARY DIAGRAM','EVAPORATOR NO. 7 STRIPPER',' VALVE CONTROL',' EVAPORATOR ROOM' UNION ALL
SELECT '5826','0','ED','1123','4','PI','111089','JR0919','PDQ','EVAPORATORS 303 - PIPING','PIPING - PLAN EL. @ GRADE 13''-6"',' TO BELOW EL. 38''-7 1/2"',' NO. 6 EVAPORATOR SURFACE CONDENSER','EVAPORATOR ROOM' UNION ALL
SELECT '5827','0','ED','1124','4','PI','080489','JR0919','PDQ','EVAPORATORS 303 - PIPING','PIPING - PLAN EL. 38''-7 1/2"',' TO BELOW 60''-0"',' NO. 6 EVAPORATOR SURFACE CONDENSER','EVAPORATOR ROOM'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #drawinginfo OFF
--===============================================================================================
--Create SectionList table for testing. The engineers can search the entire database, or search
--by section number, which is why this table exists. It may not factor into this test but here
--it is anyway.
--===============================================================================================
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#sectionlist','U') IS NOT NULL
DROP TABLE #sectionlist
--===== Create the test table with
CREATE TABLE #sectionlist
(SectionNumber int IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
SectionDescription nvarchar(80),
FormCall nvarchar(24), --not used in this test
TableCall nvarchar(24) --not used in this test
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #sectionlist ON
--===== Insert the test data into the test table
INSERT INTO #sectionlist
(SectionNumber, SectionDescription, FormCall, TableCall)
SELECT '1','WOODYARD AND WOODROOM','F SEARCH RESULTS 1','SECTION 1' UNION ALL
SELECT '2','LIME RECOVERY AND CAUSTIC ROOM','F SEARCH RESULTS 2','SECTION 2' UNION ALL
SELECT '3','RECOVERY ROOM AND CHEMICAL STORAGE','F SEARCH RESULTS 3','SECTION 3' UNION ALL
SELECT '4','EVAPORATOR ROOM','F SEARCH RESULTS 4','SECTION 4' UNION ALL
SELECT '5','K-1 DIGESTOR AND DIFFUSER ROOM','F SEARCH RESULTS 5','SECTION 5' UNION ALL
SELECT '6','WET ROOM','F SEARCH RESULTS 6','SECTION 6' UNION ALL
SELECT '7','BEATER ROOM','F SEARCH RESULTS 7','SECTION 7' UNION ALL
SELECT '8','PAPER MILL GENERAL','F SEARCH RESULTS 8','SECTION 8' UNION ALL
SELECT '9','NUMBERS 1 AND 2 PAPER MACHINES','F SEARCH RESULTS 9','SECTION 9' UNION ALL
SELECT '10','NUMBERS 3 THROUGH 6 PAPER MACHINES','F SEARCH RESULTS 10','SECTION 10'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #sectionlist OFF
July 9, 2008 at 3:26 am
How's this for a solution (slow, but then it is best to get something working and move on from that point (watch out for the two close-brackets at the end. They're important but the formattting turned them into smileys for a while)
[font="Courier New"]ALTER PROCEDURE dbo.spLocal_Drawing_Entry_Search_Results
/*
dbo.spLocal_Drawing_Entry_Search_Results 4
dbo.spLocal_Drawing_Entry_Search_Results 4,'Electrical'
dbo.spLocal_Drawing_Entry_Search_Results 4,'Electrical','press'
dbo.spLocal_Drawing_Entry_Search_Results 4,'struct','evaporator','platform'
dbo.spLocal_Drawing_Entry_Search_Results 4,default,default,default,default,'pdq'
*/ @sectnum INT = 4,--default to section 4
@criteriaOne VARCHAR(20) = NULL,--list of criteria
@criteriaTwo VARCHAR(20) = NULL,--list of criteria
@criteriaThree VARCHAR(20) = NULL,--list of criteria
@criteriaFour VARCHAR(20) = NULL,--list of criteria
@criteriaFive VARCHAR(20) = NULL,--list of criteria
@criteriaSix VARCHAR(20) = NULL--list of criteria
AS
DECLARE @criteria TABLE--put the criteria into a table
(
criterion_ID INT IDENTITY(1, 1),
criterion VARCHAR(20),
TypeOfCriterion INT
)
SET NOCOUNT ON
IF @criteriaOne IS NOT NULL
INSERT INTO @criteria--insert each criterion used
(criterion,TypeOfCriterion)
SELECT @criteriaOne,1
IF @criteriaTwo IS NOT NULL
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT @criteriaTwo,1
IF @criteriaThree IS NOT NULL
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT @criteriaThree,1
IF @criteriaFour IS NOT NULL
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT @criteriaFour,1
IF @criteriaFive IS NOT NULL
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT @criteriafive,2
IF @criteriaSix IS NOT NULL
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT @criteriaSix,2
IF NOT EXISTS ( SELECT 1
FROM @criteria)--if no criteria, then select them all
INSERT INTO @criteria
(criterion,TypeOfCriterion)
SELECT '',1
SELECT DrawingSize, DrawingNumber, DrawingSection,
DrawingType, DrawingDate, Project, Designer, Scan, Area,
LineOne, LineTwo, LineThree, LineFour
FROM #DrawingInfo
--Test for the OR criteria (TypeOfCriterion=1)
WHERE recno IN (SELECT recno
FROM #DrawingInfo INNER JOIN @criteria
ON COALESCE(LineOne, '') + COALESCE(LineTwo, '')
+ COALESCE(LineThree, '') + COALESCE(LineFour, '')
+ COALESCE(Designer, '')
LIKE '%' + Criterion + '%' AND TypeOfCriterion=1
WHERE DrawingSection = @Sectnum
)
--Test for the AND criteria (TypeOfCriterion=1)
OR recno IN (SELECT recno
FROM #DrawingInfo INNER JOIN @criteria
ON COALESCE(LineOne, '') + COALESCE(LineTwo, '')
+ COALESCE(LineThree, '') + COALESCE(LineFour, '')
+ COALESCE(Designer, '')
LIKE '%' + Criterion + '%' AND TypeOfCriterion=2
WHERE DrawingSection = @Sectnum
GROUP BY recno
HAVING COUNT(*) = (SELECT COUNT (*)
FROM @criteria
WHERE TypeOfCriterion=2)
)
)[/font]
Best wishes,
Phil Factor
July 17, 2008 at 12:18 pm
Thanks Phil. This will get me going in the right direction. It is a bit slow but I can work on that.
Thanks again.
Greg
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply