June 16, 2010 at 5:57 am
I have used subqueries in my Where clause for years with no problems. I am trying something different, and it looks like it should work, based on what I have read, but I get an error. The goal is to join my subquery like I would any other table. I don't know that it will benefit performance in this case, as the tables are tiny, but it is more of an academic exercise.
This works fine:
SELECT L.LogTableKey
,L.CreateDate
,L.DictionaryTableKey
,L.JobTableKey
,L.[Action]
,L.[Message]
FROM ssisAdmin.[Log] L
Inner Join ssisAdmin.Dictionary D On L.DictionaryTableKey = D.DictionaryTableKey
Where ProcessName = 'RecipElig' --Your process name goes here.
And L.JobTableKey = (Select Max(JobTableKey) As JobTableKey
From ssisAdmin.Job
Where DictionaryTableKey = D.DictionaryTableKey)
Order by LogTableKey Desc
This gives me the error "The multi-part identifier "D.DictionaryTableKey" could not be bound."
SELECT L.LogTableKey
,L.CreateDate
,L.DictionaryTableKey
,L.JobTableKey
,L.[Action]
,L.[Message]
FROM ssisAdmin.[Log] L
Inner Join ssisAdmin.Dictionary D On L.DictionaryTableKey = D.DictionaryTableKey
Inner Join (Select Max(JobTableKey) As JobTableKey
From ssisAdmin.Job J2
Where J2.DictionaryTableKey = D.DictionaryTableKey) As J On J.JobTableKey = L.JobTableKey
Where ProcessName = 'RecipElig' --Your process name goes here.
Order by LogTableKey Desc
I have verified my tables and fields over and over. I think I'm missing something pretty simple, but even after sleeping on it I am still stumped.
What did I miss?
Table code:
CREATE TABLE [ssisAdmin].[Job](
[JobTableKey] [int] IDENTITY(1,1) NOT NULL,
[DictionaryTableKey] [int] NOT NULL,
[JobDescription] [varchar](255) NOT NULL,
[FileSize] [bigint] NULL,
[BeginDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[TotalUnits] [bigint] NULL,
[ErrorUnits] [bigint] NULL,
[SuccessfulUnits] [bigint] NULL,
[FinalStatus] [varchar](50) NULL,
CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED
(
[JobTableKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [ssisAdmin].[Dictionary](
[DictionaryTableKey] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NULL,
[ProcessName] [varchar](50) NOT NULL,
[FileName] [varchar](250) NOT NULL,
[ChildDtsxPath] [varchar](1000) NOT NULL,
[ChildConfigPath] [varchar](1000) NOT NULL,
[HasSourceFile] [bit] NOT NULL,
[WorkingFileName] [varchar](250) NOT NULL,
[SourceDirectory] [varchar](1000) NOT NULL,
[WorkingDirectory] [varchar](1000) NOT NULL,
[ProcessedDirectory] [varchar](1000) NOT NULL,
[ProcessedDirectoryDeleteDays] [int] NOT NULL,
[ErroredDirectory] [varchar](1000) NOT NULL,
[IsCompressed] [bit] NOT NULL,
[Priority] [int] NOT NULL,
[LogWarnings] [bit] NOT NULL,
[LogErrors] [bit] NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_Dictionary] PRIMARY KEY CLUSTERED
(
[DictionaryTableKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uc_ProcessName] UNIQUE NONCLUSTERED
(
[ProcessName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [ssisAdmin].[Log](
[LogTableKey] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NULL,
[DictionaryTableKey] [int] NOT NULL,
[JobTableKey] [int] NULL,
[Action] [varchar](50) NOT NULL,
[Message] [varchar](4000) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
[LogTableKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
June 16, 2010 at 6:21 am
You simply use co-related join as sub query...
It may be help you...
SELECT L.LogTableKey
,L.CreateDate
,L.DictionaryTableKey
,L.JobTableKey
,L.[Action]
,L.[Message]
FROM [Log] L
Inner Join Dictionary D
On L.DictionaryTableKey = D.DictionaryTableKey
Inner Join (
Select Max(JobTableKey) As JobTableKey , DictionaryTableKey
From Job J2
GROUP BY DictionaryTableKey
) As J
On J.JobTableKey = L.JobTableKey
AND J.DictionaryTableKey = D.DictionaryTableKey
Where ProcessName = 'RecipElig' --Your process name goes here.
Order by LogTableKey Desc
June 16, 2010 at 6:36 am
That worked. Thanks.
Seems like it should work the other way too, but I'm obviously mistaken there.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
June 16, 2010 at 6:38 am
I thought we can not use corelated query with in inner join..
Modified query.......using corelated join (But not recomended)
SELECT L.LogTableKey
,L.CreateDate
,L.DictionaryTableKey
,L.JobTableKey
,L.[Action]
,L.[Message]
FROM [LOG] L
INNER JOIN Dictionary D
On L.DictionaryTableKey = D.DictionaryTableKey
WHERE
L.JobTableKey = (SELECT MAX(JobTableKey)
From Job J2
Where J2.DictionaryTableKey = D.DictionaryTableKey
)
and ProcessName = 'RecipElig' --Your process name goes here.
Order by LogTableKey Desc
June 16, 2010 at 7:22 am
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Ah, excellent! 😎
June 16, 2010 at 7:28 am
You can wrap you subquery into CTE or you can use cross apply like this:
SELECT L.LogTableKey
,L.CreateDate
,L.DictionaryTableKey
,L.JobTableKey
,L.[Action]
,L.[Message]
FROM [Log] L
Inner Join Dictionary D On L.DictionaryTableKey = D.DictionaryTableKey
cross apply (Select Max(JobTableKey) As JobTableKey
From Job J2
Where J2.DictionaryTableKey = D.DictionaryTableKey) As J
Where ProcessName = 'RecipElig' --Your process name goes here.
and J.JobTableKey = L.JobTableKey
Order by LogTableKey Desc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply