Too many table names in qury. The max is 256

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 qury. The max is 256

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-11-09 : 03:49:27

Hi, I have no clue to get pass this.
My previous post is my second problem & this my first.

Converting Rows to columns: Too many table names in the query. The maximum allowable is 256.

----------------------------------------------------------------------
select
case when v1.ProcessID is null -- 1 row with 4 tables
then (Select max(V.ControlValue) from dbo.fcEventHist E left join dbo.fcEventValue V on E.ID = V.EventHistID where E.ProcessID = 367 and V.ControlID = 'FG1010NItemD' group by V.ControlValue)
else (Select max(V.ControlValue) from dbo.fcEventArchive E left join dbo.fcEventValueArchive V on E.EventHistID = V.EventHistID where E.ProcessID = 367 and V.ControlID = 'FG1010NItemD' group by V.ControlValue)
end NItemD
,case when v1.ProcessID is null -- 2nd row
then (Select max(V.ControlValue) from dbo.fcEventHist E left join dbo.fcEventValue V on E.ID = V.EventHistID where E.ProcessID = 367 and V.ControlID = 'FG1020NShortD' group by V.ControlValue)
else (Select max(V.ControlValue) from dbo.fcEventArchive E left join dbo.fcEventValueArchive V on E.EventHistID = V.EventHistID where E.ProcessID = 367 and V.ControlID = 'FG1020NShortD' group by V.ControlValue)
end NShortD

from
(select ProcessID from dbo.fcEventArchive where ProcessID = 367 group by ProcessID)v1
--------------------------------------------------------------------

This code works but unfortunately I have 65 rows to be converted & does not allow me, becuase of all the joint tables

Can anyone PLEASE see where I can minimixe the amount of tables used?

Regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 04:08:43

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92354

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 04:23:32

Now I realized your code has several flaws.

1) You are grouping by the same column as you are aggregating MAX on
2) If ProcessID 367 do not exist in table v1 no records whatsoever will be returned

Have a look at this replacement to see if it gives you what you want

IF EXISTS (SELECT * FROM dbo.fcEventArchive WHERE ProcessID = 367)
	SELECT		MAX(CASE WHEN v.ControlID = 'FG1010NItemD' THEN v.ControlValue ELSE NULL END) AS NItemD
			MAX(CASE WHEN v.ControlID = 'FG1020NShortD' THEN v.ControlValue ELSE NULL END) AS NShortD
	FROM		dbo.fcEventArchive AS e
	LEFT JOIN	dbo.fcEventValueArchive AS v ON v.EventHistID = e.EventHistID
				 AND v.ControlID IN ('FG1010NItemD', 'FG1020NShortD')
	WHERE		e.ProcessID = 367
ELSE
	SELECT		MAX(CASE WHEN v.ControlID = 'FG1010NItemD' THEN v.ControlValue ELSE NULL END) AS NItemD
			MAX(CASE WHEN v.ControlID = 'FG1020NShortD' THEN v.ControlValue ELSE NULL END) AS NShortD
	FROM		dbo.fcEventHist AS e
	LEFT JOIN	dbo.fcEventValue AS v ON v.EventHistID = e.ID
				 AND v.ControlID IN ('FG1010NItemD', 'FG1020NShortD')
	WHERE		e.ProcessID = 367

E 12°55'05.25"
N 56°04'39.16"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-11-09 : 06:54:04

Hi, Wow.. A Big Thank You - totally different & good way. Thank YOU
I added the max join as well but I have a problem as the ELSE part does not work it returns for all NULLS - I checked the data in the table.The entire second query without the Else returns Nulls.
IF EXISTS (select * from dbo.fcEventArchive where ProcessID = 378)

select MAX(Case when v.ControlID = 'FG1010NItemD' then v.ControlValue else NULL END) as NItemD
,MAX(Case when v.ControlID = 'FG1020NShortD' then v.ControlValue else NULL END) as NShortD

from dbo.fcEventArchive as e
left join dbo.fcEventValueArchive as v
on v.EventHistID = e.EventHistID
and v.ControlID in ('FG1010NItemD', 'FG1020NShortD')
join (select max(v1.EventHistID) as max_id,v1.ControlID from dbo.fcEventArchive E1
left join dbo.fcEventValueArchive V1 on E1.EventHistID = V1.EventHistID where E1.ProcessID = 378 group by v1.controlid) max_qry
on max_id = V.EventHistID and max_qry.ControlID = V.ControlID
where e.ProcessID = 378

Else

select MAX(Case when v.ControlID = 'FG1010NItemD' then v.ControlValue else NULL END) as NItemD
,MAX(Case when v.ControlID = 'FG1020NShortD' then v.ControlValue else NULL END) as NShortD

from dbo.fcEventHist as e
left join dbo.fcEventValue as v
on v.EventHistID = e.ID
and v.ControlID in ('FG1010NItemD', 'FG1020NShortD')
join (select max(v1.EventHistID) as max_id,v1.ControlID from dbo.fcEventArchive E1
left join dbo.fcEventValueArchive V1 on E1.EventHistID = V1.EventHistID where E1.ProcessID = 378 group by v1.controlid) max_qry
on max_id = V.EventHistID and max_qry.ControlID = V.ControlID
where e.ProcessID = 378

It Returns ALL NULLS but there is data & link is fine.]

Please have a look!

Regards.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-11-09 : 07:06:03

Hi, I got it... I did not change the tables & fields on my max for the second query.

Thank You Mr. Larsson

from dbo.fcEventHist as e
left join dbo.fcEventValue as v
on v.EventHistID = e.ID
and v.ControlID in ('FG1010NItemD', 'FG1020NShortD')
join
(select max(v1.EventHistID) as max_id,v1.ControlID from dbo.fcEventHist E1
left join dbo.fcEventValue V1 on E1.ID = V1.EventHistID where E1.ProcessID = 378 group by v1.controlid) max_qry
on max_id = V.EventHistID and max_qry.ControlID = V.ControlID

where e.ProcessID = 378

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 07:23:59

And as a stored procedure

CREATE PROCEDURE dbo.uspGetMyEvents
(
	@ProcessID INT
)
AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM dbo.fcEventArchive WHERE ProcessID = @ProcessID)
	SELECT		MAX(CASE WHEN ControlID = 'FG1010NItemD' THEN ControlValue END) AS NItemD
			MAX(CASE WHEN ControlID = 'FG1020NShortD' THEN ControlValue END) AS NShortD
	FROM		(
				SELECT		TOP 1 WITH TIES
						v.ControlID,
						v.ControlValue
				FROM		dbo.fcEventArchive AS e
				LEFT JOIN	dbo.fcEventValueArchive AS v ON v.EventHistID = e.EventHistID
							AND v.ControlID IN ('FG1010NItemD', 'FG1020NShortD')
				WHERE		e.ProcessID = @ProcessID
				ORDER BY	e.EventHistID DESC
			) AS j
ELSE
	SELECT		MAX(CASE WHEN ControlID = 'FG1010NItemD' THEN ControlValue END) AS NItemD
			MAX(CASE WHEN ControlID = 'FG1020NShortD' THEN ControlValue END) AS NShortD
	FROM		(
				SELECT		TOP 1 WITH TIES
						v.ControlID,
						v.ControlValue
				FROM		dbo.fcEventHist AS e
				LEFT JOIN	dbo.fcEventValue AS v ON v.EventHistID = e.ID
							AND v.ControlID IN ('FG1010NItemD', 'FG1020NShortD')
				WHERE		e.ProcessID = @ProcessID
				ORDER BY	e.ID DESC
			) AS j

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources