Too many table names in the query

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

Transact-SQL (2000)

Too many table names in the query

Author

Topic

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-06 : 08:34:34

[code]
WHERE
ISNULL(AHD.ahd.net_res.del,0) < case @iActive when 0 then 99 else 1 end AND
ISNULL(AHD.ahd.net_res.nr_prim_search_key ,'') LIKE '%' + LTRIM(@sName) + '%' AND
ISNULL(AHD.ahd.net_res.nr_system_name ,'') LIKE '%' + LTRIM(@sSystemName) + '%' AND
ISNULL(AHD.ahd.man_mod.mdl_sym ,'') LIKE '%' + LTRIM(@sModel) + '%' AND
ISNULL(AHD.ahd.net_res.nr_serial_num ,'') LIKE '%' + LTRIM(@sSerialNumber) + '%' AND
(@sStatus IS NULL OR (AHD.ahd.ressst.sym IN (SELECT sStatus FROM #LcSelecties))) AND
ISNULL(AHD.ahd.zsftwt.sym ,'') LIKE '%' + LTRIM(@sTypeSoftware) + '%' AND
(@sFamily IS NULL OR (AHD.ahd.resfam.sym IN (SELECT sFamily FROM #LcSelecties))) AND
(@sClass IS NULL OR (AHD.ahd.gen_res.grc_type IN (SELECT sClass FROM #LcSelecties))) AND
(@sLocation IS NULL OR (AHD.ahd.loc.l_name IN (SELECT sLocation FROM #LcSelecties))) AND
ISNULL(AHD.ahd.net_res.nr_room ,'') LIKE '%' + LTRIM(@sRoom) + '%'[/code]
I get the next error

Server: Msg 106, Level 15, State 1, Procedure spLcSelecties, Line 259
Too many table names in the query. The maximum allowable is 16.

The maximum of 16 is because its sql server 6.5 how can i workarround this?

macka
Posting Yak Master

162 Posts

Posted - 2003-08-06 : 08:48:46

[url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=01be0c94%24ee582600%24aa0a50ca%40jimmy.schednet.com&rnum=3&prev=/groups%3Fq%3DToo%2Bmany%2Btable%2Bnames%2Bin%2Bthe%2Bquery.%2BThe%2Bmaximum%2Ballowable%2Bis%2B16.%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D01be0c94%2524ee582600%2524aa0a50ca%2540jimmy.schednet.com%26rnum%3D3[/url]

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-06 : 08:56:24

Macka, in the above link i see the same problem but no solution (except upgrading to SQL server 2000 thats not an option for me at this moment)

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 09:18:04

quote:

Originally posted by Stalknecht


WHERE
ISNULL(AHD.ahd.net_res.del,0) < case @iActive when 0 then 99 else  1 end   AND
ISNULL(AHD.ahd.net_res.nr_prim_search_key ,'') LIKE '%' + LTRIM(@sName) + '%' AND
ISNULL(AHD.ahd.net_res.nr_system_name ,'') LIKE '%' + LTRIM(@sSystemName) + '%' AND
ISNULL(AHD.ahd.man_mod.mdl_sym ,'') LIKE '%' + LTRIM(@sModel) + '%' AND
ISNULL(AHD.ahd.net_res.nr_serial_num ,'') LIKE '%' + LTRIM(@sSerialNumber) + '%' AND
(@sStatus IS NULL OR (AHD.ahd.ressst.sym IN (SELECT sStatus FROM #LcSelecties))) AND
ISNULL(AHD.ahd.zsftwt.sym ,'') LIKE '%' + LTRIM(@sTypeSoftware) + '%' AND
(@sFamily IS NULL OR (AHD.ahd.resfam.sym IN (SELECT sFamily FROM #LcSelecties))) AND
(@sClass IS NULL OR (AHD.ahd.gen_res.grc_type IN (SELECT sClass FROM #LcSelecties))) AND
(@sLocation IS NULL OR (AHD.ahd.loc.l_name  IN (SELECT sLocation FROM #LcSelecties))) AND
ISNULL(AHD.ahd.net_res.nr_room ,'') LIKE '%' + LTRIM(@sRoom) + '%'

I get the next error

Server: Msg 106, Level 15, State 1, Procedure spLcSelecties, Line 259
Too many table names in the query. The maximum allowable is 16.

The maximum of 16 is because its sql server 6.5 how can i workarround this?

First, why not post the whole query.

Second, AAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHh

Thrid, Yuck

Fourth, with all the '%something%' on a multi table join, do you think it will ever come home (It's going to scan each table)

Fifth, Eliminate ISNULL..any null values will be automatically excluded...'' will never be equal to @Name for example (and if it is why?)

Sixth, Look up ALIAS in BOL and make your life easier..

Seventh, If you are stuck with a table limit (which I doubt is the problem...I have had as many as 13 table joins in 6.5) try putting result sets in to temp tables..

But really post the whole thing, and nothing but the whole thing, so help you Rob...

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-06 : 09:32:05

[code]
IF OBJECT_ID('spLcSelecties') IS NOT NULL
DROP procedure spLcSelecties
GO

-- Create Stored Procedure
CREATE procedure spLcSelecties
(
@iActive Integer= NULL ,
@sName VARCHAR(50) = '',
@sSystemName VARCHAR(50) = '',
@sModel VARCHAR(50) = '',
@sSerialNumber VARCHAR(50) = '',
@sStatus VARCHAR(255) = '',
@sTypeSoftware VARCHAR(50) = '',
@sFamily VARCHAR(255) = '',
@sClass VARCHAR(255) = '',
@sLocation VARCHAR(255) = '',
@sRoom VARCHAR(50) = '',
@sNetwerkEntry VARCHAR(50) = '',
@sWallOutletNumber VARCHAR(50) = '',
@sPrimaryContact VARCHAR(50) = '',
@sOwnerGroup VARCHAR(255) = '',
@sMngrFunctGroup VARCHAR(255) = '',
@sMngrTechGroup VARCHAR(255) = '',
@sOriginating_Vendor VARCHAR(50) = '',
@sOrderNumber VARCHAR(50) = '',
@sManufacturer VARCHAR(50) = '',
@iCreationDateStart INTEGER = NULL,
@iCreationDateEnd INTEGER = NULL,
@iOrderDateStart INTEGER = NULL,
@iOrderDateEnd INTEGER = NULL,
@iAcquisitionDateStart INTEGER = NULL,
@iAcquisitionDateEnd INTEGER = NULL,
@iInstallationDateStart INTEGER = NULL,
@iInstallationDateEnd INTEGER = NULL,
@iExpirationDateStart INTEGER = NULL,
@iExpirationDateEnd INTEGER = NULL
)
AS
DECLARE @iFrom INTEGER
DECLARE @iTo INTEGER

--Create temporary table
CREATE TABLE #LcSelecties(sStatus VARCHAR(50),sFamily VARCHAR(50),sClass VARCHAR(50),sLocation VARCHAR(50),sOwnerGroup VARCHAR(50), sMngrFunctGroup VARCHAR(50),sMngrTechGroup VARCHAR(50))
select @iFrom = 1
select @iTo = 0
While @iTo < len (@sStatus)
begin
exec spcharindex ',',@sStatus,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sStatus)+ 1
insert
INTO
#LcSelecties(sStatus)
Select SUBSTRING(@sStatus, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sStatus)
select SUBSTRING(@sStatus,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

--Fill temporary table
select @iFrom = 1
select @iTo = 0
While @iTo < len (@sFamily)
begin
exec spcharindex ',',@sFamily,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sFamily)+ 1
insert
INTO
#LcSelecties(sFamily)
Select SUBSTRING(@sFamily, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sFamily)
select SUBSTRING(@sFamily,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

select @iFrom = 1
select @iTo = 0
While @iTo < len (@sClass)
begin
exec spcharindex ',',@sClass,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sClass)+ 1
insert
INTO
#LcSelecties(sClass)
Select SUBSTRING(@sClass, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sClass)
select SUBSTRING(@sClass,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

select @iFrom = 1
select @iTo = 0
While @iTo < len (@sLocation)
begin
exec spcharindex ',',@sLocation,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sLocation)+ 1
insert
INTO
#LcSelecties(sLocation)
Select SUBSTRING(@sLocation, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sLocation)
select SUBSTRING(@sLocation,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

select @iFrom = 1
select @iTo = 0
While @iTo < len (@sOwnerGroup)
begin
exec spcharindex ',',@sOwnerGroup,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sOwnerGroup)+ 1
insert
INTO
#LcSelecties(sOwnerGroup)
Select SUBSTRING(@sOwnerGroup, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sOwnerGroup)
select SUBSTRING(@sOwnerGroup,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

select @iFrom = 1
select @iTo = 0
While @iTo < len (@sMngrFunctGroup)
begin
exec spcharindex ',',@sMngrFunctGroup,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sMngrFunctGroup)+ 1
insert
INTO
#LcSelecties(sMngrFunctGroup)
Select SUBSTRING(@sMngrFunctGroup, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sMngrFunctGroup)
select SUBSTRING(@sMngrFunctGroup,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

select @iFrom = 1
select @iTo = 0
While @iTo < len (@sMngrTechGroup)
begin
exec spcharindex ',',@sMngrTechGroup,@iTo,@iTo output
if (@iTo = 0)
begin
select @iTo = len (@sMngrTechGroup)+ 1
insert
INTO
#LcSelecties(sMngrTechGroup)
Select SUBSTRING(@sMngrTechGroup, @iFrom, ( @iTo - @ifrom))
end
else
begin
Insert INTO
#LcSelecties(sMngrTechGroup)
select SUBSTRING(@sMngrTechGroup,@ifrom, ( @iTo - @ifrom))
select @iFrom = @ito + 1
end
End

--The select

SELECT
AHD.ahd.net_res.nr_prim_search_key As Name,
AHD.ahd.net_res.nr_system_name As 'System Name',
AHD.ahd.man_mod.mdl_sym As Model,
AHD.ahd.net_res.nr_serial_num As 'Serial Number',
AHD.ahd.ressst.sym As Status,
AHD.ahd.zsftwt.sym As 'Type software',
AHD.ahd.resfam.sym As Family,
AHD.ahd.gen_res.grc_type As Class,
AHD.ahd.loc.l_name As Location,
AHD.ahd.net_res.nr_room As Room,
AHD.ahd.net_res.z_nr_patchpaneldev As 'Netwerk Entry',
AHD.ahd.net_res.z_nr_outletnum As 'Wall Outlet Number',
AHD.ahd.ctct.c_last_name As 'Primary Contact',
AHD.ahd.int_org.iorg_name AS 'Owner Group',
CTCT2.c_last_name As 'Mngr Functional Group',
CTCT3.c_last_name As 'Mngr Technical Group',
AHD.ahd.vnd_prov.v_name As 'Originating Vendor',
AHD.ahd.net_res.z_nr_ordernum As 'Order Number',
AHD.ahd.man.sym As Manufacturer,
AHD.ahd.net_res.z_nr_createdate As 'Record Creation Date',
AHD.ahd.net_res.z_nr_orderdate As 'Order Date',
AHD.ahd.net_res.nr_aq_dt As 'Acquisition Date',
AHD.ahd.net_res.nr_inst_dt As 'Installation Date',
AHD.ahd.net_res.nr_exp_dt As 'Expiration Date'

FROM (((((((((((AHD.ahd.net_res

LEFT JOIN AHD.ahd.resfam ON AHD.ahd.net_res.nr_family = AHD.ahd.resfam.enum)
LEFT JOIN AHD.ahd.gen_res ON AHD.ahd.net_res.nr_grc_id = AHD.ahd.gen_res.id)
LEFT JOIN AHD.ahd.ressst ON AHD.ahd.net_res.nr_rss_id = AHD.ahd.ressst.id)
LEFT JOIN AHD.ahd.vnd_prov ON AHD.ahd.net_res.nr_prim_v_id = AHD.ahd.vnd_prov.id)
LEFT JOIN AHD.ahd.man ON AHD.ahd.net_res.nr_mfr_id = AHD.ahd.man.id)
LEFT JOIN AHD.ahd.man_mod ON AHD.ahd.net_res.nr_mdl_id = AHD.ahd.man_mod.id)
LEFT JOIN AHD.ahd.ctct ON AHD.ahd.net_res.nr_prim_c_id = AHD.ahd.ctct.id)
LEFT JOIN AHD.ahd.loc ON AHD.ahd.net_res.nr_loc_id = AHD.ahd.loc.id)
LEFT JOIN AHD.ahd.zsftwt ON AHD.ahd.net_res.z_nr_softwtype = AHD.ahd.zsftwt.id)
LEFT JOIN AHD.ahd.int_org ON AHD.ahd.net_res.z_nr_ownerorg = AHD.ahd.int_org.id)
LEFT JOIN AHD.ahd.ctct AS CTCT2 ON AHD.ahd.net_res.z_nr_functorg = CTCT2.id)
LEFT JOIN AHD.ahd.ctct AS CTCT3 ON AHD.ahd.net_res.z_nr_technorg = CTCT3.id

WHERE
ISNULL(AHD.ahd.net_res.del,0) < case @iActive when 0 then 99 else 1 end AND
ISNULL(AHD.ahd.net_res.nr_prim_search_key ,'') LIKE '%' + LTRIM(@sName) + '%' AND
ISNULL(AHD.ahd.net_res.nr_system_name ,'') LIKE '%' + LTRIM(@sSystemName) + '%' AND
ISNULL(AHD.ahd.man_mod.mdl_sym ,'') LIKE '%' + LTRIM(@sModel) + '%' AND
ISNULL(AHD.ahd.net_res.nr_serial_num ,'') LIKE '%' + LTRIM(@sSerialNumber) + '%' AND

(@sStatus IS NULL OR (AHD.ahd.ressst.sym IN (SELECT sStatus FROM #LcSelecties))) AND
ISNULL(AHD.ahd.zsftwt.sym ,'') LIKE '%' + LTRIM(@sTypeSoftware) + '%' AND

(@sFamily IS NULL OR (AHD.ahd.resfam.sym IN (SELECT sFamily FROM #LcSelecties))) AND

(@sClass IS NULL OR (AHD.ahd.gen_res.grc_type IN (SELECT sClass FROM #LcSelecties))) AND

(@sLocation IS NULL OR (AHD.ahd.loc.l_name IN (SELECT sLocation FROM #LcSelecties))) AND
ISNULL(AHD.ahd.net_res.nr_room ,'') LIKE '%' + LTRIM(@sRoom) + '%' AND
ISNULL(AHD.ahd.net_res.z_nr_patchpaneldev ,'') LIKE '%' + LTRIM(@sNetwerkEntry) + '%' AND
ISNULL(AHD.ahd.net_res.z_nr_outletnum ,'') LIKE '%' + LTRIM(@sWallOutletNumber) + '%' AND
ISNULL(AHD.ahd.ctct.c_last_name ,'') LIKE '%' + LTRIM(@sPrimaryContact) + '%' AND
(@sOwnerGroup IS NULL OR (AHD.ahd.int_org.iorg_name IN (SELECT sOwnerGroup FROM #LcSelecties))) AND
(@sMngrFunctGroup IS NULL OR (CTCT2.c_last_name IN (SELECT sMngrFunctGroup FROM #LcSelecties))) AND
(@sMngrTechGroup IS NULL OR (CTCT3.c_last_name IN (SELECT sMngrTechGroup FROM #LcSelecties))) AND
ISNULL(AHD.ahd.vnd_prov.v_name ,'') LIKE '%' + LTRIM(@sOriginating_Vendor) + '%' AND
ISNULL(AHD.ahd.net_res.z_nr_ordernum ,'') LIKE '%' + LTRIM(@sOrderNumber) + '%' AND
ISNULL(AHD.ahd.man.sym ,'') LIKE '%' + LTRIM(@sManufacturer) + '%' AND
ISNULL(AHD.ahd.man.sym ,'') LIKE '%' + LTRIM(@sManufacturer) + '%' AND
ISNULL(AHD.ahd.net_res.z_nr_createdate,0) >= COALESCE(@iCreationDateStart, ISNULL(AHD.ahd.net_res.z_nr_createdate,0)) AND
ISNULL(AHD.ahd.net_res.z_nr_createdate,0) <= COALESCE(@iCreationDateEnd, ISNULL(AHD.ahd.net_res.z_nr_createdate,0)) AND
ISNULL(AHD.ahd.net_res.z_nr_orderdate,0) >= COALESCE(@iOrderDateStart, ISNULL(AHD.ahd.net_res.z_nr_orderdate,0)) AND
ISNULL(AHD.ahd.net_res.z_nr_orderdate,0) <= COALESCE(@iOrderDateEnd, ISNULL(AHD.ahd.net_res.z_nr_orderdate,0)) AND
ISNULL(AHD.ahd.net_res.nr_aq_dt,0) >= COALESCE(@iAcquisitionDateStart, ISNULL(AHD.ahd.net_res.nr_aq_dt,0)) AND
ISNULL(AHD.ahd.net_res.nr_aq_dt,0) <= COALESCE(@iAcquisitionDateEnd,ISNULL(AHD.ahd.net_res.nr_aq_dt,0)) AND
ISNULL(AHD.ahd.net_res.nr_inst_dt,0) >= COALESCE(@iInstallationDateStart, ISNULL(AHD.ahd.net_res.nr_inst_dt,0)) AND
ISNULL(AHD.ahd.net_res.nr_inst_dt,0) <= COALESCE(@iInstallationDateEnd, ISNULL(AHD.ahd.net_res.nr_inst_dt,0)) AND
ISNULL(AHD.ahd.net_res.nr_exp_dt,0) >= COALESCE(@iExpirationDateStart, ISNULL(AHD.ahd.net_res.nr_exp_dt,0)) AND
ISNULL(AHD.ahd.net_res.nr_exp_dt,0) <= COALESCE(@iExpirationDateEnd, ISNULL(AHD.ahd.net_res.nr_exp_dt,0))
GO
[/code]

First a had created different temp tables but because of the same error i made just one

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 10:10:22

Can you edit your last response and fix your code tags...

Please...

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-06 : 11:30:20

quote:
Originally posted by X002548

Can you edit your last response and fix your code tags...

Please...

Brett

8-)

SELECT POST=NewId()

Code tags fixed

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 11:33:11

OK...I won't even begin to say I undesrstand...

but a couple of comments..

I organized the SQL so I could read it, and 2 things jumped out at me.

There are three "types" of Predicates that you have going on ( I won't even ask about the ISNULL, COALESCE, 0 Date stuff...)

I would try to create three temp tabl;es to hold each "type" of predicate and then join to those tables, or an existance check...

And can you lose the leading '%' in the LIKE statement...

Any here's the formatted code so I could "see" ( I See said the blind man) the code...I'll keep looking at it...

But I think I hurt muself...


SELECT
	  A.nr_prim_search_key 	As [Name]
	, A.nr_system_name 	As [System Name]
	, G.mdl_sym 		As [Model]
	, A.nr_serial_num 	As [Serial Number]
	, D.sym 		As [Status]
	, J.sym 		As [Type software]
	, B.sym 		As [Family]
	, C.grc_type 		As [Class]
	, I.l_name 		As [Location]
	, A.nr_room 		As [Room]
	, A.z_nr_patchpaneldev 	As [Netwerk Entry]
	, A.z_nr_outletnum 	As [Wall Outlet Number]
	, H.c_last_name 	As [Primary Contact]
	, K.iorg_name 		AS [Owner Group]
	, L.c_last_name 	As [Mngr Functional Group]
	, M.c_last_name 	As [Mngr Technical Group]
	, E.v_name 		As [Originating Vendor]
	, A.z_nr_ordernum 	As [Order Number]
	, F.sym 		As [Manufacturer]
	, A.z_nr_createdate 	As [Record Creation Date]
	, A.z_nr_orderdate 	As [Order Date]
	, A.nr_aq_dt 		As [Acquisition Date]
	, A.nr_inst_dt 		As [Installation Date]
	, A.nr_exp_dt 		As [Expiration Date]
	FROM ((((((((((((
	     AHD.ahd.net_res		A
   LEFT JOIN AHD.ahd.resfam		B 
	  ON A.nr_family 		= B.enum)
   LEFT JOIN AHD.ahd.gen_res		C
	  ON A.nr_grc_id 		= C.[id])
   LEFT JOIN AHD.ahd.ressst		D 
	  ON A.nr_rss_id 		= D.[id])
   LEFT JOIN AHD.ahd.vnd_prov		E 
	  ON A.nr_prim_v_id		= E.[id])
   LEFT JOIN AHD.ahd.man		F
	  ON A.nr_mfr_id 		= F.[id])
   LEFT JOIN AHD.ahd.man_mod		G 
	  ON A.nr_mdl_id 		= G.[id])
   LEFT JOIN AHD.ahd.ctct		H
	  ON A.nr_prim_c_id 		= H.[id])
   LEFT JOIN AHD.ahd.loc		I 
	  ON A.nr_loc_id 		= I.[id])
   LEFT JOIN AHD.ahd.zsftwt		J
	  ON A.z_nr_softwtype 		= J.id)
   LEFT JOIN AHD.ahd.int_org		K 
	  ON A.z_nr_ownerorg 		= K.[id])
   LEFT JOIN AHD.ahd.ctct 		L -- AS CTCT2 
	  ON A.z_nr_functorg 		= L.[id])
   LEFT JOIN AHD.ahd.ctct 		M -- AS CTCT3 
	  ON A.z_nr_technorg 		= M.[id])

WHERE
  	ISNULL(A.del,0) < case @iActive when 0 then 99 else  1 end   
  AND	ISNULL(A.nr_prim_search_key ,'')  	LIKE '%' + LTRIM(@sName) + '%' 
  AND	ISNULL(A.nr_system_name ,'') 		LIKE '%' + LTRIM(@sSystemName) + '%' 
  AND	ISNULL(G.mdl_sym ,'') 			LIKE '%' + LTRIM(@sModel) + '%' 
  AND	ISNULL(A.nr_serial_num ,'') 		LIKE '%' + LTRIM(@sSerialNumber) + '%' 
  AND	ISNULL(A.nr_room ,'') 			LIKE '%' + LTRIM(@sRoom) + '%' 
  AND	ISNULL(A.z_nr_patchpaneldev ,'') 	LIKE '%' + LTRIM(@sNetwerkEntry) + '%' 
  AND	ISNULL(A.z_nr_outletnum ,'') 		LIKE '%' + LTRIM(@sWallOutletNumber) + '%' 
  AND	ISNULL(H.c_last_name ,'') 		LIKE '%' + LTRIM(@sPrimaryContact) + '%' 
  AND	ISNULL(E.v_name ,'') 			LIKE '%' + LTRIM(@sOriginating_Vendor) + '%' 
  AND	ISNULL(A.z_nr_ordernum ,'') 		LIKE '%' + LTRIM(@sOrderNumber) + '%' 
  AND 	ISNULL(F.sym ,'') 			LIKE '%' + LTRIM(@sManufacturer) + '%' 
  AND	ISNULL(F.sym ,'') 			LIKE '%' + LTRIM(@sManufacturer) + '%' 
  AND	ISNULL(J.sym ,'') 			LIKE '%' + LTRIM(@sTypeSoftware) + '%' 
  AND ( @sOwnerGroup 				IS NULL 
	OR K.iorg_name 				IN (SELECT sOwnerGroup FROM #LcSelecties)
      ) 
  AND ( @sMngrFunctGroup 			IS NULL 
	OR L.c_last_name 			IN (SELECT sMngrFunctGroup FROM #LcSelecties)
      ) 
  AND ( @sMngrTechGroup 			IS NULL 
	OR M.c_last_name			IN (SELECT sMngrTechGroup FROM #LcSelecties)
      ) 
  AND ( @sStatus 				IS NULL 
	OR D.sym 				IN (SELECT sStatus FROM #LcSelecties)
       ) 
  AND ( @sFamily 				IS NULL 
	OR B.sym 				IN (SELECT sFamily FROM #LcSelecties)
       ) 
  AND ( @sClass 				IS NULL 
	OR C.grc_type 				IN (SELECT sClass FROM #LcSelecties)
      ) 
  AND ( @sLocation 				IS NULL 
	OR I.l_name  				IN (SELECT sLocation FROM #LcSelecties)
       ) 
  AND	ISNULL(A.z_nr_createdate,0) 		>= 
	COALESCE(@iCreationDateStart, 	  	ISNULL(A.z_nr_createdate,0)) 
  AND	ISNULL(A.z_nr_createdate,0) 		<= 
	COALESCE(@iCreationDateEnd, 	  	ISNULL(A.z_nr_createdate,0)) 
  AND	ISNULL(A.z_nr_orderdate,0) 		>= 
	COALESCE(@iOrderDateStart, 	  	ISNULL(A.z_nr_orderdate,0)) 
  AND	ISNULL(A.z_nr_orderdate,0) 		<= 
	COALESCE(@iOrderDateEnd, 	  	ISNULL(A.z_nr_orderdate,0)) 
  AND	ISNULL(A.nr_aq_dt,0) 	 		>= 
	COALESCE(@iAcquisitionDateStart,  	ISNULL(A.nr_aq_dt,0)) 
  AND	ISNULL(A.nr_aq_dt,0) 	 		<= 
	COALESCE(@iAcquisitionDateEnd,	  	ISNULL(A.nr_aq_dt,0)) 
  AND	ISNULL(A.nr_inst_dt,0) 	 		>= 
	COALESCE(@iInstallationDateStart, 	ISNULL(A.nr_inst_dt,0)) 
  AND	ISNULL(A.nr_inst_dt,0) 	 		<= 
	COALESCE(@iInstallationDateEnd,   	ISNULL(A.nr_inst_dt,0)) 
  AND	ISNULL(A.nr_exp_dt,0) 	 		>= 
	COALESCE(@iExpirationDateStart,   	ISNULL(A.nr_exp_dt,0)) 
  AND   ISNULL(A.nr_exp_dt,0) 	 		<= 
	COALESCE(@iExpirationDateEnd, 	  	ISNULL(A.nr_exp_dt,0))
GO

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-07 : 03:30:28

Seems that sql 6.5 counts the related tables including the select statements in the WHERE clause.
Brett what do you mean by "three "types" of Predicates"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-08-07 : 04:08:03

And old technique that MIGHT work for you.

Create a temp table. get '1/2' you query : i.e. do your first 4 tables, left-joined, into that table. Then run, as a second query, left joining from the temp tbale, and carrying-on.

NB NB NB I haven't "tested this at all" it is merely a rough example of what you might try :


insert into temp_table
SELECT
	  A.nr_prim_search_key 	As [Name]
	, A.nr_system_name 	As [System Name]
	, A.nr_serial_num 	As [Serial Number]
	, D.sym 		As [Status]
	, B.sym 		As [Family]
	, C.grc_type 		As [Class]
	, A.nr_room 		As [Room]
	, A.z_nr_patchpaneldev 	As [Netwerk Entry]
	, A.z_nr_outletnum 	As [Wall Outlet Number]
	, E.v_name 		As [Originating Vendor]
	, A.z_nr_ordernum 	As [Order Number]
	, F.sym 		As [Manufacturer]
	, A.z_nr_createdate 	As [Record Creation Date]
	, A.z_nr_orderdate 	As [Order Date]
	, A.nr_aq_dt 		As [Acquisition Date]
	, A.nr_inst_dt 		As [Installation Date]
	, A.nr_exp_dt 		As [Expiration Date]
	FROM (((((
	     AHD.ahd.net_res		A
   LEFT JOIN AHD.ahd.resfam		B 
	  ON A.nr_family 		= B.enum)
   LEFT JOIN AHD.ahd.gen_res		C
	  ON A.nr_grc_id 		= C.[id])
   LEFT JOIN AHD.ahd.ressst		D 
	  ON A.nr_rss_id 		= D.[id])
   LEFT JOIN AHD.ahd.vnd_prov		E 
	  ON A.nr_prim_v_id		= E.[id])
   LEFT JOIN AHD.ahd.man		F
	  ON A.nr_mfr_id 		= F.[id])

WHERE
  	ISNULL(A.del,0) < case @iActive when 0 then 99 else  1 end   
  AND	ISNULL(A.nr_prim_search_key ,'')  	LIKE '%' + LTRIM(@sName) + '%' 
  AND	ISNULL(A.nr_system_name ,'') 		LIKE '%' + LTRIM(@sSystemName) + '%' 
  AND	ISNULL(A.nr_serial_num ,'') 		LIKE '%' + LTRIM(@sSerialNumber) + '%' 
  AND	ISNULL(A.nr_room ,'') 			LIKE '%' + LTRIM(@sRoom) + '%' 
  AND	ISNULL(A.z_nr_patchpaneldev ,'') 	LIKE '%' + LTRIM(@sNetwerkEntry) + '%' 
  AND	ISNULL(A.z_nr_outletnum ,'') 		LIKE '%' + LTRIM(@sWallOutletNumber) + '%' 
  AND	ISNULL(E.v_name ,'') 			LIKE '%' + LTRIM(@sOriginating_Vendor) + '%' 
  AND	ISNULL(A.z_nr_ordernum ,'') 		LIKE '%' + LTRIM(@sOrderNumber) + '%' 
  AND 	ISNULL(F.sym ,'') 			LIKE '%' + LTRIM(@sManufacturer) + '%' 
  AND	ISNULL(F.sym ,'') 			LIKE '%' + LTRIM(@sManufacturer) + '%' 
  AND ( @sStatus 				IS NULL 
	OR D.sym 				IN (SELECT sStatus FROM #LcSelecties)
       ) 
  AND ( @sFamily 				IS NULL 
	OR B.sym 				IN (SELECT sFamily FROM #LcSelecties)
       ) 
  AND ( @sClass 				IS NULL 
	OR C.grc_type 				IN (SELECT sClass FROM #LcSelecties)
      ) 
  AND	ISNULL(A.z_nr_createdate,0) 		>= 
	COALESCE(@iCreationDateStart, 	  	ISNULL(A.z_nr_createdate,0)) 
  AND	ISNULL(A.z_nr_createdate,0) 		<= 
	COALESCE(@iCreationDateEnd, 	  	ISNULL(A.z_nr_createdate,0)) 
  AND	ISNULL(A.z_nr_orderdate,0) 		>= 
	COALESCE(@iOrderDateStart, 	  	ISNULL(A.z_nr_orderdate,0)) 
  AND	ISNULL(A.z_nr_orderdate,0) 		<= 
	COALESCE(@iOrderDateEnd, 	  	ISNULL(A.z_nr_orderdate,0)) 
  AND	ISNULL(A.nr_aq_dt,0) 	 		>= 
	COALESCE(@iAcquisitionDateStart,  	ISNULL(A.nr_aq_dt,0)) 
  AND	ISNULL(A.nr_aq_dt,0) 	 		<= 
	COALESCE(@iAcquisitionDateEnd,	  	ISNULL(A.nr_aq_dt,0)) 
  AND	ISNULL(A.nr_inst_dt,0) 	 		>= 
	COALESCE(@iInstallationDateStart, 	ISNULL(A.nr_inst_dt,0)) 
  AND	ISNULL(A.nr_inst_dt,0) 	 		<= 
	COALESCE(@iInstallationDateEnd,   	ISNULL(A.nr_inst_dt,0)) 
  AND	ISNULL(A.nr_exp_dt,0) 	 		>= 
	COALESCE(@iExpirationDateStart,   	ISNULL(A.nr_exp_dt,0)) 
  AND   ISNULL(A.nr_exp_dt,0) 	 		<= 
	COALESCE(@iExpirationDateEnd, 	  	ISNULL(A.nr_exp_dt,0))


-- then the next bit

select
	  TT.nr_prim_search_key 	As [Name]
	, TT.nr_system_name 	As [System Name]
	, G.mdl_sym 		As [Model]
	, TT.nr_serial_num 	As [Serial Number]
	, TT.sym 		As [Status]
	, J.sym 		As [Type software]
	, TT.sym 		As [Family]
	, TT.grc_type 		As [Class]
	, I.l_name 		As [Location]
	, TT.nr_room 		As [Room]
	, TT.z_nr_patchpaneldev 	As [Netwerk Entry]
	, TT.z_nr_outletnum 	As [Wall Outlet Number]
	, H.c_last_name 	As [Primary Contact]
	, K.iorg_name 		AS [Owner Group]
	, L.c_last_name 	As [Mngr Functional Group]
	, M.c_last_name 	As [Mngr Technical Group]
	, TT.v_name 		As [Originating Vendor]
	, TT.z_nr_ordernum 	As [Order Number]
	, TT.sym 		As [Manufacturer]
	, TT.z_nr_createdate 	As [Record Creation Date]
	, TT.z_nr_orderdate 	As [Order Date]
	, TT.nr_aq_dt 		As [Acquisition Date]
	, TT.nr_inst_dt 		As [Installation Date]
	, TT.nr_exp_dt 		As [Expiration Date]

from
	temp_table TT
   LEFT JOIN AHD.ahd.man_mod		G 
	  ON TT.nr_mdl_id 		= G.[id])
   LEFT JOIN AHD.ahd.ctct		H
	  ON TT.nr_prim_c_id 		= H.[id])
   LEFT JOIN AHD.ahd.loc		I 
	  ON TT.nr_loc_id 		= I.[id])
   LEFT JOIN AHD.ahd.zsftwt		J
	  ON TT.z_nr_softwtype 		= J.id)
   LEFT JOIN AHD.ahd.int_org		K 
	  ON TT.z_nr_ownerorg 		= K.[id])
   LEFT JOIN AHD.ahd.ctct 		L -- AS CTCT2 
	  ON TT.z_nr_functorg 		= L.[id])
   LEFT JOIN AHD.ahd.ctct 		M -- AS CTCT3 
	  ON TT.z_nr_technorg 		= M.[id])

where
	ISNULL(G.mdl_sym ,'') 			LIKE '%' + LTRIM(@sModel) + '%' 
	AND	ISNULL(H.c_last_name ,'') 		LIKE '%' + LTRIM(@sPrimaryContact) + '%' 
	AND	ISNULL(J.sym ,'') 			LIKE '%' + LTRIM(@sTypeSoftware) + '%' 
	AND 	( @sOwnerGroup 				IS NULL 
		OR K.iorg_name		IN (SELECT sOwnerGroup FROM #LcSelecties)
      		) 
	AND 	( @sMngrFunctGroup 			IS NULL 
		OR L.c_last_name 	IN (SELECT sMngrFunctGroup FROM #LcSelecties)
      		) 
	AND 	( @sMngrTechGroup 			IS NULL 
		OR M.c_last_name	IN (SELECT sMngrTechGroup FROM #LcSelecties)
      		) 
	AND 	( @sLocation 				IS NULL 
		OR I.l_name  		IN (SELECT sLocation FROM #LcSelecties)
       		)

You'll still need to worry about all your performance issues, but this MIGHT get you around the 16 table limit...

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-07 : 09:03:23

[code]CREATE TABLE #LcSelecties(Field INTEGER)
INSERT INTO
#LcSelecties
SELECT
AHD.AHD.net_res.id
FROM AHD.AHD.net_res
LEFT JOIN AHD.AHD.vnd_prov ON AHD.AHD.net_res.nr_prim_v_id = AHD.AHD.vnd_prov.id
LEFT JOIN AHD.AHD.man ON AHD.AHD.net_res.nr_mfr_id = AHD.AHD.man.id
LEFT JOIN AHD.AHD.man_mod ON AHD.AHD.net_res.nr_mdl_id = AHD.AHD.man_mod.id
LEFT JOIN AHD.AHD.ctct ON AHD.AHD.net_res.nr_prim_c_id = AHD.AHD.ctct.id
LEFT JOIN AHD.AHD.zsftwt ON AHD.AHD.net_res.z_nr_softwtype = AHD.AHD.zsftwt.id
LEFT JOIN AHD.AHD.ctct AS CTCT3 ON AHD.AHD.net_res.z_nr_technorg = CTCT3.id
WHERE
-- nog aanpassen del = 0
(@sActive = '' OR AHD.AHD.net_res.del = convert(integer,@sActive)) AND --when 0 then 99 else 1 end AND
ISNULL(AHD.AHD.net_res.nr_prim_search_key ,'') LIKE '%' + LTRIM(@sName) + '%' AND
ISNULL(AHD.AHD.net_res.nr_system_name ,'') LIKE '%' + LTRIM(@sSystemName) + '%' AND
ISNULL(AHD.AHD.man_mod.mdl_sym ,'') LIKE '%' + LTRIM(@sModel) + '%' AND
ISNULL(AHD.AHD.net_res.nr_serial_num ,'') LIKE '%' + LTRIM(@sSerialNumber) + '%' AND
(@Status = '' OR (AHD.AHD.net_res.nr_rss_id IN (SELECT Field FROM #Status))) AND
ISNULL(AHD.AHD.zsftwt.sym ,'') LIKE '%' + LTRIM(@sTypeSoftware) + '%' AND
(@Family = '' OR (AHD.AHD.net_res.nr_family IN (SELECT Field FROM #Family))) AND
(@Class = '' OR (AHD.AHD.net_res.nr_grc_id IN (SELECT Field FROM #Class))) AND
(@Location = '' OR (AHD.AHD.net_res.nr_loc_id IN (SELECT Field FROM #Location))) AND
ISNULL(AHD.AHD.net_res.nr_room ,'') LIKE '%' + LTRIM(@sRoom) + '%' AND
ISNULL(AHD.AHD.net_res.z_nr_patchpaneldev ,'') LIKE '%' + LTRIM(@sNetwerkEntry) + '%' AND
ISNULL(AHD.AHD.net_res.z_nr_outletnum ,'') LIKE '%' + LTRIM(@sWallOutletNumber) + '%' AND
ISNULL(AHD.AHD.ctct.c_last_name ,'') LIKE '%' + LTRIM(@sPrimaryContact) + '%' AND
(@OwnerGroup = '' OR (AHD.AHD.net_res.z_nr_ownerorg IN (SELECT Field FROM #OwnerGroup))) AND
(@MngrFunctGroup = '' OR (AHD.AHD.net_res.z_nr_functorg IN (SELECT Field FROM #MngrFunctGroup))) AND
ISNULL(CTCT3.c_last_name ,'') LIKE '%' + LTRIM(@MngrTechGroup) + '%' AND
(@MngrTechGroup = '' OR (AHD.AHD.net_res.z_nr_technorg IN (SELECT Field FROM #MngrTechGroup))) AND
ISNULL(AHD.AHD.vnd_prov.v_name ,'') LIKE '%' + LTRIM(@sOriginating_Vendor) + '%' AND
ISNULL(AHD.AHD.net_res.z_nr_ordernum ,'') LIKE '%' + LTRIM(@sOrderNumber) + '%' AND
ISNULL(AHD.AHD.man.sym ,'') LIKE '%' + LTRIM(@sManufacturer) + '%' AND
ISNULL(AHD.AHD.man.sym ,'') LIKE '%' + LTRIM(@sManufacturer) + '%' AND
ISNULL(AHD.AHD.net_res.z_nr_createdate,0) >= COALESCE(@iCreationDateStart, ISNULL(AHD.AHD.net_res.z_nr_createdate,0)) AND
ISNULL(AHD.AHD.net_res.z_nr_createdate,0) <= COALESCE(@iCreationDateEnd, ISNULL(AHD.AHD.net_res.z_nr_createdate,0)) AND
ISNULL(AHD.AHD.net_res.z_nr_orderdate,0) >= COALESCE(@iOrderDateStart, ISNULL(AHD.AHD.net_res.z_nr_orderdate,0)) AND
ISNULL(AHD.AHD.net_res.z_nr_orderdate,0) <= COALESCE(@iOrderDateEnd, ISNULL(AHD.AHD.net_res.z_nr_orderdate,0)) AND
ISNULL(AHD.AHD.net_res.nr_aq_dt,0) >= COALESCE(@iAcquisitionDateStart, ISNULL(AHD.AHD.net_res.nr_aq_dt,0)) AND
ISNULL(AHD.AHD.net_res.nr_aq_dt,0) <= COALESCE(@iAcquisitionDateEnd,ISNULL(AHD.AHD.net_res.nr_aq_dt,0)) AND
ISNULL(AHD.AHD.net_res.nr_inst_dt,0) >= COALESCE(@iInstallationDateStart, ISNULL(AHD.AHD.net_res.nr_inst_dt,0)) AND
ISNULL(AHD.AHD.net_res.nr_inst_dt,0) <= COALESCE(@iInstallationDateEnd, ISNULL(AHD.AHD.net_res.nr_inst_dt,0)) AND
ISNULL(AHD.AHD.net_res.nr_exp_dt,0) >= COALESCE(@iExpirationDateStart, ISNULL(AHD.AHD.net_res.nr_exp_dt,0)) AND
ISNULL(AHD.AHD.net_res.nr_exp_dt,0) <= COALESCE(@iExpirationDateEnd, ISNULL(AHD.AHD.net_res.nr_exp_dt,0))

---
----
SELECT
--AHD.AHD.net_res.id

AHD.AHD.net_res.nr_prim_search_key As Name,
AHD.AHD.net_res.nr_system_name As 'System Name',
AHD.AHD.man_mod.mdl_sym As Model,
AHD.AHD.net_res.nr_serial_num As 'Serial Number',
AHD.AHD.ressst.sym As Status,
AHD.AHD.zsftwt.sym As 'Type software',
AHD.AHD.resfam.sym As Family,
AHD.AHD.gen_res.grc_type As Class,
AHD.AHD.loc.l_name As Location,
AHD.AHD.net_res.nr_room As Room,
AHD.AHD.net_res.z_nr_patchpaneldev As 'Netwerk Entry',
AHD.AHD.net_res.z_nr_outletnum As 'Wall Outlet Number',
AHD.AHD.ctct.c_last_name As 'Primary Contact',
AHD.AHD.int_org.iorg_name AS 'Owner Group',
CTCT2.c_last_name As 'Mngr Functional Group',
CTCT3.c_last_name As 'Mngr Technical Group',
AHD.AHD.vnd_prov.v_name As 'Originating Vendor',
AHD.AHD.net_res.z_nr_ordernum As 'Order Number',
AHD.AHD.man.sym As Manufacturer,
AHD.AHD.net_res.z_nr_createdate As 'Record Creation Date',
AHD.AHD.net_res.z_nr_orderdate As 'Order Date',
AHD.AHD.net_res.nr_aq_dt As 'Acquisition Date',
AHD.AHD.net_res.nr_inst_dt As 'Installation Date',
AHD.AHD.net_res.nr_exp_dt As 'Expiration Date'

FROM AHD.AHD.net_res
INNER JOIN #LcSELECTies ON AHD.AHD.net_res.id = #LcSELECTies.field
LEFT JOIN AHD.AHD.resfam ON AHD.AHD.net_res.nr_family = AHD.AHD.resfam.enum
LEFT JOIN AHD.AHD.gen_res ON AHD.AHD.net_res.nr_grc_id = AHD.AHD.gen_res.id
LEFT JOIN AHD.AHD.ressst ON AHD.AHD.net_res.nr_rss_id = AHD.AHD.ressst.id
LEFT JOIN AHD.AHD.vnd_prov ON AHD.AHD.net_res.nr_prim_v_id = AHD.AHD.vnd_prov.id
LEFT JOIN AHD.AHD.man ON AHD.AHD.net_res.nr_mfr_id = AHD.AHD.man.id
LEFT JOIN AHD.AHD.man_mod ON AHD.AHD.net_res.nr_mdl_id = AHD.AHD.man_mod.id
LEFT JOIN AHD.AHD.ctct ON AHD.AHD.net_res.nr_prim_c_id = AHD.AHD.ctct.id
LEFT JOIN AHD.AHD.loc ON AHD.AHD.net_res.nr_loc_id = AHD.AHD.loc.id
LEFT JOIN AHD.AHD.zsftwt ON AHD.AHD.net_res.z_nr_softwtype = AHD.AHD.zsftwt.id
LEFT JOIN AHD.AHD.int_org ON AHD.AHD.net_res.z_nr_ownerorg = AHD.AHD.int_org.id
LEFT JOIN AHD.AHD.ctct AS CTCT2 ON AHD.AHD.net_res.z_nr_functorg = CTCT2.id
LEFT JOIN AHD.AHD.ctct AS CTCT3 ON AHD.AHD.net_res.z_nr_technorg = CTCT3.id
GO[/code]

I did it this way it's working on 6.5 and is returning 28895 records in 40 seconds.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-08-07 : 09:10:09

Glad the "split up" helped you out.

Is 40 secs acceptable ... is this reporting, or a on-line transaction ?

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 09:12:18

quote:

I did it this way it's working on 6.5 and is returning 28895 records in 40 seconds.

OK, but what are you going to do with all of that?

Don't you need to reduce you selection?

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-07 : 09:31:08

The problem is that i cannot loose those wildcards and the "ISNULL/COALESCE" construction is to prevent losing records because the WHERE statement is applied after the FROM clause!(think of all those NULL's)

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 09:37:37

My question is, what do you plan to do with almost 30k of records?

The final result of the sproc is to spit out 30k records...for what purpose?

Brett

8-)

SELECT POST=NewId()

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-08-07 : 10:10:39

I'm guessing some kind of reporting, or else this is used to then generate work off of (work list generation).

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-07 : 10:12:53

For reporting only.
I tested the speed with 30k. The users results should be less.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources